Get comma separated values ​​from xml data

Question

I know how to do this in a simple scenario, for example

DECLARE @commaSeparatedValues NVARCHAR(MAX) DECLARE @xml XML = N'<id>1</id> <name>test</name> <istest>1</istest>' ;WITH nodes AS ( SELECT Tbl.Col.value('.', 'nvarchar(max)') as Value FROM @xml.nodes('*/text()') Tbl(Col) ), prepareStrings AS ( SELECT IIF(ISNUMERIC(n.value) = 1, n.Value, '''' + n.Value + '''') AS Value FROM nodes n ) SELECT @commaSeparatedValues = CASE WHEN @commaSeparatedValues IS NULL THEN s.Value ELSE @commaSeparatedValues + ',' + s.value END FROM prepareStrings s SELECT @commaSeparatedValues as csv 

It works great. The problem occurs when I want to parse this way the following XML data. I am having trouble writing the correct query.

 DECLARE @xml XML = N' <e> <id>1</id> <name>test</name> <istest>1</istest> </e> <e> <id>2</id> <name>test2</name> <istest>0</istest> </e> ' 

I can get items line by line using

 select Tbl.col.query('.') as [xml] from @xml.nodes('e') Tbl(col) 

I do not know how to move on. I don’t know how to use this query and now request the [xml] column.

Answer 1

Try the SQL query below

 DECLARE @commaSeparatedValues NVARCHAR(MAX) DECLARE @xml XML = N' <e> <id>1</id> <name>test1</name> <istest>1</istest> </e> <e> <id>2</id> <name>test2</name> <istest>2</istest> </e> ' ;with cte as ( select rownr = ROW_NUMBER() over (order by @commaSeparatedValues), Tbl.col.query('.') as [xml] from @xml.nodes('e') Tbl(col) ), cols as ( select rownr, Tbl.Col.value('.', 'nvarchar(max)') as Value from cte cross apply cte.xml.nodes('//text()') Tbl(Col) ) select distinct STUFF(( SELECT ',' + IIF(ISNUMERIC(value) = 1, Value, '''' + Value + '''') FROM cols SSF WHERE SSF.rownr = S.rownr FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)' ), 1, 1, '') from cols S 

I use the SQL function row_number () to write numbers and distinguish between column values ​​when they are divided by values ​​(the second CTE uses the Partition By clause to sort the columns between row data)

Then I concatenate the string values ​​into a comma separated string using the SQL string concatenation method using XML PATH ()

I hope this helps

Answer 2

Are you looking for something like this?

 DECLARE @xml XML = N' <e> <id>1</id> <name>test</name> <istest>1</istest> </e> <e> <id>2</id> <name>test2</name> <istest>0</istest> </e>' SELECT XC.value('(id)[1]', 'varchar(10)') + ',' + XC.value('(name)[1]', 'varchar(100)') + ',' + xc.value('(istest)[1]', 'varchar(10)') FROM @Xml.nodes('/e') AS XT(XC) 

It is output:

 1,test,1 2,test2,0 

Basically, the .nodes() operator will create a “virtual list” of XML fragments (one for each <e> node in your XML document), and then select a “reach” for this XML fragment for each row in this virtual table and combine the separate parts, separated by commas, in one line