using openXML in SP with a text parameter

Question

using openXML in SP with a text parameter

Ranga N

SSChasing Mays

Points: 642
More actions
June 28, 2005 at 9:36 pm

#76362

Hi,
In one of my stored proc, i have used a text parameter to accept a huge XML string and using openXML to read and insert the data in sql table. IS it a good idea to have a text parameter or it is better to get the path of the xml file and use it...
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

peterhe SSChampion Points: 11469 More actions · Answer 1

I have several SPs that accept nText parameters, which are parsed later by OPENXML.

In my opinion, passing Text/nText parametr is the only practical way to do this. The reason is SQL server is not good at reading files (it can but not good at it). If the file is bigger than 8000 bytes, you cannot hold it in a local variable in your SP, thus not easy to pass it to sp_xml_preparedocument. Besides, you need to take of file access permissions, security etc.

If the content of the file is passed as a parameter of the SP, you can use it directly when you call the sp_xml_preparedocument. You do not need to declare a local variable to hold it. The client side handles all the file accesses and permission issues

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Everything Peter said...

The caveat that you have to be aware of is memory. Every document handle takes a certain amount of memory. Then the document itself chews up memory. Memory management is the one thing that can really hurt an XML query.

Other than that, you're on the right track, like Peter said.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning