sql-server:在结果上使用REPLACE()函数后，将选择值拆分为多行

Question

我得到了一个包含类似值的表。

1.<content><value>foo</value><value>bar</value></content>
2.<content><value>value3</value><value>value4</value></content>

我正在使用 REPLACE 函数来删除所有标签（内容和值），并将我替换</value>的值与;

select CAST(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(CAST(TEXTVALUE as NVarchar(MAX))
,'<content>',''),'<value>','')
,'</value>',';')
,' ','')
,'</content>','')
AS NText) from dbo.mytable ...

我的 select 语句的输出如下所示：

1.foo;bar
2.value3;value4

但是 - 我想拆分此选择语句以为每个值返回一个新行：

1. foo
2. bar
3. value3
4. value4

我已经看到了很多将 select 语句的结果拆分为多行的答案和方法，但我没有看到任何方法可以先替换部分值，然后按字符将结果拆分为多行。

我希望你能在这里帮助我。

编辑：感谢这么多有用的答案。我忘了补充，我也希望能够在同一请求中选择其他列。

Answer 1

这是使用 xml nodes方法的一种方法

SELECT id,Split.a.value('.', 'VARCHAR(100)')
FROM   (SELECT Cast(xmlcol AS XML) AS data,id
        FROM   Yourtable) a
       CROSS apply Data.nodes ('content/value') AS Split(a) 

Answer 2

这应该可以解决问题：

SELECT STRING_SPLIT(Sub.NText, ';') FROM 
    (select CAST(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(CAST(TEXTVALUE as NVarchar(MAX))
    ,'<content>',''),'<value>','')
    ,'</value>',';')
    ,' ','')
    ,'</content>','')
    AS NText) from dbo.mytable) AS Sub

Answer 3

在

根元素内添加 XML 字符串。 像这样

<root><content><value>foo</value><value>bar</value></content><content><value>value3</value><value>value4</value></content></root>
declare @xmlPath xml = '<root><content><value>foo</value><value>bar</value></content><content><value>value3</value><value>value4</value></content></root>';
SELECT 
    p.value('(text())[1]', 'VARCHAR(8000)') AS value
FROM @xmlPath.nodes('/root/content/value') AS a(p)

无需替换 XML 元素字符串。

你也可以使用它。 如果你正在将值提取到表中。文本字符串==是字段名称

SELECT  
    p.value('(text())[1]', 'VARCHAR(8000)') AS value
FROM #tempTable temp
 CROSS APPLY  textstring.nodes('/content/value') AS a(p)