Xml Column Into Multiple Column With Separated Comma In Sql Server

Question

Using Sql Server 2012

I have table with single field within String containing xml format like below,

a
----
<data1>11</data1><data2>4</data2><data1>12</data1><data2>5</data2>

I want result table with containing separate comma for each column like this

data1|data2
-----------
11,12|4,5

Ed Bangga · Accepted Answer · 2019-09-25 07:28:27Z

2

Here's your script. Your a column is not a valid XML type, so i need to append as root element.

declare @strxml xml = concat('<t>', '<data1>11</data1><data2>4</data2><data1>12</data1><data2>5</data2>', '</t>')

SELECT
        STUFF((SELECT ',' + b.dat.value('.', 'NVARCHAR(MAX)')
               FROM   tmp.node.nodes('data1') b (dat)
               FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'),
              1, 1, '') AS a,

        STUFF((SELECT ',' + a.dat.value('.', 'NVARCHAR(MAX)')
               FROM   tmp.node.nodes('data2') a (dat)
               FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'),
              1, 1, '') AS b
FROM @strxml.nodes('/t') tmp(node);

applying your table. just replace test3 and yourcolumn

SELECT
        STUFF((SELECT ',' + b.dat.value('.', 'NVARCHAR(MAX)')
               FROM   tmp.node.nodes('data1') b (dat)
               FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'),
              1, 1, '') AS a,

        STUFF((SELECT ',' + a.dat.value('.', 'NVARCHAR(MAX)')
               FROM   tmp.node.nodes('data2') a ( dat )
               FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'),
              1, 1, '') AS b
FROM test3 t
CROSS APPLY (select cast(concat('<t>', yourcolumn, '</t>') AS xml)) as t1(X)
CROSS APPLY t1.X.nodes('t') AS tmp(node)

edited Sep 25, 2019 at 7:28

answered Sep 25, 2019 at 6:13

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

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

3 Comments

Fakhryan Albar Over a year ago

can we not using declare xml variable,

Fakhryan Albar Over a year ago

i mean like from (SELECT ... ).nodes('/t') tmp(node);

Ed Bangga Over a year ago

try now. your column is not a valid xml type, so i need to append <t></t> as root element

Akhileshkumar singh · Accepted Answer · 2019-09-25 07:49:54Z

DECLARE @XMLData XML 
SET @XMLData = '<a> 
                <data1>11</data1><data2>4</data2><data1>12</data1><data2>5</data2>
            </a>'

SELECT REPLACE
(
    @XMLData.query('data(a/data1)').value('.','varchar(100)')
    ,' ' -- replace the white spaces
    ,',' -- replace the white spaces with commas
) AS data1,

REPLACE
(
    @XMLData.query('data(a/data2)').value('.','varchar(100)')
    ,' '
    ,','
) AS data2

OR

If you don't have any Root

DECLARE @XMLData XML 
SET @XMLData = ' 
                <data1>11</data1><data2>4</data2><data1>12</data1><data2>5</data2>
            '

SELECT REPLACE
(
    @XMLData.query('data(data1)').value('.','varchar(100)')
    ,' ' -- replace the white spaces
    ,',' -- replace the white spaces with commas
) AS data1,

REPLACE
(
    @XMLData.query('data(data2)').value('.','varchar(100)')
    ,' '
    ,','
) AS data2

Note:-

1. First we have space separated value...

2. To fulfill our objective we can use Replace function to replace the spaces with comma's.

Collectives™ on Stack Overflow

Xml Column Into Multiple Column With Separated Comma In Sql Server

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related