SQLServerCentral / Discuss Content Posted by Raj Vasant / Article Discussions / Article Discussions by Author / Using OpenXML / Latest Posts

RE: Using OpenXML

Simon Sabin — Thu, 05 Mar 2009 10:49:53 GMT

Its all about how the columns in the with are matched when you don't specify an xpath literal i.ewith (name varchar(100) ,age int)With attribute centric mapping this will look for attributes of the elements matched in the OPENXML clause, i.e. @name and @age in whereas element centric mapping looks at sub elements. i.e.

RE: Using OpenXML

zacmush — Thu, 05 Mar 2009 10:41:59 GMT

One thing about the article that I did not find clarification on was the difference between the flags 1 and 2: in bol it says if 1 used and combined with XML_Elements then attribute centric mapping is applied first and then the element centric mapping applied to those columns not handled while if 2 is used with XML_Attributes then attribute centric mapping is still applied first and then the element centric mapping is applied to the remaining columns not handled. I found this very odd and haven't found an explanation for this oddness- other places have kept repeating that. Can anyone clarify this?

RE: Using OpenXML

Ronald Avendaño — Fri, 21 Nov 2008 17:43:18 GMT

Is "8 - Combined with XML_ATTRIBUTES or XML_ELEMENTS" the right parameter, i think this may be 3

RE: Using OpenXML

stephensunil — Fri, 23 Feb 2007 20:30:00 GMT

Excellent Article. Can u please tell about Image handling using BCP.It is really time consuming action to save the image in the db.

RE: Using OpenXML

latinsky — Fri, 23 Feb 2007 12:59:00 GMT

the reason we are using OPENXML in SQL Server 2000 is we get a list of integers from a source and we need to pass them into a stored procedure to be turned into a table to be joined with some destination table in order to filter out records that don't meet the search criteria. We cannot use comma-delimited strings since we never know how many values will be passed in. That's why we fomat integers as attributese and pass them as a text variable to a stored procedure which shreds the xml and inserts the field into a table variable. The problem we are having with OPENXML is poor response times when the number of records passed in is more than 150000. The following graph show how long (in seconds) it takes to obtain a hanle and open the document respectively.

Number Of Records	Obtain handle	Open XML
300000	15	45
200000	10	25
100000	5	10
80000	3	8
40000	2	4
20000	1.5	2

I could not find any alternative in SQL Server 2000 to achive better results when dealing with big files except for saving it somewhere on a hard drive and using the file path and a bcp utility to load from a file.(I really do not want to do this).

RE: Using OpenXML

Jonathan Scott-Sheldon — Fri, 23 Feb 2007 08:08:00 GMT

I found this article weak. All information presented is available in the BOL. So RTFM people.

I actually got more benefit by reading the comments posted about the article. Specifically with respect to performance, using temp tables and releasing the xml document as early as possible.

RE: Using OpenXML

Vasant Raj — Fri, 23 Feb 2007 00:13:00 GMT

The parameter passed to the stored procedure can be assigned the XML file from the application.

For example: In .NET,

- Create a dataset.

- Get XML-string using the <DataSetName>.GetXml() function.

- Pass this XML-string to the procedure.

RE: Using OpenXML

Arthur P. Jammz — Wed, 21 Feb 2007 13:58:00 GMT

SQL Server 2000 (not 2005) Question:Where you say, "Passing the XML document to the SQL server is just the same as passing any other text," for us new people, how do you pass any other text?I'm missing something here. I'm confused at how the data in an xml file, say c:\XMLDocs\MyData.xml in the file system somewhere, gets passed into the @strXML VARCHAR(2000) variable in Raj's example. Every example in every book I've seen (including BOL) shows the XML hard-coded into the sp_xml_preparedocument staement, which is of course worthless. Here's an example from BOL, Which shows the XML hard-coded in the SET @doc= statement (with leading "<" symbols removed, so it will show up in the post): declare @idoc int declare @doc varchar(1000) set @doc =" -- <------- This part is what I'm talking about.... ROOT> Customers CustomerID="VINET" ContactName="Paul Henriot"> Orders CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00"> Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/> Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/> /Orders> /Customers> Customers CustomerID="LILAS" ContactName="Carlos Gonzlez"> Orders CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"> Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/> /Orders> /Customers> /ROOT>" --Create an internal representation of the XML document. exec sp_xml_preparedocument @idoc OUTPUT, @doc -- SELECT statement using OPENXML rowset provider SELECT * FROM OPENXML (@idoc, '/ROOT/Customers') EXEC sp_xml_removedocument @idocUsing a sproc to pass a varchar(2000) (or TEXT) variable sounds like a much better idea, but how do you load from the .XML file into the varchar(2000) variable, to pass into the sproc in the first place?Thanks...

RE: Using OpenXML

bijulsoni — Fri, 06 Oct 2006 00:29:00 GMT

good article vasant You done a nice job

RE: Using OpenXML

ThomasLN — Fri, 12 May 2006 13:04:00 GMT

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.

RE: Using OpenXML

Gunjan Moghe — Tue, 04 Apr 2006 08:03:00 GMT

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...

RE: Using OpenXML

Simon Sabin — Tue, 21 Feb 2006 15:44:00 GMT

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.

RE: Using OpenXML

Jim-153675 — Thu, 09 Jun 2005 21:31:00 GMT

I don't understand the benefit outlined in the introduction.

Even with SQL 6.5 you could have simply submitted the 100 insert statements in one big string and executed it (ADO/ASP/.Net has supported this for years). 1 trip, 100 records. The extra T-SQL involved should be the same size as the extra XML tags involved.

i.e. cn.execute "INSERT Table VALUES ('Jim') INSERT Table VALUES ('Bob')"

The non-XML method should also be easier because you don't need to figure out how to transfer the xml file to the other server (assuming the sql server was on a different machine then your code). Transferring one XML file is easy. Transferring multiple XML files due to multiple users/threads will have implications (not to mention cleanup). Why bother when there is no benefit?

Of course, if you really needed to insert a LOT of records you wouldn't use either of these methods.

RE: Using OpenXML

Ramya-236512 — Thu, 02 Jun 2005 03:47:00 GMT

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

RE: Using OpenXML

tymberwyld — Fri, 13 May 2005 08:21:00 GMT

Well, if you can make it for one table, I can probably modify the Proc to handle any table. Also, I can have the Proc auto-magically figure out the PKeys for the Table it's importing, so don't worry about that either.

RE: Using OpenXML

Vasant Raj — Fri, 13 May 2005 04:19:00 GMT

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.

RE: Using OpenXML

Vasant Raj — Fri, 13 May 2005 04:11:00 GMT

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...

RE: Using OpenXML

Grant Fritchey — Thu, 12 May 2005 11:28:00 GMT

That's good to know. We were doing it for performance reasons, not because of failures in the code, but if we dodged a bullet, so much the better.

RE: Using OpenXML

rhein — Thu, 12 May 2005 10:47:00 GMT

Just to clarify:

In our case we were calling sp_xml_removedocument EVERY time. The problem we ran into is not from the orphans, but from repeated selects from the xml. So, I agree that it's vital to call sp_xml_removedocument everytime, but it's also vital to only make a limited number of selects directly from the xml, which BOL doesn't emphasis enough. Also, the problem we ran into wasn't a server crash, just an unexpected failure of stored proccedure.

Like you, our practice now is to make only one select against the xml - so you can get it into a temp table or table variable, then close it.

I just wanted to mention this so others don't have to go thru the headache I went thru.

RE: Using OpenXML

Michael Rauch — Thu, 12 May 2005 10:32:00 GMT

Yes, it is critically important to call sp_xml_removedocument. The statement in the article "This handle is valid for until the connection is reset, or until the execution of sp_xml_removedocument." is correct but does not mention the bug you ran into. If you do not call sp_xml_removedocument the handle is orphaned and will not be released. Overtime your handle count is going to increase and your server will eventually crash. (You can watch the handle count increase for sqlservr.exe by using the process tab of the task manager and adding the column for handles.)

Therefore our standard is to read the xml document into a temp table and immediately call sp_xml_removedocument before there is a chance to orphan the handle.

RE: Using OpenXML

rhein — Thu, 12 May 2005 09:25:00 GMT

A followup to you your comment about

"Generally, if we run into a situation where the XML is used in more than a single statement, we load the data into a temp table and close the document as soon as possible before doing more processing"

It is CRITICALLY important to do this. There is actually a bug in OpenXML that will cause you to get unexpected crashes when you use the OpenXML over and over. It might not bite you every time, but I've had first hand experience with it.

We had an application that we used OpenXml with in testing. The stored proceedure had been finished and had been working for 6 months when one day, the stored proccedure starte crashing for NO reason. - The XML was valid, and had worked the day before, and nothing had changed, but it simply started crashing. A consultant who was working with us, who had worked in the SQL group at Microsoft said that he had seen that before, and to do the OpenXML, get the data into table variables, and close the xml as quickly as possible. We ended up implimenting that change, which was fairly trivial and poof - the problem disappeared as quickly as it had appeared. I seem to recall that the root of the problem was a memory leak in the OpenXML code.

RE: Using OpenXML

tymberwyld — Thu, 12 May 2005 07:38:00 GMT

Could someone give me an example of how I might be able to import an XML Doc that has this structure? I currently use this structure when Exporting / Importing data from any table in my DB. It's simple and understandable. Sometimes, this file can be about 50MB though. How would that be possible using a Stored Proc?<DATASOURCE Name="Users"> <ROW> <COLUMN Name="ID"><![CDATA[1]]></COLUMN> <COLUMN Name="FirstName"><![CDATA[George]]></COLUMN> <COLUMN Name="LastName"><![CDATA[Wilson]]></COLUMN> </ROW> <ROW> <COLUMN Name="ID"><![CDATA[2]]></COLUMN> <COLUMN Name="FirstName"><![CDATA[Martha]]></COLUMN> <COLUMN Name="LastName"><![CDATA[Wilson]]></COLUMN> </ROW></DATASOURCE>

RE: Using OpenXML

Grant Fritchey — Thu, 12 May 2005 07:28:00 GMT

"can we also use a path where the XML file exists."

I'm pretty sure that you can't reference files directly from within TSQL. If you really, really HAD to do this, you have to open a command shell and/or create objects with sp_oa* procedures. I'd strongly recommend against both. They're basically stepping outside of the realm where TSQL peforms consistently and well.

You know that you can pass procedures a TEXT data type so that you're not limited by the 8K of varchar (or 4k of nvarchar)? That's how we do it now. If you have to load really huge files, instead of using OPENXML, look into XML Bulk load in BOL.

RE: Using OpenXML

Subhash-141882 — Thu, 12 May 2005 07:12:00 GMT

Good Article Vasant, wrote in a very simple and easy way.

I have one doubt here about sp_xml_preparedocument.

In BOL and also in the mentioned article XML document/content is assigning to a varchar variable and using in sp_xml_preparedocument. Instead of using the variable, can we also use a path where the XML file exists. If so how do we need to mention.

Can any one help regarding this with a simple syntax?

Thanks,

Subhash

RE: Using OpenXML

Grant Fritchey — Thu, 12 May 2005 06:29:00 GMT

Nice article.

You might consider adding information about performance management. For example, maintaining the document handle across multiple queries and/or procedure calls is pretty expensive. Generally, if we run into a situation where the XML is used in more than a single statement, we load the data into a temp table and close the document as soon as possible before doing more processing. It shows quite a performance improvement.

Also, another performance issue, we found that element centric XML code is pretty fat, causing more networking and memory issues(every column has <dude></dude> as well as the data as well as the rows). As a standard in our organization we use attribute centric (where columns get @dude="", reducing the file size and memory footprint considerably).

Excellently written.

RE: Using OpenXML

vickster — Thu, 12 May 2005 05:17:00 GMT

Thought this was great myself, keep em coming!

RE: Using OpenXML

Agson Chellakudam — Wed, 11 May 2005 23:51:00 GMT

Good Article!!..Simple presentation....Keep it up..

Using OpenXML

Vasant Raj — Wed, 04 May 2005 15:04:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/usingopenxml.asp