問(wèn)題描述
我嘗試使用 XQuery .nodes 將 XML 分解到一個(gè)臨時(shí)表中，如下所示.但是，我遇到了性能問(wèn)題.粉碎需要很多時(shí)間.請(qǐng)給我一個(gè)關(guān)于此替代方案的想法.
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.
我的要求是將批量記錄傳遞給存儲(chǔ)過(guò)程并解析這些記錄并根據(jù)記錄值執(zhí)行一些操作.
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() 節(jié)點(diǎn).
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() 將創(chuàng)建一個(gè)查詢(xún)計(jì)劃，該計(jì)劃嘗試將來(lái)自指定節(jié)點(diǎn)的值與其所有子節(jié)點(diǎn)連接起來(lái)，我想在這種情況下您不希望這樣.如果您不使用 text()，則查詢(xún)的連接部分由 UDX 運(yùn)算符完成，最好不要在您的計(jì)劃中使用它.
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 文檔)，我發(fā)現(xiàn) OPENXML 執(zhí)行速度更快.
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
這篇關(guān)于SQL 中 XML 粉碎的替代方案的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！