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, February 21, 2007 1:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 23, 2007 11:30 AM
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...
Post #346512
Posted Friday, February 23, 2007 12:13 AM
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

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.

Post #346906
Posted Friday, February 23, 2007 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 30, 2014 11:18 AM
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.

Post #347012
Posted Friday, February 23, 2007 12:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 6:37 AM
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).




Post #347151
Posted Friday, February 23, 2007 8:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 11:04 PM
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.
Post #347235
Posted Friday, November 21, 2008 5:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 12, 2009 4:42 PM
Points: 2, Visits: 11
Is "8 - Combined with XML_ATTRIBUTES or XML_ELEMENTS" the right parameter, i think this may be 3
Post #606905
Posted Thursday, March 5, 2009 10:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, March 7, 2009 2:17 AM
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?
Post #669429
Posted Thursday, March 5, 2009 10:49 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 573, 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
Post #669435
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse