Using OpenXML

Question

Using OpenXML

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Jim-153675 Grasshopper Points: 21 More actions · Answer 1

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.

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 2

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.

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Gunjan Moghe Grasshopper Points: 21 More actions · Answer 3

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

ThomasLN Mr or Mrs. 500 Points: 521 More actions · Answer 4

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.

bijulsoni SSC Journeyman Points: 98 More actions · Answer 5

bijulsoni

SSC Journeyman

Points: 98

October 6, 2006 at 12:29 am

#664510

good article vasant You done a nice job

Arthur P. Jammz Newbie Points: 6 More actions · Answer 6

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

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 @idoc

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

Vasant Raj SSCommitted Points: 1835 More actions · Answer 7

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.

Jonathan Scott-Sheldon Grasshopper Points: 24 More actions · Answer 8

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.

latinsky SSC Enthusiast Points: 193 More actions · Answer 9

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

stephensunil SSC Veteran Points: 262 More actions · Answer 10

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

Ronald Avendaño SSC Journeyman Points: 88 More actions · Answer 11

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

zacmush Grasshopper Points: 13 More actions · Answer 12

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?

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 13

Its all about how the columns in the with are matched when you don't specify an xpath literal i.e

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

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons