Using OPENXML in SQL Server 2008 stored proc - INSERT order differs from XML document

Question

I'm using SQL Server 2008's XML-parsing abilities to iterate through an XML document and perform an INSERT each element.

However, my stored procedure appears to be inserting each element into the table in an order which differs from the order in the document.

Furthermore, the more times I try this, the INSERT order seems to change.

Here's a sample of the XML document - nothing too fancy going on here.

<ts>
    <t id="36a3c8c1-b958-42f0-82d1-dfa6bf9b99a1" encryptedAccountId="fQ/XF8lpeR9wEDUV3yMzvQ==" uploaded="2012-04-03T15:49:19.9615097Z" visible="1">
        <tv fieldId="301" officialValue="0, 0" friendlyValue="0, 0" />
        <tv fieldId="302" officialValue="0, 1" friendlyValue="0, 1" />
        <tv fieldId="303" officialValue="0, 2" friendlyValue="0, 2" />
        <tv fieldId="304" officialValue="0, 3" friendlyValue="0, 3" />
        <tv fieldId="305" officialValue="0, 4" friendlyValue="0, 4" />
        <tv fieldId="306" officialValue="0, 5" friendlyValue="0, 5" />
    </t>
    <t id="9d56d082-4b6a-4bdf-a7a2-f5c6af88344e" encryptedAccountId="fQ/XF8lpeR9wEDUV3yMzvQ==" uploaded="2012-04-03T15:49:19.9615097Z"  visible="1">
        <tv fieldId="301" officialValue="1, 0" friendlyValue="1, 0" />
        <tv fieldId="302" officialValue="1, 1" friendlyValue="1, 1" />
        <tv fieldId="303" officialValue="1, 2" friendlyValue="1, 2" />
        <tv fieldId="304" officialValue="1, 3" friendlyValue="1, 3" />
        <tv fieldId="305" officialValue="1, 4" friendlyValue="1, 4" />
        <tv fieldId="306" officialValue="1, 5" friendlyValue="1, 5" />
    </t>
    <t id="27db47a3-ad3f-4279-8f4f-0a8944ce32d4" encryptedAccountId="fQ/XF8lpeR9wEDUV3yMzvQ==" uploaded="2012-04-03T15:49:19.9615097Z" visible="1">
        <tv fieldId="301" officialValue="2, 0" friendlyValue="2, 0" />
        <tv fieldId="302" officialValue="2, 1" friendlyValue="2, 1" />
        <tv fieldId="303" officialValue="2, 2" friendlyValue="2, 2" />
        <tv fieldId="304" officialValue="2, 3" friendlyValue="2, 3" />
        <tv fieldId="305" officialValue="2, 4" friendlyValue="2, 4" />
        <tv fieldId="306" officialValue="2, 5" friendlyValue="2, 5" />
    </t>
    <t id="867ea26b-0341-4d60-ac48-f305492a60f0" encryptedAccountId="fQ/XF8lpeR9wEDUV3yMzvQ==" uploaded="2012-04-03T15:49:19.9615097Z" visible="1">
        <tv fieldId="301" officialValue="3, 0" friendlyValue="3, 0" />
        <tv fieldId="302" officialValue="3, 1" friendlyValue="3, 1" />
        <tv fieldId="303" officialValue="3, 2" friendlyValue="3, 2" />
        <tv fieldId="304" officialValue="3, 3" friendlyValue="3, 3" />
        <tv fieldId="305" officialValue="3, 4" friendlyValue="3, 4" />
        <tv fieldId="306" officialValue="3, 5" friendlyValue="3, 5" />
    </t>
</ts>

The stored procedure has a few operations taking place, but I've commented-out other parts leaving only the SQL which inserts the <t/> elements and then the <tv/> elements.

The SQL in the stored procedure is as follows.

(@xmlTransaction is an NVARCHAR (MAX) input param containing the above XML)

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 <t/> element */
            SET @encryptedAccountID = (SELECT eID FROM OPENXML (@Handle, '/ts/t[1]', 2) WITH ( eID VARCHAR '@encryptedAccountId' ))

            /* Go through each <t/> 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

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 <tv/> elements does store the elements into a second table in the correct order, but the <t/> elements are not stored in their table in the correct order.

Can anyone explain to me why the <t/> elements are not being INSERTed into the table in the same order as they appear in the XML document?

Answer 1

If I use the native XQuery support in SQL Server instead of the "legacy" OPENXML stuff, then it would appear that the <t> nodes are indeed inserted into the table in the order they appear in the XML document.

I've used code something like this:

INSERT INTO dbo.[Transactions]([ID], [EncryptedAccountID])
   SELECT
        XT.value('@id', 'uniqueidentifier'),
        XT.value('@encryptedAccountId', 'varchar(200)')
   FROM
      @xmlTransaction.nodes('/ts/t') AS Nodes(XT)

The same could be done for the <tv> subnodes, too.