在 SQL Server 2005 中将 XML 转换为表 [英] Convert XML to table in SQL Server 2005

问题描述

如果我将一个 xml 参数传递给一个如下所示的存储过程:

If I pass in an xml parameter to a stored proc which looks like this:

<ClientKeys>
   <ck>3052</ck>
   <ck>3051</ck>
   <ck>3050</ck>
   <ck>3049</ck>
   ...
 </ClientKeys>

...然后将 XML 转换为这样的临时表:

...and then convert the XML to a temp table like this:

CREATE TABLE #ClientKeys ( ClientKey varchar(36) )  
INSERT INTO #ClientKeys (ClientKey) 
     SELECT ParamValues.ck.value('.','VARCHAR(36)')  
    FROM @ClientKeys.nodes('/ClientKeys/ck') as ParamValues(ck)   

...临时 tbl 已填充，一切都很好.然而，填充所述表所花费的时间与 xml 中ck"元素的数量严格成正比——这是我没想到的，因为没有迭代步骤.因此填充 tbl 所花费的时间很快就会变得太长".

...the temp tbl is populated and everything is good. However the time taken to populate said table is strictly proportionate to the number of 'ck' elements in the xml - which I wasn't expecting as there is no iterative step. And thus the time taken to populate the tbl soon becomes 'too long'.

有没有更快的方法来实现上述目标?

Is there a quicker way to achieve the above?

推荐答案

仅仅因为你的源代码不包含任何循环语法并不意味着没有迭代发生.如果您的代码在一个表中插入 10 行，则它总是比插入 1 行需要 10 倍的时间.

Just because your source code does not contain any looping syntax does not mean there is no iteration happening. If your code is inserting 10 rows into a table, it will always take 10 times longer than inserting 1 row.

这篇关于在 SQL Server 2005 中将 XML 转换为表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！