CODEWITHSUNDEEP
sqlsql-serversqlxml
Saravanan
Saravanan

Reputation: 293

SQL Server - convert XML Column to delimited string

Gurus, I have XML Column value like below in SQL 2012

'<XMLDoc>
<AAA>
  <Name>Name_A</Name>
  <Value>Val_A</Value>
</AAA>
<AAA>
  <Name>Name_B</Name>
  <Value>Val_B</Value>
</AAA>
<AAA>
  <Name>Name_C</Name>
  <Value>Val_C</Value>
</AAA>
<AAA>
  <Name>Name_D</Name>
  <Value>Val_D</Value>
</AAA>
<AAA>
  <Name>Name_E</Name>
  <Value>Val_E</Value>
</AAA>
:
:
<AAA>
  <Name>Name_Z</Name>
  <Value>Val_Z</Value>
</AAA>
</XMLDoc>'

We have requirement to store first 2 nodes in individual columns and the remaining nodes (length may be 0 to n) as delimited string in third column.

I have tried SQL as below, need help to populate 3rd column

SELECT Col1 =  Col.value('(/AAA/Value)[1]', 'varchar(255)') ,
 Col2 =  Col.value('(/AAA/Value)[2]', 'varchar(255)') 
FROM table

We need Output as

Col1               Col2                    Col3
Val_A              Val_B                   Val_c,Val_D,Val_E....Val_n

Upvotes: 2

Views: 1489

Answers (3)

Ajay2707
Ajay2707

Reputation: 5808

You can check this...I taken some part from @svain.

declare @xmlstr varchar(1000) = '<XMLDoc>
<AAA>
  <Name>Name_A</Name>
  <Value>Val_A</Value>
</AAA>
<AAA>
  <Name>Name_B</Name>
  <Value>Val_B</Value>
</AAA>
<AAA>
  <Name>Name_C</Name>
  <Value>Val_C</Value>
</AAA>
<AAA>
  <Name>Name_D</Name>
  <Value>Val_D</Value>
</AAA>
<AAA>
  <Name>Name_E</Name>
  <Value>Val_E</Value>
</AAA>
</XMLDoc>'

declare @xml xml = (select cast( @xmlstr as xml))


select 
    distinct    
    xmlnodes.idnode.value('(/XMLDoc/AAA/Value)[1]', 'varchar(255)') as bb,  
    xmlnodes.idnode.value('(/XMLDoc/AAA/Value)[2]', 'varchar(255)') as dd,
    Col3 = (SELECT ', '+xmlnodes.idnode.value('text()[1]', 'varchar(max)')
               FROM @xml.nodes('/XMLDoc/AAA[position()>2]/Value') as xmlnodes(idnode)   
               FOR XML PATH(''), type).value('substring(text()[1], 3)', 'varchar(max)')
from 
    @xml.nodes('/XMLDoc/AAA') as xmlnodes(idnode)

Upvotes: 0

Svein Fidjest&#248;l
Svein Fidjest&#248;l

Reputation: 3216

The following T-SQL should solve your problem in SQL Server 2012, using a subquery with FOR XML PATH

SELECT Col1 =  Col.value('(/XMLDoc/AAA/Value)[1]', 'varchar(255)'),
       Col2 =  Col.value('(/XMLDoc/AAA/Value)[2]', 'varchar(255)'),
       Col3 = (SELECT ', '+P.N.value('text()[1]', 'varchar(max)')
               FROM [table].Col.nodes('/XMLDoc/AAA[position()>2]/Value') P(N)
               FOR XML PATH(''), type).value('substring(text()[1], 3)', 'varchar(max)')
FROM [table]

Upvotes: 0

Saravanan
Saravanan

Reputation: 293

Gurus, I was able to solve this question by below SQL

SELECT Col1 =  Col.value('(/XMLDoc/AAA/Value)[1]', 'varchar(255)') ,
 Col2 =  Col.value('(/XMLDoc/AAA/Value)[2]', 'varchar(255)') ,
Col3 = STUFF( (SELECT ',' + x.value('(Value)[1]', 'varchar(50)') FROM Col.nodes('/XMLDoc/AAA[position()>2]') AS Node(x) FOR XML PATH('')), 1, 1, '')
FROM table

Let me know if any better solution

Upvotes: 1

Related Questions