在SQL Server 2008中行使OPENXML存储进程 – INSERT呼吁与XML文

我正在行使SQL Server 2008的 XML理会成果来迭代 XML文档并执行INSERT每个元素.

可是,我的存储进程好像是凭证与文档中的次序差异的次序将每个元素插入到表中.

另外,我实行这次的次数越多,INSERT次序好像就会产生变革.

这是XML文档的一个示例 – 没有什么太花哨的了.

<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>

存储进程产生了一些操纵,但我已经注释掉其他部门,只留下插入< t />的SQL.元素,然后< tv />元素.

存储进程中的SQL如下所示.

(@xmlTransaction是包括上述XML的NVARCHAR(MAX)输入参数)

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

应该相等简朴.然而,假如我查询功效,它们与XML文档的次序差异. < tv />的第二个INSERT语句元素确实以正确的次序将元素存储到第二个表中,但< t />元素不以正确的次序存储在其表中.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!