Concatenating Values Within SQL XML field

Question

I have a table in SQL Server 2012 which has an XML field. The field contains arrays (the number of elements is not constant) in the following format:

<values>
    <value>A</value>
    <value>B</value>
    <value>C</value>
    <value>D</value>
</values>

and I would like to turn it into a varchar like this:

'A;B;C;D'

I have tried:

SELECT myField.value('.', 'NVARCHAR(50)')
FROM   myTable

which creates 'ABCD' but I don't know how to delimit it (In the real case they are not single character values).

bvr · Accepted Answer · 2013-07-19 09:16:17Z

1

Try this

DECLARE @myTable TABLE (id int,myField XML)

INSERT INTO @myTable(id,myField) VALUES(1,'<values>
    <value>A</value>
    <value>B</value>
    <value>C</value>
    <value>D</value>
</values>')


;WITH xmltable
AS
(
    SELECT id, myField.v.value('.', 'varchar(200)') AS myField
    FROM @myTable 
    CROSS APPLY myField.nodes('/values/value') AS myField(v)
)

SELECT STUFF((SELECT ';' + myField 
                  FROM xmltable t2 
                  WHERE t2.id = t1.id  
                  FOR XML PATH('')),1,1,'') AS myField
FROM xmltable t1
GROUP BY id

answered Jul 19, 2013 at 9:16

bvr

4,8361 gold badge23 silver badges24 bronze badges

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

1 Comment

John Kiernander Over a year ago

That works thanks, I was hoping for something simpler but I'm beginning to think it isn't possible.

John Kiernander · Accepted Answer · 2013-07-19 08:54:24Z

0

I've thought of a hack to achieve this...

SELECT REPLACE(
           REPLACE(
               REPLACE(
                   CAST([myField] As NVARCHAR(MAX)), 
                   '<values><value>', 
                   ''), 
               '</value></values>', 
               ''), 
           '</value><value>', 
           ';'
       ) As [Hacked] 
FROM   [myTable]

...but it makes me feel a bit dirty. There has to be a better way.

answered Jul 19, 2013 at 8:54

John Kiernander

4,9041 gold badge17 silver badges29 bronze badges

Collectives™ on Stack Overflow

Concatenating Values Within SQL XML field

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related