Transform XML record to table

Question

I have XML data in table "sample" column name "xmlrec" that looks like:

<row id='1'>
 <c1>rec1</c1>
 <c2>a</c2>
 <c2>b</c2>
 <c2>c</c2>
</row>

The same needs to be transformed as below:

c1      c2
----    ----
rec1    a
rec1    b
rec1    c

Please help

Answer 1

You can use CROSS APPLY xmlrec.nodes('/row/c2') to find all c2 nodes. Finding the corresponding c1 is easy:

SELECT n.value('(../c1)[1]', 'VARCHAR(100)') AS c1
     , n.value('.', 'VARCHAR(100)') AS c2
FROM sample
CROSS APPLY xmlrec.nodes('/row/c2') AS x(n)

Answer 2

I'd prefer forward-navigation. It is possible to use ../c1 as in the other answer, but - as far as I know - performance is not the best. This is an alternative:

DECLARE @xml XML =
N'<row id="1">
 <c1>rec1</c1>
 <c2>a</c2>
 <c2>b</c2>
 <c2>c</c2>
</row>';

SELECT A.r.value('@id','int') AS row_id
      ,A.r.value('(c1/text())[1]','nvarchar(10)') AS c1
      ,B.c2.value('text()[1]','nvarchar(10)') AS c2
FROM @xml.nodes('/row') A(r)
CROSS APPLY A.r.nodes('c2') B(c2);

If there is only one <row> within your XML, this was best

SELECT @xml.value('(/row/@id)[1]','int') AS row_id
      ,@xml.value('(/row/c1/text())[1]','nvarchar(10)') AS c1
      ,B.c2.value('text()[1]','nvarchar(10)') AS c2
FROM @xml.nodes('/row/c2') B(c2);