我有一个可变大小的XML文档,需要在MSsql 2008 R2上解析,如下所示：
<data item_id_type="1" cfgid="{4F5BBD5E-72ED-4201-B741-F6C8CC89D8EB}" has_data_event="False">
<item name="1">
<field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2">0.506543009706267</field>
<field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2">-0.79500402346138</field>
<field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2">0.0152649050024924</field>
</item>
<item name="2">
<field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2">0.366096802804087</field>
<field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2">-0.386642801354842</field>
<field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2">0.031671174184115</field>
</item>
</data>
.
我想要的是
我需要将其转换成如下所示的常规表类型数据集：
item_name field_id field_type field_value --------- ------------------------------------ ----------- --------------- 1 EA032B25-19F1-4C1B-BDDE-3113542D13A5 2 0.5065430097062 1 71014ACB-571B-4C72-9C9B-05458B11335F 2 -0.795004023461 1 740C36E9-1988-413E-A1D5-B3E5B4405B45 2 0.0152649050024 2 EA032B25-19F1-4C1B-BDDE-3113542D13A5 2 0.3660968028040 2 71014ACB-571B-4C72-9C9B-05458B11335F 2 -0.386642801354 2 740C36E9-1988-413E-A1D5-B3E5B4405B45 2 0.0316711741841 3 EA032B25-19F1-4C1B-BDDE-3113542D13A5 2 0.8839620369590 3 71014ACB-571B-4C72-9C9B-05458B11335F 2 -0.781459993268 3 740C36E9-1988-413E-A1D5-B3E5B4405B45 2 0.2284423515729
.
什么工作
create table #temp (x xml)
insert into #temp (x)
values ('
<data item_id_type="1" cfgid="{4F5BBD5E-72ED-4201-B741-F6C8CC89D8EB}" has_data_event="False">
<item name="1">
<field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2">0.506543009706267</field>
<field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2">-0.79500402346138</field>
<field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2">0.0152649050024924</field>
</item>
<item name="2">
<field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2">0.366096802804087</field>
<field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2">-0.386642801354842</field>
<field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2">0.031671174184115</field>
</item>
<item name="3">
<field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2">0.883962036959074</field>
<field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2">-0.781459993268713</field>
<field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2">0.228442351572923</field>
</item>
</data>
')
select c.value('(../@name)','varchar(5)') as item_name,c.value('(@id)','uniqueidentifier') as field_id,c.value('(@type)','int') as field_type,c.value('(.)','nvarchar(15)') as field_value
from #temp cross apply
#temp.x.nodes('/data/item/field') as y(c)
drop table #temp
.
问题
当有几百个(或更少)< item> XML中的元素,查询执行得很好.然而,当有1,000个项目时,元素,需要24秒才能完成返回SSMS中的行.当有6,500个项目时元素,运行交叉申请查询大约需要20分钟.我们可以有10-20,000个项目元素.
.
问题
什么使得交叉应用查询在这个简单的XML文档上执行得如此糟糕,并且随着数据集增长而呈指数级慢？
解决方法
What makes the cross apply query perform so poorly on this simple XML
document,and perform exponentially slower as the dataset grows?
查询计划的这一部分是有问题的.
注意423行从较低的表值函数中出来.
只需添加一个具有三个现场节点的项目节点就可以.
返回732行.
如果我们将节点从第一个查询加倍到总共6个项目节点呢？
我们最多回到1602行.
顶部函数中的图18是XML中的所有字段节点.我们有6个项目,每个项目有三个字段.这18个节点用于嵌套循环连接到另一个函数,所以18个执行返回1602行,每次迭代返回89行.恰好恰好是整个XML中节点的确切数量.那么它实际上比所有可见节点多一个.我不知道为什么您可以使用此查询来检查XML中的节点总数.
select count(*)
from @XML.nodes('//*,//@*,//*/text()') as T(X)
因此,当您在值函数中使用父轴时,sql Server用于获取值的算法是它首先查找所有要切碎的节点,最后一个情况下为18.对于每个节点,它会分片并返回整个XML文档,并检查您实际需要的节点的过滤器运算符.你有你的指数增长.
而不是使用父轴,您应该使用一个额外的交叉应用.首先在项目上,然后在现场.
select I.X.value('@name',F.X.value('@id',F.X.value('@type',F.X.value('text()[1]','nvarchar(15)') as field_value
from #temp as T
cross apply T.x.nodes('/data/item') as I(X)
cross apply I.X.nodes('field') as F(X)
我也改变了如何访问字段的文本值.使用.将使sql Server去查找子节点进行字段并将结果中的值连接起来.您没有子值,所以结果是一样的,但是避免在查询计划(UDX运算符)中具有该部分是件好事.
