Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Using OpenXML Expand / Collapse
Author
Message
Posted Wednesday, May 4, 2005 3:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 8, 2013 7:23 AM
Points: 199, Visits: 136
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/usingopenxml.asp
Post #179995
Posted Wednesday, May 11, 2005 11:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 24, 2014 10:58 PM
Points: 79, Visits: 74
Good Article!!..Simple presentation....Keep it up..
Post #181699
Posted Thursday, May 12, 2005 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 9, 2008 6:55 AM
Points: 1, Visits: 3
Thought this was great myself, keep em coming!


Post #181769
Posted Thursday, May 12, 2005 6:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:15 AM
Points: 14,005, Visits: 28,387

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.



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #181796
Posted Thursday, May 12, 2005 7:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 2, 2010 1:04 AM
Points: 110, Visits: 22

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

Post #181814
Posted Thursday, May 12, 2005 7:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:15 AM
Points: 14,005, Visits: 28,387

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



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #181826
Posted Thursday, May 12, 2005 7:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
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>



Post #181835
Posted Thursday, May 12, 2005 9:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 13, 2005 8:27 AM
Points: 2, Visits: 1

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.

 

 

 




Post #181913
Posted Thursday, May 12, 2005 10:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 24, 2008 3:58 PM
Points: 124, Visits: 3

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.

 

Post #181953
Posted Thursday, May 12, 2005 10:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 13, 2005 8:27 AM
Points: 2, Visits: 1

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.

 

 




Post #181964
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse