Converting XML node values to comma separated values in SQL

Question

I am trying to convert XML node values to comma separated values but, getting a

Incorrect syntax near the keyword 'SELECT'. error message

declare @dataCodes XML = '<Root>
                    <List Value="120" />
                    <List Value="110" />
                </Root>';

DECLARE   @ConcatString VARCHAR(MAX)
SELECT   @ConcatString = COALESCE(@ConcatString + ', ', '') + Code FROM (SELECT T.Item.value('@Value[1]','VARCHAR(MAX)') as Code  FROM  @dataCodes.nodes('/Root/List') AS T(Item))
SELECT   @ConcatString AS Result
GO

I tried to follow an article but not sure how to proceed further. Any suggestion is appreciated.

Expectation:

Comma separated values ('120,110') stored in a variable.

Of course you can use general-purpose languages like VB, C#, C++, Java, Python, PHP which excellently handles data conversion of flat files (xml, csv, txt, json, etc.) even results from RDMS connections. SQL is a special purpose language and should primarily be used to interact with databases (retrieval, manipulation, definition). — Parfait
– Parfait, Commented Oct 1, 2015 at 15:59

Hozikimaru · Accepted Answer · 2015-10-01 15:29:13Z

4

Try this;

DECLARE @dataCodes XML = '<Root>
                    <List Value="120" />
                    <List Value="110" />
                </Root>';      

DECLARE @ConcatString VARCHAR(MAX)

SELECT @ConcatString = COALESCE(@ConcatString + ', ', '') + Code
FROM (
    SELECT T.Item.value('@Value[1]', 'VARCHAR(MAX)') AS Code
    FROM @dataCodes.nodes('/Root/List') AS T(Item)
    ) as TBL

SELECT @ConcatString AS Result
GO

You just need to add an alias to your sub SQL query.

answered Oct 1, 2015 at 15:29

Hozikimaru

1,1661 gold badge9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parfait · Accepted Answer · 2015-10-02 02:47:25Z

For future readers, XML data can be extracted into arrays, lists, vectors, and variables for output in comma separated values more fluidly using general purpose languages. Below are open-source solutions using OP's needs taking advantage of XPath.

Python

import lxml.etree as ET

xml = '<Root>\
         <List Value="120" />\
         <List Value="110" />\
       </Root>'

dom = ET.fromstring(xml)
nodes = dom.xpath('//List/@Value')

data = []  # LIST
for elem in nodes:
    data.append(elem)

print((", ").join(data))

120, 110

PHP

$xml = '<Root>
         <List Value="120" />
         <List Value="110" />
       </Root>';

$dom = simplexml_load_string($xml);    
$node = $dom->xpath('//List/@Value');

$data = [];   # Array
foreach ($node as $n){  
     $data[] = $n; 
}

echo implode(", ", $data);

120, 110

R

library(XML)

xml = '<Root>
         <List Value="120" />
         <List Value="110" />
       </Root>'

doc<-xmlInternalTreeParse(xml)    
data <- xpathSApply(doc, "//List", xmlGetAttr, 'Value')  # LIST

print(paste(data, collapse = ', '))

120, 110

kevinpo · Accepted Answer · 2022-12-02 21:10:56Z

0

To do this without a variable, you can use the nodes method to convert the xml nodes into a table format with leading commas, then use FOR XML PATH('') to collapse it into a single line of XML, then wrap that in STUFF to convert it to varchar and strip off the initial leading comma:

DECLARE @dataCodes XML = '<Root>
                <List Value="120" />
                <List Value="110" />
            </Root>';      
            
SELECT STUFF(
    (
    SELECT ', ' + T.Item.value('@Value[1]', 'VARCHAR(MAX)')
        FROM @dataCodes.nodes('/Root/List') AS T(Item)
        FOR XML PATH('')
    ), 1, 2, '')

answered Dec 2, 2022 at 21:10

kevinpo

1,99321 silver badges22 bronze badges

Collectives™ on Stack Overflow

Converting XML node values to comma separated values in SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related