Shred & Concat XML in one column
which is an easy way to output the output concatenated into one column for all parameter values in the example below, this xml does not have ns in it and I am using SQL Server 2012.
I am trying to get this output, below snippet with sample xml produce 3 columns and I would like to do 1.
100 | Param1
,Param22
,Param3322
XML and code:
DECLARE @xml XML = '<Parameters>
<Parameter>
<Name>Param1</Name>
</Parameter>
<Parameter>
<Name>Param22</Name>
</Parameter>
<Parameter>
<Name>Param3322</Name>
</Parameter> </Parameters> '
SELECT 100 id
,@XML xmlinfo
INTO #t -- drop table #t -- select * from #t
DECLARE @xml XML = (
SELECT xmlinfo
FROM #t
)
SELECT (
SELECT ID
FROM #t
) AS ID
,X.STockData.query('Name[1]').value('.', 'Varchar(10)') AS 'Parameter'
---CONCAT(X.STockData.query('Name[1]').value('.','Varchar(10)'),X.STockData.query('Name[2]').value('.','Varchar(10)')) AS 'Parameter'
FROM @xml.nodes('Parameters/Parameter') AS X(StockData)
source to share
You can use an XQuery loop for
to construct a comma separated value from an XML field, for example:
SELECT
t.ID as ID
, CAST(t.xmlinfo.query('
for $p in Parameters/Parameter
return
if ($p is (Parameters/Parameter[last()])[1])
then string($p/Name[1])
else concat($p/Name[1], ", ")
')
AS VARCHAR(MAX)
) as Parameter
FROM MyTable as t
The XQuery above just Parameter
loops through the elements and returns the child Name
if the current one Parameter
is the last Parameter
, otherwise it returns Name
concatenated with a comma.
source to share
SELECT (
SELECT ID
FROM #t
) AS ID
,X.STockData.query('Name[1]').value('.', 'Varchar(10)')
+ ', ' + X.STockData.query('Name[2]').value('.', 'Varchar(10)')
+ ', ' + X.STockData.query('Name[3]').value('.', 'Varchar(10)')
as parameter
FROM @xml.nodes('Parameters/Parameter') AS X(StockData)
try it
source to share