Importing XML into SQL Server

Question

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?

Answer 1

You can do it using OPENXML and 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

Results:

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

I'm not sure what you want populated in 'UserString', but you can sort that out later.

Hope this provides a suitable solution to your question.

-- Sorry gbn, you're probably right about sp_xml_preparedocument. I just took this approach from some similar stored procs we had on a project we worked on with Microsoft SDC team, so figured it'd be safe. You're approach is probably cleaner anyway.

Answer 2

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

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)

Answer 3

I have almost same problem but I try to do this trough C# and Visual Studio 2008. Any suggestion for doing this in C#

Thanks in advice