Help Retrieving XML into Tables

Question

Help Retrieving XML into Tables

Ruprecht

SSC Eights!

Points: 968
More actions
July 1, 2008 at 12:53 pm

#94302

Hello All,
I'm new to the XML world and I'm having a bit of difficulty. How can you retrieve XML data from an XML column into a table when there are multiple (hundreds) of data rows in each column instance? I'm able to select a given row from the column using the value() method, but I can't find a way to select all of the rows from the XML at one time into a table.
All columns are in the attached format.
Any help would be greatly appreciated!
Thanks,
Ruprecht

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

DB_Andrew Ten Centuries Points: 1089 More actions · Answer 1

OPEN XML Method.

Note that this allocates 1/8 of your servers memory when you call sp_XML_preparedocument. My server has 10gigs and sits at 8743mb avail on average so its no biggie.

DECLARE @XML XML

SET @XML = 'PUT YOUR XML HERE '

DECLARE @xmldoc INT

EXEC sp_XML_preparedocument @xmldoc OUTPUT, @XML

SELECT

debt_id

,payment_amount

,[user_id]

,payment_date

,create_user

FROM OPENXML (@xmldoc, '/row',2)

WITH(

debt_id VARCHAR(30)

,payment_amount MONEY

,[user_id] VARCHAR(20)

,payment_date DATETIME

,create_user INT

)

EXEC sp_xml_removedocument @xmldoc

DB_Andrew Ten Centuries Points: 1089 More actions · Answer 2

If you don't like OPEN XML, or server dosn't have the resources to support it.

Here is the Xquery method.

DECLARE @XML XML

SET @XML = 'YOUR XML GOES HERE'

SELECT

x.value('debt_id[1]','VARCHAR(30)') AS debt_id

,x.value('payment_amount[1]','MONEY') AS payment_amount

,x.value('user_id[1]','VARCHAR(20)') AS [user_id]

,x.value('payment_date[1]','datetime') AS payment_date

,x.value('create_user[1]','int') AS payment_date

FROM

@XML.nodes('/row') y(x);

Daniel Millstone SSC Veteran Points: 216 More actions · Answer 3

In both scenarios, is there a way for it to error if the XML value exceeds the column definition? For example, debt_id turns out to be 35 characters and not the defined the 30. When I've tested this scenario, the extra characters are trimmed and only the first 30 characters are displayed.

DB_Andrew Ten Centuries Points: 1089 More actions · Answer 4

I don't know what the size of your data types are. I was just guessing when I set those values. If you need support for something larger just bump it up.

But to answer your question. No, not that I'm aware of. You'll just need to set something large enough to handle what your dealing with.

Ruprecht SSC Eights! Points: 968 More actions · Answer 5

Thanks so much for all help, DB_Andrew. Both the Open XML and XQuery methods work perfectly.

I didn't know the procedure call took such a significant fraction of the system's resources. In this situation, resources won't be an issue so I've decided to go with the Open XML method. But in the future, I'll need to use the XQuery method on a different server.

Thanks again!

Ruprecht

DB_Andrew Ten Centuries Points: 1089 More actions · Answer 6

Your welcome.

TIP:

For small amounts of XML use Xquery. (If you cut and past your xml into a text file it should be < 5MB)

For Large amounts of XML use Open XML. (5MB+)

These sizes may very. You'll get different results based upon your hardware and how busy your SQL Server is.

I was trying to read 10mb+ worth of XML, this was 1000 records worth once inserted into my table with 15 columns, nothing larger then a VARCHAR(30))

and it was taking around 1min to process this much XML and I was using hints [1] etc. Each 1000 records wroth of XML I added would double the time. 1000 = 1 min, 2000 = 2 min , 3000 = 4 min etc.

I switched to Open XML and It would do it in 1 second. I tested 1000, 2000, 3000, up to 10,000 rows worth of XML and it was always 1 second per 1000 rows using Open XML.

Belive it or not the Open XML was much faster then calling 1 stored procedure 1000 - 10,000 times once for each row. The stored procedure was MUCH faster then the Xquery method.