Tricky one...
i m working on it ... wait for some time
Are you in need of a generic stored proc. (to insert data in any table) ???
If not, then solution is simple...
nice suggestion for using temp tables...
Its better to use them if the XML files are large...
If any exceptions occurs, temporary tables are not destroyed from the memory.
This can be a big problem.
Instead, one can use variable of TABLE Type.
thnx.
Good job.
But in practical scenario where we have a huge data, say 2,000,000 records to be inserted into the database, the OpenXML command doesnt seem to be satisfactory.We are facing this problem where we have used this OpenXML which consumes about 15secs for inserting 65,000 records in a single user environment.When it is concurrent users, the performance of the entire system is poor.
We came across another component 'XML Bulk Load Component' for the same scenario.But wasnt able to proceed much on this.Would like to have comments from anyone who have knowledge on this.
Thanks & Regards,
Ramya
If you are interested in using OPENXML I strongly suggest that you get hold of a copy of SQL Server XML Distilled. Why you may ask, well I wrote the OPENXML chapter. It covers, the pros and cons of element and attribute centric documents. Performance of using OPENXML relating to document size, attributes, namespaces.
There are lots of gotchas to be wary of, type casting, nulls, case sensitivity, use of complex xpath.
I also investigate the memory issue, its not as cut and dried as the 1/8th statement suggests, and generally depends on available memory at startup
You can't pass in a filename, but in 2005 you can read the contents of a file into an xml variable and then use that.
Passing the XML document to the SQL server is just the same as passing any other text. exec up_myXMLProc '<root><element1></element1> ....</root>', No clean up is neccesary except the use of sp_xml_removedocument, which should be called after every openxml call.
As for the benefits, the one is the saving of hierachical data, you only have to make one SP call rather than one for the parent and one for each child. These can be batched up which is why in testing both ways work out to be pretty much the same.
In 2005 the parser has been updated so you can use more xpath functions, in addition you can use xquery.
So lots of good things.
hi,
Using Open XML was a article full of information. It was partucurly helpful for me because ur simple and easy to understand presentation.keep posting...
Very good, well orgainzed article with everything needed for simple XML queries.
But it seems to me that responsible developers should avoid the temptation to use new technologies simply because of their cool value. XML has its uses for hierarchical representation of data and as a communication format for web applications (because of firewall transparency).
However, for the examples provided in the original article, the equivalent code in T/SQL is much simpler and does not require the use of Temp tables (and thus tempdb) or table variables which have their own particular limitations.
In addition, for SQL Server 2000 applications XML is not optimized by the query optimizer and thus can be a performance liability depending on the complexity of the query. I don't know about 2005, but I assume that it's implementation is similar though query syntax is greatly simplified.
I would simply advise anyone thinking of using XML in SQL Server 2000 to do so for the right reasons and ensure that their code remains efficient, readable and maintainable.
