sql-server - SQL Server 2012 - 无法将带有 xmlns 的 XML 导入表中

Question

我正在尝试将一些节点从带有 ns 的 xml 文件导入到 sql server 表中

<?xml version="1.0" encoding="UTF-8"?>
<GetSellerTransactionsResponse xmlns="urn:ebay:apis:eBLBaseComponents">
    <Timestamp>2019-08-03T17:51:45.081Z</Timestamp>
    ...
</GetSellerTransactionsResponse>

使用此查询：

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

;WITH XMLNAMESPACES (DEFAULT 'urn:ebay:apis:eBLBaseComponents' )
insert into Users (EIASToken,Email, UserID,RegistrationDate)
SELECT distinct EIASToken,Email, UserID,RegistrationDate
FROM OPENXML(@hDoc, 'GetSellerTransactionsResponse/TransactionArray/Transaction/Buyer')
WITH 
(
    EiasToken[nvarchar](100) 'EIASToken',
    Email[nvarchar](100) 'Email',
    UserID [nvarchar](100) 'UserID',
    RegistrationDate [datetime] 'RegistrationDate',
    Name [nvarchar] (100) 'BuyerInfo/ShippingAddress/Name'
)
where EIASToken not in (select EIASToken from Users)

EXEC sp_xml_removedocument @hDoc

我以这种方式解决了，为 xmlns (ebl) 创建一个名称并添加@xmlns为第三个参数sp_xml_preparedocument，然后将 ebl: 添加到所有节点和子节点并删除;WITH XMLNAMESPACES语句：

SET @xmlns = '<GetSellerTransactionsResponse xmlns:ebl="urn:ebay:apis:eBLBaseComponents" />'
SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, @xmlns

insert into Users (EIASToken,Email, UserID,RegistrationDate,Name)
SELECT distinct EIASToken,Email, UserID,RegistrationDate,Name
FROM OPENXML(@hDoc, 'ebl:GetSellerTransactionsResponse/ebl:TransactionArray/ebl:Transaction/ebl:Buyer')
WITH 
(
EiasToken[nvarchar](100) 'ebl:EIASToken',
Email[nvarchar](100) 'ebl:Email',
UserID [nvarchar](100) 'ebl:UserID',
RegistrationDate [datetime] 'ebl:RegistrationDate',
Name [nvarchar] (100) 'ebl:BuyerInfo/ebl:ShippingAddress/ebl:Name'
)
where EIASToken not in (select EIASToken from Users)

但我很确定有办法避免ebl: 有人提出解决方案的所有问题？谢谢

-----------------

按照 marc_s 的建议，我尝试使用 XQuery 构建查询（实际上没有使用@XML.query）

;WITH XMLNAMESPACES (DEFAULT 'urn:ebay:apis:eBLBaseComponents' )
INSERT INTO dbo.Sales 
    (...) 
SELECT
    ...
FROM @XML.nodes('//OrderArray/Order/TransactionArray/Transaction') as O(X)

和

;WITH XMLNAMESPACES (DEFAULT 'urn:ebay:apis:eBLBaseComponents' )
INSERT INTO dbo.Sales 
    (...) 
SELECT
    ...
FROM @XML.nodes('//OrderArray/Order') as O(X)
CROSS APPLY O.X.nodes('//TransactionArray/Transaction') as I(X)
WHERE O.X.value('(TransactionArray/Transaction/TransactionID/text())[1]', 'nvarchar(70)')=I.X.value('(TransactionID/text())[1]', 'nvarchar(70)')

第一个解决方案返回了相同数量的记录，而第二个解决方案丢失了大约 10%。但最大的问题在于性能：

使用 1237 个“记录”解析相同的 12MB XML，我得到以下结果：

OPENXML
(1237 rows affected)
 SQL Server Execution Times:
   CPU time = 687 ms,  elapsed time = 986 ms.

-

XQuery without Cross Apply
(1237 rows affected)
 SQL Server Execution Times:
   CPU time = 14.106.641 ms,  elapsed time = 7.788.378 ms.

-

XQuery with Cross Apply
(1134 rows affected)
 SQL Server Execution Times:
   CPU time = 1.661.968 ms,  elapsed time = 1.668.166 ms.

因此OPENXML解决方案是唯一合理的，还是我犯了一些错误或应该使用@XML.query()？

标签： sql-serveropenxmlxml-namespaces