Reputation:
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?
Upvotes: 6
Views: 14438
Reputation: 71
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.
Upvotes: 1
Reputation: 432657
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)
Upvotes: 9