Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using OpenXML


Using OpenXML

Author
Message
Arthur P. Jammz
Arthur P. Jammz
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 2
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 @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
Vasant Raj
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 137

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
Jonathan Scott-Sheldon
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 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
latinsky
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 31

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 RecordsObtain handleOpen XML
3000001545
2000001025
100000510
8000038
4000024
200001.52

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
stephensunil
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 83
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
Ronald Avendaño
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 11
Is "8 - Combined with XML_ATTRIBUTES or XML_ELEMENTS" the right parameter, i think this may be 3
zacmush
zacmush
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
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
Simon Sabin
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 107
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search