Hi,
If you're using
SQL Server 2008 or above, you can do it in this way:
CREATE TABLE #TempTable
(ID INT,
Name VARCHAR(50),
xmlData XML);
INSERT INTO #TempTable (ID, Name, xmlData)
VALUES (1, 'ABC', '<row><d1>10</d1><d2>25</d2></row>'),
(2, 'XYZ', '<row><d1>12</d1><d2>11</d2><d3>15</d3></row>');
;WITH Data
AS (SELECT ID,
Name,
xmlCol = C.value('local-name(.)', 'VARCHAR(50)'),
[Value] = C.value('./.', 'INT')
FROM #TempTable
CROSS APPLY xmlData.nodes('/row/*') AS T(C))
SELECT ID, Name, xmlCol, [Value]
FROM Data;
DROP TABLE #TempTable;
So here I'm using
CTE
,
CROSS APPLY
and
XQuery
to parse XML and transform the data into desired output.
Result:
ID Name xmlCol Value
1 ABC d1 10
1 ABC d2 25
2 XYZ d1 12
2 XYZ d2 11
2 XYZ d3 15