SQL 中 XML 粉碎的替代方案

2021-10-01 00:00:00 xml sql sql-server

我尝试使用 XQuery .nodes 将 XML 分解到一个临时表中，如下所示.但是，我遇到了性能问题.粉碎需要很多时间.请给我一个关于此替代方案的想法.

I tried to shred XML into a temporary table by using XQuery .nodes as follows. But, I got performance problem. It is taking much time to shred. Please give me an idea on alternatives for this.

我的要求是将批量记录传递给存储过程并解析这些记录并根据记录值执行一些操作.

My requirement is to pass bulk records to a stored procedure and parse those records and do some operation based on record values.

 CREATE TABLE #DW_TEMP_TABLE_SAVE(  
[USER_ID] [NVARCHAR](30), 
[USER_NAME] [NVARCHAR](255)
)   

insert into #DW_TEMP_TABLE_SAVE
   select 
       A.B.value('(USER_ID)[1]', 'nvarchar(30)' ) [USER_ID], 
       A.B.value('(USER_NAME)[1]', 'nvarchar(30)' ) [USER_NAME]
   from 
       @l_n_XMLDoc.nodes('//ROW') as A(B)

推荐答案

在 values 子句中指定 text() 节点.

Specify the text() node in your values clause.

insert into #DW_TEMP_TABLE_SAVE
select A.B.value('(USER_ID/text())[1]', 'nvarchar(30)' ) [USER_ID], 
       A.B.value('(USER_NAME/text())[1]', 'nvarchar(30)' ) [USER_NAME]
from @l_n_XMLDoc.nodes('/USER_DETAILS/RECORDSET/ROW') as A(B)

不使用 text() 将创建一个查询计划，该计划尝试将来自指定节点的值与其所有子节点连接起来，我想在这种情况下您不希望这样.如果您不使用 text()，则查询的连接部分由 UDX 运算符完成，最好不要在您的计划中使用它.

Not using text() will create a query plan that tries concatenate the values from the specified node with all its child nodes and I guess you don't want that in this scenario. The concatenation part of the query if you don't use text() is done by the UDX operator and it is a good thing not to have it in your plan.

另一件要尝试的事情是 OPENXML.在某些情况下(大型 xml 文档)，我发现 OPENXML 执行速度更快.

Another thing to try is OPENXML. In some scenarios (large xml documents) I have found that OPENXML performs faster. 

declare @idoc int
exec sp_xml_preparedocument @idoc out, @l_n_XMLDoc

insert into #DW_TEMP_TABLE_SAVE
select USER_ID, USER_NAME
from openxml(@idoc, '/USER_DETAILS/RECORDSET/ROW', 2) 
  with (USER_ID  nvarchar(30), USER_NAME nvarchar(30))

exec sp_xml_removedocument @idoc

相关文章