get comma separated values from xml data

Question

I know how to do it in the simplest scenario, e.g.

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

This works perfectly. Problem arises when I want to parse this way the following xml data. I have problems with writing the proper 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 the elements row by row by using

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

What I don't know is how to move forward with this. Don't know how to use this query and now querying the [xml] column.

Gallstone

Answer 1

Please try the below SQL query

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 SQL row_number() function to number records and distinguish column values when they are separated into values (the second CTE uses Partition By clause to sort columns among row data)

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

I hope it helps

Thorman

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)

This outputs:

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 the selects "reach" into that XML fragment for each row in that virtual table and concat together the individual pieces, separated by commas, into a single string

Countershaft

I forgot to mention, that the xml elements names will differ. I'm looking for universal piece of code. As you can see in my previous post, I have not used any element name. If any new element in the xml will be added, I want this element value in the result too. – Gallstone