title: Using OPENXML in SQL Server 2008 stored proc - INSERT order differs from XML document
body: <p>I'm using SQL Server 2008's XML-parsing abilities to iterate through an XML document and perform an INSERT each element.</p>
  
  <p>However, my stored procedure appears to be inserting each element into the table in an order which differs from the order in the document.</p>
  
  <p>Furthermore, the more times I try this, the INSERT order seems to change.</p>
  
  <p>Here's a sample of the XML document - nothing too fancy going on here.</p>
  
  <pre><code>&lt;ts&gt;
      &lt;t id="36a3c8c1-b958-42f0-82d1-dfa6bf9b99a1" encryptedAccountId="fQ/XF8lpeR9wEDUV3yMzvQ==" uploaded="2012-04-03T15:49:19.9615097Z" visible="1"&gt;
          &lt;tv fieldId="301" officialValue="0, 0" friendlyValue="0, 0" /&gt;
          &lt;tv fieldId="302" officialValue="0, 1" friendlyValue="0, 1" /&gt;
          &lt;tv fieldId="303" officialValue="0, 2" friendlyValue="0, 2" /&gt;
          &lt;tv fieldId="304" officialValue="0, 3" friendlyValue="0, 3" /&gt;
          &lt;tv fieldId="305" officialValue="0, 4" friendlyValue="0, 4" /&gt;
          &lt;tv fieldId="306" officialValue="0, 5" friendlyValue="0, 5" /&gt;
      &lt;/t&gt;
      &lt;t id="9d56d082-4b6a-4bdf-a7a2-f5c6af88344e" encryptedAccountId="fQ/XF8lpeR9wEDUV3yMzvQ==" uploaded="2012-04-03T15:49:19.9615097Z"  visible="1"&gt;
          &lt;tv fieldId="301" officialValue="1, 0" friendlyValue="1, 0" /&gt;
          &lt;tv fieldId="302" officialValue="1, 1" friendlyValue="1, 1" /&gt;
          &lt;tv fieldId="303" officialValue="1, 2" friendlyValue="1, 2" /&gt;
          &lt;tv fieldId="304" officialValue="1, 3" friendlyValue="1, 3" /&gt;
          &lt;tv fieldId="305" officialValue="1, 4" friendlyValue="1, 4" /&gt;
          &lt;tv fieldId="306" officialValue="1, 5" friendlyValue="1, 5" /&gt;
      &lt;/t&gt;
      &lt;t id="27db47a3-ad3f-4279-8f4f-0a8944ce32d4" encryptedAccountId="fQ/XF8lpeR9wEDUV3yMzvQ==" uploaded="2012-04-03T15:49:19.9615097Z" visible="1"&gt;
          &lt;tv fieldId="301" officialValue="2, 0" friendlyValue="2, 0" /&gt;
          &lt;tv fieldId="302" officialValue="2, 1" friendlyValue="2, 1" /&gt;
          &lt;tv fieldId="303" officialValue="2, 2" friendlyValue="2, 2" /&gt;
          &lt;tv fieldId="304" officialValue="2, 3" friendlyValue="2, 3" /&gt;
          &lt;tv fieldId="305" officialValue="2, 4" friendlyValue="2, 4" /&gt;
          &lt;tv fieldId="306" officialValue="2, 5" friendlyValue="2, 5" /&gt;
      &lt;/t&gt;
      &lt;t id="867ea26b-0341-4d60-ac48-f305492a60f0" encryptedAccountId="fQ/XF8lpeR9wEDUV3yMzvQ==" uploaded="2012-04-03T15:49:19.9615097Z" visible="1"&gt;
          &lt;tv fieldId="301" officialValue="3, 0" friendlyValue="3, 0" /&gt;
          &lt;tv fieldId="302" officialValue="3, 1" friendlyValue="3, 1" /&gt;
          &lt;tv fieldId="303" officialValue="3, 2" friendlyValue="3, 2" /&gt;
          &lt;tv fieldId="304" officialValue="3, 3" friendlyValue="3, 3" /&gt;
          &lt;tv fieldId="305" officialValue="3, 4" friendlyValue="3, 4" /&gt;
          &lt;tv fieldId="306" officialValue="3, 5" friendlyValue="3, 5" /&gt;
      &lt;/t&gt;
  &lt;/ts&gt;
  </code></pre>
  
  <p>The stored procedure has a few operations taking place, but I've commented-out other parts leaving only the SQL which inserts the <code>&lt;t/&gt;</code> elements and then the <code>&lt;tv/&gt;</code> elements.</p>
  
  <p>The SQL in the stored procedure is as follows.</p>
  
  <p>(<code>@xmlTransaction</code> is an <code>NVARCHAR (MAX)</code> input param containing the above XML)</p>
  
  <pre><code>BEGIN
      SET NOCOUNT ON;
  
      DECLARE @encryptedAccountID AS VARCHAR(200)
  
      BEGIN TRANSACTION
          BEGIN TRY
              DECLARE @Handle AS INT
              DECLARE @TransactionCount AS INT
  
              EXEC sp_xml_preparedocument @Handle OUTPUT, @xmlTransaction
  
              /* encryptedAccountId is always the same for each @xmlTransaction param */
              /* Just take the value from the first &lt;t/&gt; element */
              SET @encryptedAccountID = (SELECT eID FROM OPENXML (@Handle, '/ts/t[1]', 2) WITH ( eID VARCHAR '@encryptedAccountId' ))
  
              /* Go through each &lt;t/&gt; element in the XML document and INSERT */
              INSERT INTO
              [Transactions] 
              (
                  [ID],
                  [EncryptedAccountID]
              )
              SELECT
                  *
              FROM
                  OPENXML (@Handle, '/ts/t', 2)
              WITH
              (
                  rID UNIQUEIDENTIFIER '@id',
                  rEncryptedAccountID VARCHAR (200) '@encryptedAccountId'
              )
  
              /* Loop through each TransactionValue in the XML document and INSERT */
              INSERT INTO
              [TransactionValues]
              (
                  FieldID,
                  TransactionID,
                  OfficialValue,
                  FriendlyValue
              )
              SELECT
                  *
              FROM
                  OPENXML (@Handle, '/ts/t/tv', 2)
              WITH
              (
                  rFieldID INT '@fieldId',
                  rTransactionID UNIQUEIDENTIFIER '../@id',
                  rOfficialValue NVARCHAR (500) '@officialValue',
                  rFriendlyValue NVARCHAR (500) '@friendlyValue'
              )
  
              /* Dispose of the XML document */
              EXEC sp_xml_removedocument @Handle
  
          COMMIT TRANSACTION
      END TRY
      BEGIN CATCH
  
          RETURN @@ERROR
  
          ROLLBACK TRANSACTION        
      END CATCH
  
  END
  </code></pre>
  
  <p>Should be fairly straightforward. And yet if I query the results, they're not in the same order as the XML document. The second INSERT statement for the <code>&lt;tv/&gt;</code> elements <em>does</em> store the elements into a second table in the correct order, but the  <code>&lt;t/&gt;</code> elements are <em>not</em> stored in their table in the correct order.</p>
  
  <p>Can anyone explain to me why the  <code>&lt;t/&gt;</code> elements are not being INSERTed into the table in the same order as they appear in the XML document?</p>