将XML导入SQL Server

Question

我可以找到很多关于如何将某些类型的XML数据导入SQL Server 2005的示例。但是我收到的数据采用以下格式（重复“row”和“cell”，带有ID而不是标签名称等）：

<?xml version="1.0"?> <rows>
    <row id='1'>
        <cell id='category'>Simple</cell>
        <cell id='query'>summary</cell>
        <cell id='clientsfound'>6</cell>
        <cell id='eligibleclients'>11</cell>
        <cell id='percentage'>55</cell>
        <cell id='days'>0</cell>
    </row>

    <row id='2'>
        <cell id='category'>Complex</cell>
        <cell id='query'>details</cell>
        <cell id='clientsfound'>4</cell>
        <cell id='eligibleclients'>6</cell>
        <cell id='percentage'>67</cell>
        <cell id='days'>5</cell>
    </row>

    ...
     </rows>

理想情况下，我希望将其加载到类似表格中的位置：

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
)

有人可以给我提供一个例子或者指向一个在线教程吗？

- Jonathan

Answer 1

XML应该是 "" 而不是 ' 内部，对吧？

无论如何，您可以本地解析XML数据类型。sp_xml_preparedocument实际上是很危险的，因为它会带来内存使用的额外开销。

DECLARE @foo XML;

SET @foo = N'<?xml version="1.0"?>
<rows>
    <row id="1">
        <cell id="category">Simple</cell>
        <cell id="query">summary</cell>
        <cell id="clientsfound">6</cell>
        <cell id="eligibleclients">11</cell>
        <cell id="percentage">55</cell>
        <cell id="days">0</cell>
    </row>
    <row id="2">
        <cell id="category">Complex</cell>
        <cell id="query">details</cell>
        <cell id="clientsfound">4</cell>
        <cell id="eligibleclients">6</cell>
        <cell id="percentage">67</cell>
        <cell id="days">5</cell>
    </row>
</rows>';

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)

- gbn

Answer 2

你可以使用OPENXML和XQUERY来做到这一点。

DECLARE @XMLdoc XML
DECLARE @idoc int
SELECT @XMLdoc = '<?xml version="1.0"?>
  <rows>
    <row id="1">
        <cell id="category">Simple</cell>
        <cell id="query">summary</cell>
        <cell id="clientsfound">6</cell>
        <cell id="eligibleclients">11</cell>
        <cell id="percentage">55</cell>
        <cell id="days">0</cell>
    </row>
    <row id="2">
        <cell id="category">Complex</cell>
        <cell id="query">details</cell>
        <cell id="clientsfound">4</cell>
        <cell id="eligibleclients">6</cell>
        <cell id="percentage">67</cell>
        <cell id="days">5</cell>
    </row>
  </rows>'


-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLDoc

INSERT INTO QueryResults (RowID,Category,Query,ClientsFound,EligibleClients,Percentage,Days)
SELECT  id,
        overflow.value('(/row/cell[@id="category"])[1]', 'nchar(10)'),
        overflow.value('(/row/cell[@id="query"])[1]', 'nchar(10)'),
        overflow.value('(/row/cell[@id="clientsfound"])[1]', 'int'),
        overflow.value('(/row/cell[@id="eligibleclients"])[1]', 'int'),
        overflow.value('(/row/cell[@id="percentage"])[1]', 'int'),
        overflow.value('(/row/cell[@id="days"])[1]', 'int')
FROM OPENXML (@idoc, '/rows/row',10)
WITH (id int '@id',
    overflow xml '@mp:xmltext' --the row xml node
)

-- Release resources allocated for the XML document.
EXEC sp_xml_removedocument @idoc

SELECT * FROM QueryResults

结果：

UserString  ImportStamp        RowID  Category  Query    ClientsFound EligibleClients Percentage  Days
----------- ------------------ ------ --------- -------- ------------ --------------- ----------- ----
NULL        0x000000000000C1CA 1      Simple    summary  6            11              55          0
NULL        0x000000000000C1CB 2      Complex   details  4            6               67          5

我不确定您想在“UserString”中填充什么，但您可以稍后解决。

希望这提供了一个适当的解决方案来回答您的问题。

-- 抱歉 gbn，您关于 sp_xml_preparedocument 的看法可能是正确的。我只是从我们与 Microsoft SDC 团队合作的项目中采用了类似的存储过程方法，所以认为这应该是安全的。您的方法可能更加简洁。

- mimix

---

在SQL 2005中不需要使用sp_xml_preparedocument。 - gbn

XML处理的改变是SQL Server 2005中较好的功能之一... :-) - gbn