SQL Server XML file with multiple nodes named the same

Question

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' number of LotResults child nodes.

Is there a way I can manipulate this in the stored procedure so that I can retrieve all LotResults/Result nodes for each root node?

So far I have declared the cursor which can deal with the top-level nodes:

DECLARE cur CURSOR FOR
   SELECT tab.col.value('ID[1]','NCHAR(10)') as INT_TransactionID,
          tab.col.value('ResultDateTime[1]','INT') as DAT_ResultDateTime,
          tab.col.value('StandardComment[1]/ID[1]','BIT') as INT_StandardCommentID,
          tab.col.value('ReviewComment[1]/ID[1]','BIT') as INT_ReviewCommentID
     FROM @XML_Results.nodes('/roots/root') AS tab(col)

     OPEN cur
-- loop over nodes within xml document and populate declared variables
    FETCH 
     NEXT 
     FROM cur

     INTO @INT_TransactionID, 
          @DAT_ResultDateTime, 
          @INT_StandardCommentID, 
          @INT_ReviewCommentID 

    WHILE @@FETCH_STATUS = 0
    BEGIN

    BEGIN
    -- use my values here

      END

    -- fetch next record
    FETCH 
     NEXT 
     FROM cur
     INTO @INT_TransactionID, 
          @DAT_ResultDateTime, 
          @INT_StandardCommentID, 
          @INT_ReviewCommentID 

      END

    CLOSE cur;

Note: I found a post describing how to extract nodes with the same name and I feel like it is something that can be used to achieve what I want to do here but I need some guidance on how this can be applied to my scenario.

score 3 · Accepted Answer · answered Aug 04 '16 at 07:32

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)

@Schnugo thanks for getting back. Apologies for the print screen of the XML, will know for any future posts. With regards to the omission of inner-cursor content, this is because I wanted to get above sorted first. Basically, the cursor will iterate over each top level `root` node, extract all relevant data and `UPDATE` a number of tables. Do you have any advice which could prevent the use of a cursor in this case? — Sandman, Aug 04 '16 at 07:39
My advise for this: Place a `INTO #myTempResult` right before the `FROM`. This will shuffle the whole result into one newly created table with the appropriate structure created automatically. After this use `INSERT INTO MyTable(col1,col2,...) SELECT DISTINCT col1,col2,... FROM #myTempResult`. You'll need one `insert` for each table you are writing to. All set-based, no cursor... — Shnugo, Aug 04 '16 at 07:43
Thanks for the advice. I will give this a go and avoid the dreaded devil cursor ;) — Sandman, Aug 04 '16 at 08:11
@Sandman, yes! Come back into the shining light of set-base thinking. Always good to rescue a damned soul :-) Happy Coding! — Shnugo, Aug 04 '16 at 08:12

SQL Server XML file with multiple nodes named the same

1 Answers1

Linked