using openXML in SP with a text parameter
-
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
-
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
-
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Thank you very much for your feedback.
-
Try this link for some options using OpenXML with Text datatype and others. Hope this helps.
http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply