OPENXML vs sp_OACreate
-
Does anyone have any inside knowledge on what goes on 'behind the scenes' with OPENXML used with sp_xml_preparedocument and sp_xml_removedocument? I know it uses MSXML2, and that it will use up to 1/8 of the total amount of memory allocated to SQL Server, all from BOL. What I'm really interested in if it is any different from using the sp_OACreate sp to create an instance of the MSXML2 parser, and running the methods of that object with sp_OAMethod and sp_OAGetProperty.
The reason is that I have created a nice UDF that parses an XML-string using spOA... and MSXML2, but the problem is that the spOA-sprocs leak memory and I don't feel entirely comfortable with them.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
SP_oa and the rest are useful, but only in very limited circumstances I think. Why did you decide to code it yourself vs openxml?
Andy
-
Andy wrote:
> Why did you decide to code it yourself vs
openxml?
Two reasons:
1) I had some older code from a SQL 7 'experimental app' that used them so I just modified that code.
2) I didn't know about OPENXML...
I really hadn't looked into any of SQL2k's XML features, and only knew about FOR XML.I've logged the time spent parsing using either method, and they came out equal. I've changed my udf now so that I use OPENXML. Actually, I had to drop the UDF, since I couldn't use sp_xml_preparedocument inside an udf.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
PS. I'm still interested in knowing what's going on behind the scenes. DS.
Edited by - chrhedga on 09/03/2001 2:58:11 PM
-
Anyone? Otherwise I guess I'll have to wait for PASS and ask someone from MS (or Kalen Delaney perhaps), just two weeks now...
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Well OPENXML is my chapter in the book below. I have done some research into this and like chrhedga created a fucntion to do teh same as OPENXML. What is interesting is that the tiem taken to retrieve one property is the similiar useing sp_OA and OPENXML.
How ever returning multiple columns the difference become larger. this is due I believe to the overhead of the sp_oa stored procs and that each call must result in x function calls.
But what it does suggest is that they are using the msxml dom directly with no fancy stuff and thus the similiar performance.
On some of the YUKON posts I have said that this is something MS have to sort out. They need a parser built into the engine that handles extracting sets of data from an xml document.
In the book I look at the time taken to use OPENXML and show that is directly proportional to the number of rows and proptional to the number of columns return suggesting that the are builind the rowset up column by column and row by row.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
Chris, any luck getting info on this at PASS?
Andy
I really hadn't looked into any of SQL2k's XML features, and only knew about FOR XML.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply