title: SQL Server XML file with multiple nodes named the same
body: <p>I have this inner XML which I am passing across to a SQL Server stored procedure.</p>
  
  <p><a href="https://i.stack.imgur.com/oGPKu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/oGPKu.png" alt="Sample of XML being passed to SQL Server"></a></p>
  
  <p>As you can see, it contains multiple <code>root</code> nodes but additionally, it can also contain 1 to '<code>n</code>' number of <code>LotResults</code> child nodes.</p>
  
  <p>Is there a way I can manipulate this in the stored procedure so that I can retrieve all <code>LotResults</code>/<code>Result</code> nodes for each <code>root</code> node?</p>
  
  <p>So far I have declared the <code>cursor</code> which can deal with the top-level nodes:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Note: I found a post describing <a href="https://stackoverflow.com/questions/15353668/use-sql-server-to-get-all-the-data-from-xml-nodes-named-the-same">how to extract nodes with the same name</a> 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.</p>