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
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)
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.
