将 XML 导入 SQL Server [英] Importing XML into SQL Server

问题描述

我可以找到很多关于如何将某些类型的 XML 数据导入 SQL Server 2005 的示例.但是我得到了以下格式的数据(用 ID 而不是标记重复行"和单元格")命名等:

I can find lots of example on how to import certain types of XML data into SQL Server 2005. But I've been given data in the following format (repeating "row" and "cell" with ID's instead of the tags been named etc:

<?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>

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

Ideally I want to load it into a table such as:

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
)

有人能给我提供一个例子或指向一个在线教程吗?

Can someone provide me with an example or point to towards a online tutorial?

推荐答案

内部的 xml 应该是 "" 而不是 '，不是吗?

The xml should be "" not ' internally, no?

无论如何，您可以本地解析 XML 数据类型.sp_xml_preparedocument 坦率地说是危险的，因为内存使用的开销很大.

Anyway, you can parse the XML datatype natively. sp_xml_preparedocument is frankly dangerous because of the overhead of memory usage.

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)

这篇关于将 XML 导入 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！