在遇到一个非常相似的问题时偶然发现了这个问题,我一直在运行一个处理 7.5MB XML 文件(约 10,000 个节点)的查询大约 3.5~4 小时,然后最终放弃。
然而,经过更多研究后,我发现使用模式输入 XML 并创建 XML 索引(我将批量插入到表中),相同的查询在大约 0.04 毫秒内完成。
性能提升怎么样!
创建模式的代码:
IF EXISTS ( SELECT * FROM sys.xml_schema_collections where [name] = 'MyXmlSchema')
DROP XML SCHEMA COLLECTION [MyXmlSchema]
GO
DECLARE @MySchema XML
SET @MySchema =
(
SELECT * FROM OPENROWSET
(
BULK 'C:\Path\To\Schema\MySchema.xsd', SINGLE_CLOB
) AS xmlData
)
CREATE XML SCHEMA COLLECTION [MyXmlSchema] AS @MySchema
GO
使用类型化 XML 列创建表的代码:
CREATE TABLE [dbo].[XmlFiles] (
[Id] [uniqueidentifier] NOT NULL,
-- Data from CV element
[Data] xml(CONTENT dbo.[MyXmlSchema]) NOT NULL,
CONSTRAINT [PK_XmlFiles] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
创建索引的代码
CREATE PRIMARY XML INDEX PXML_Data
ON [dbo].[XmlFiles] (Data)
但有一些事情需要牢记。 SQL Server 的 Schema 实现不支持 xsd:include。这意味着,如果您有一个引用其他架构的架构,则必须将所有这些复制到一个架构中并添加它。
我也会得到一个错误:
XQuery [dbo.XmlFiles.Data.value()]: Cannot implicitly atomize or apply 'fn:data()' to complex content elements, found type 'xs:anyType' within inferred type 'element({http://www.mynamespace.fake/schemas}:SequenceNumber,xs:anyType) ?'.
如果我尝试在我使用节点功能选择的节点上方导航。例如。
SELECT
,C.value('CVElementId[1]', 'INT') AS [CVElementId]
,C.value('../SequenceNumber[1]', 'INT') AS [Level]
FROM
[dbo].[XmlFiles]
CROSS APPLY
[Data].nodes('/CVSet/Level/CVElement') AS T(C)
发现处理此问题的最佳方法是使用 OUTER APPLY 实际上对 XML 执行“外部联接”。
SELECT
,C.value('CVElementId[1]', 'INT') AS [CVElementId]
,B.value('SequenceNumber[1]', 'INT') AS [Level]
FROM
[dbo].[XmlFiles]
CROSS APPLY
[Data].nodes('/CVSet/Level') AS T(B)
OUTER APPLY
B.nodes ('CVElement') AS S(C)
希望这对某人有帮助,因为那几乎是我的一天。