将XML数据分解为SQL Server数据库列的最佳方法

在遇到一个非常类似的问题时偶然发现了这个问题,在最终放弃之前,我已经运行了一个查询处理一个7.5MB的XML文件(大约10000个节点)大约3.5~4个小时。

这对性能的提高有什么好处!

创建架构的代码:

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)

另外,我会得到一个错误:

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)

发现处理此问题的最佳方法是使用外部应用程序对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)

希望这能帮助到别人,因为这是我的一天。

解释见下面的链接。

例子:

INSERT INTO @tbl (Tbl_ID, Name, Value, ParamData)
SELECT     1,
    tbl.cols.value('name[1]', 'nvarchar(255)'),
    tbl.cols.value('value[1]', 'nvarchar(255)'),
    tbl.cols.query('./paramdata[1]')
FROM @xml.nodes('//root') as tbl(cols) OPTION ( OPTIMIZE FOR ( @xml = NULL ) )

https://connect.microsoft.com/SQLServer/feedback/details/562092/an-insert-statement-using-xml-nodes-is-very-very-very-slow-in-sql2008-sp1

INSERT INTO Test
SELECT Id, Data 
FROM OPENXML (@XmlDocument, '/Root/blah',2)
WITH (Id   int         '@ID',
      Data varchar(10) '@DATA')

为了加快速度,可以创建XML索引。您可以专门为以下对象设置索引:

我们这里也有类似的问题。我们的DBA(SP,你就是那个人)查看了我的代码,对语法做了一些调整,我们得到了我们期望的速度。这是不寻常的,因为我从XML中选择的速度非常快,但插入速度非常慢。因此,请尝试以下语法:

INSERT INTO some_table (column1, column2, column3)
    SELECT 
        Rows.n.value(N'(@column1/text())[1]', 'varchar(20)'), 
        Rows.n.value(N'(@column2/text())[1]', 'nvarchar(100)'), 
        Rows.n.value(N'(@column3/text())[1]', 'int')
    FROM @xml.nodes('//Rows') Rows(n) 

因此,指定text()参数似乎真的能提高性能。将我们插入的2K行从“我一定写错了-让我停下来”改为大约3秒。这比我们在连接中运行的原始insert语句快2倍。

我不认为这是“最佳”解决方案,但我已经为此编写了一个通用的SQL CLR过程——它采用“表格”Xml结构(如for Xml RAW返回的结构)并输出一个结果集。

它不需要对Xml中“表”的结构进行任何定制/了解,而且速度非常快/效率非常高(尽管这不是设计目标)。我只是在20秒内切碎了一个25MB(非类型化)的xml变量,返回了一个相当宽的表中的25000行。

http://architectshack.com/ClrXmlShredder.ashx

这不是一个答案,更多的是对这个问题的补充——我刚刚遇到了同样的问题,我可以给出edg在评论中要求的数字。

我的测试使用xml,结果插入了244条记录,因此插入了244个节点。

我正在重写的代码平均运行时间为0.4秒。(运行10个测试,从.56秒扩展到.344秒)性能不是重写代码的主要原因,但新代码需要同样或更好的性能。这段旧代码循环xml节点,调用sp在每个循环中插入一次

使用新代码切换的测试表明,新sp平均需要3.7秒,几乎慢了10倍。

INSERT INTO some_table (column1, column2, column3)
SELECT
Rows.n.value('(@column1)[1]', 'varchar(20)'),
Rows.n.value('(@column2)[1]', 'nvarchar(100)'),
Rows.n.value('(@column3)[1]', 'int'),
FROM @xml.nodes('//Rows') Rows(n)

我将不得不放弃这段代码,我认为任何优化都无法克服这种方法固有的缓慢。我将尝试sp_xml_preparedocument/OPENXML方法,看看它的性能是否更好。如果有人在web搜索中遇到这个问题(就像我一样),我强烈建议您在SQL Server中使用这种类型的分解之前进行一些性能测试

有一个 XML Bulk load COM对象( .NET Example

从…起 MSDN :

语句和OPENXML函数; 需要插入大量XML 数据

显然存在一些缺陷,比如不必要地使用数据集,然后首先将整个文档加载到内存中。我想在将来更进一步,实现我自己的IDataReader来绕过DataSet方法,但目前DataSet对于这项工作来说“足够好”。

基本上,我从来没有找到一个解决我最初的问题的方法,即那种XML分解的性能很慢。它可能很慢,因为类型化xml查询本身就很慢,或者与事务和SQL Server日志有关。我猜类型化xml函数从来都不是为处理非平凡的节点大小而设计的。

是 虽然速度很快,但我在64位环境下使用COM dll时遇到了麻烦,我通常会尽量避免使用不再受支持的COM dll。