将XML导入SQL Server

我可以找到很多关于如何将某些类型的XML数据导入SQL Server 2005的示例.但是我已经获得了以下格式的数据(用ID代替"row"和"cell"而不是命名的标签等:

 
    
        Simple
        summary
        6
        11
        55
        0
    

    
        Complex
        details
        4
        6
        67
        5
    

    ...
     

理想情况下,我想将其加载到表中,例如:

CREATE TABLE [dbo].[QueryResults](
    [UserString] [varchar](50) NULL,
    [ImportStamp] [timestamp] NULL,
    [RowID] [int] NULL,
    [Category] [nchar](10) NULL,
    [Query] [nchar](10) NULL,
    [ClientsFound] [int] NULL,
    [EligibleClients] [int] NULL,
    [Percentage] [int] NULL,
    [Days] [int] NULL
)

有人能为我提供一个示例或指向在线教程吗？

xml应该是""不是'内部,不是？

无论如何,您可以本机解析XML数据类型.由于内存使用量的开销,sp_xml_preparedocument非常危险.

DECLARE @foo XML;

SET @foo = N'

    
        Simple
        summary
        6
        11
        55
        0
    
    
        Complex
        details
        4
        6
        67
        5
    
';

SELECT
    x.item.value('@id', 'int') AS RowID,
    y.item.value('(./cell[@id="category"])[1]', 'nchar(10)') AS category,
    y.item.value('(./cell[@id="query"])[1]', 'nchar(10)') AS query,
    y.item.value('(./cell[@id="clientsfound"])[1]', 'int') AS clientsfound,
    y.item.value('(./cell[@id="eligibleclients"])[1]', 'int') AS eligibleclients,
    y.item.value('(./cell[@id="percentage"])[1]', 'int') AS percentage,
    y.item.value('(./cell[@id="days"])[1]', 'int') AS days
FROM
    @foo.nodes('/rows/row') x(item)
    CROSS APPLY
    x.item.nodes('.') AS y(item)