Sql Server Xml File With Multiple Nodes Named The Same

I have this inner XML which I am passing across to a SQL Server stored procedure. As you can see, it contains multiple root nodes but additionally, it can also contain 1 to 'n' nu

Solution 1:

No cursors! Cursor are created by the devil to lead poor little db people away from the light of set-based thinking deep into the dark acres of procedural approaches...

Please (for future questions): Do not paste pictures! Had to type my example in...

And btw: Your use my values here makes it difficult, to advise the correct thing. Depending on what you are doing there, a cursor might be needed actually. But in this case you should create the cursor from a query like I show you...

Try it like this:

DECLARE @xml XML=
'<roots><root><ID>5</ID><LotResults><ID>13</ID><Result><ID>5</ID><Count>2</Count></Result></LotResults><LotResults><ID>13</ID><Result><ID>5</ID><Count>2</Count></Result></LotResults><StandardComment><ID>0</ID></StandardComment><ReviewComment><ID>0</ID></ReviewComment></root><root><ID>44</ID><LotResults><ID>444</ID><Result><ID>4444</ID><Count>2</Count></Result></LotResults><LotResults><ID>555</ID><Result><ID>55</ID><Count>2</Count></Result></LotResults><StandardComment><ID>5</ID></StandardComment><ReviewComment><ID>5</ID></ReviewComment></root></roots>';

--and here's the query

SELECT r.value('ID[1]','int') AS root_ID
      ,lr.value('ID[1]','int') AS LotResult_ID
      ,lr.value('(Result/ID)[1]','int') AS LotResult_Result_ID
      ,lr.value('(Result/Count)[1]','int') AS LotResult_Result_Count
      ,r.value('(StandardComment/ID)[1]','int') AS StandardComment_ID 
      ,r.value('(ReviewComment/ID)[1]','int') AS ReviewComment_ID 
FROM @xml.nodes('/roots/root') AS A(r)
CROSS APPLY r.nodes('LotResults') AS B(lr)