我正在使用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 />元素不以正确的顺序存储在其表中.
任何人都可以向我解释为什么< t />元素是否以与XML文档中出现的顺序相同的顺序插入到表中？
解决方法
如果我在sql Server中使用本机XQuery支持而不是“遗留”OPENXML内容,那么看起来< t>节点确实按照它们出现在XML文档中的顺序插入到表中.
我使用过这样的代码：
INSERT INTO dbo.[Transactions]([ID],[EncryptedAccountID])
SELECT
XT.value('@id','uniqueidentifier'),XT.value('@encryptedAccountId','varchar(200)')
FROM
@xmlTransaction.nodes('/ts/t') AS Nodes(XT)
对于< tv>也可以这样做.子节点也是.
