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

将XML数据分解为各种数据库列的最佳方法是什么？到目前为止,我主要使用节点和值函数,如下所示:

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)

但是我发现即使是中等大小的xml数据,这也变得非常慢.

在遇到类似问题的同时偶然发现了这个问题,我一直在运行一个查询处理一个7.5MB的XML文件(大约10,000个节点)大约3.5~4个小时,最后放弃了.

然而,经过一番研究后,我发现使用模式键入XML并创建了一个XML索引(我将批量插入到表中),相同的查询在~0.04ms内完成.

如何提高性能!

用于创建架构的代码:

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)

希望这可以帮助别人,因为那几乎是我的一天.

在我的情况下,我正在运行SQL 2005 SP2(9.0).

唯一有帮助的是添加OPTION(OPTIMIZE FOR(@your_xml_var = NULL)).解释在以下链接中.

例:

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