如何在SQL中将XML节点值转换为逗号分隔的值?

问题描述：

我想在SQL中将XML节点值转换为逗号分隔的值.

我尝试过的事情:

I Want to Convert XML node values to comma separated values in SQL.

What I have tried:

DECLARE @PersonsXml XML = 
''<ROOT><FILETYPE>PEN</FILETYPE><FILETYPE>REM</FILETYPE><FILETYPE>SAL</FILETYPE></ROOT>''

--SELECT  b.value(''(./firstName/text())[1]'',''nvarchar(max)'') as FirstName, b.value(''(./lastName/text())[1]'',''nvarchar(max)'') as LastName
--FROM @PersonsXml.nodes(''/persons/person'') AS a(b)


DECLARE @ConcatString VARCHAR(MAX)

SELECT @ConcatString = COALESCE(@ConcatString + '', '', '''') + Code
FROM (
SELECT  b.value(''(./FILETYPE/text())[2]'',''nvarchar(max)'') as Code
--, b.value(''(./lastName/text())[1]'',''nvarchar(max)'') as LastName
FROM @PersonsXml.nodes(''/ROOT'') AS a(b)
)as TBL

答

这非常简单-

this is pretty simple-

DECLARE @PersonsXml XML = 
'<ROOT><FILETYPE>PEN</FILETYPE><FILETYPE>REM</FILETYPE><FILETYPE>SAL</FILETYPE></ROOT>'
 

SELECT @list = ISNULL( @list + ',', '' ) + x.y.value('.', 'VARCHAR(MAX)' )  
FROM @PersonsXml.nodes('ROOT/FILETYPE') x(y)

看到这个博客-
^ ]

see this blog-
SQL xml: get xml values into CSV?[^]

如何在SQL中将XML节点值转换为逗号分隔的值?

相关推荐