xml 选择查询 xpath 很慢
我的 XML 结构:
<Items>
<Item>
<guid>FC550573-7171-997F-752D-8D65590CBFD6</guid>
<Objects>
<Object>
<type>0</type>
<guid>E10D9DA9-2C8D-8024-2F07-DF21395811BF</guid>
</Object>
<Object>
<type>0</type>
<guid>D8338400-35C7-781E-A039-C0FDDF80714A</guid>
</Object>
</Objects>
</Item>
</Items>
填充对象表时:
CREATE TABLE [dbo].[Objects](
[item_guid] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[type] [int] NOT NULL,
[guid] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
使用查询:
INSERT INTO [dbname].[dbo].[Objects]
([item_guid]
,[type]
,[guid])
SELECT
X.source.query('../../guid').value('.','VARCHAR(36)') as item_guid,
X.source.query('type').value('.','INT') as type,
X.source.query('guid').value('.','VARCHAR(36)') as guid
FROM(
Select xmldata from XmlFiles where fullpath=@fp
) AS T(x)
CROSS APPLY x.nodes('Items/Item/Objects/Object') As X(source)
这一行使查询变得非常慢:
This line is making the query VERY slow:
X.source.query('../../guid').value('.','VARCHAR(36)') as item_guid
这里的正确方法是什么?
What is the proper approach here?
推荐答案
使用 /text()
获取值有利于非类型化 XML 的性能.使用父轴 ../..
也可能不好(如@marc_s 建议的那样).
Using /text()
to get the value is good for performance on untyped XML. It can also be bad to use the parent axis ../..
(as @marc_s suggested).
这是一个带有额外交叉应用和 /text()
来获取值的版本.
Here is a version with a extra cross apply and /text()
to get the values.
试试这个:
select T2.N.value('(guid/text())[1]', 'uniqueidentifier') as item_guid,
T3.N.value('(type/text())[1]', 'int') as type,
T3.N.value('(guid/text())[1]', 'uniqueidentifier') as guid
from (SELECT xmldata FROM dbo.XmlFiles WHERE fullpath = @fp) as T1(N)
cross apply T1.N.nodes('Items/Item') as T2(N)
cross apply T2.N.nodes('Objects/Object') as T3(N)
您必须判断哪个查询对您来说最快.
You have to be the judge which query is the fastest for you.
相关文章