Joining External XML to SQL - Size Limitations?

[Jeff Donnici]

We've got a .NET (WinForms) application that talks to a read-only SQL Server
database (it's a reference tool that the user can't modify). On the user's
local machine, however, they can store some "favorites" ... these are stored
as very simple XML files with the IDs for the widgets they want to save as a
"favorite". As a side note, the application talks to the database only
through stored procedures (there's no in-line SQL in the C# code).

Later, the user wants to run a query that returns information related only
to their favorite widgets (via either a join or a subquery). To do this,
we're toying with code similar to what's pasted below (modified from the
samples, using data/structures from Northwind).

The problem is that it's VERY LIKELY that the string containing the XML will
exceed 8000 characters. In experimenting with this, it looks like a TEXT
datatype can't be used in the procedure (when replacing the varchar(8000)
with text).

So, my questions are:

1 - What is the size limitation on the XML string that can be passed to
sp_xml_preparedocument?

2 - Are there any drawbacks to passing the XML to our own stored proc as a
TEXT parameter (not a local variable) and sending that directly to
sp_xml_preparedocument?

3- Most importantly -- Given what we want to do (join an XML-based set of
IDs to SQL Server data), is there a better/cleaner/faster way to go than
this OPENXML route?

Thanks in advance for any assistance.

Regards,

JD



CODE BELOW
==================================

DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='
<ROOT>
<Customer CustomerID="BERGS" ContactName="Berglunds"></Customer>
<Customer CustomerID="FAMIA" ContactName="Familia"></Customer>
<Customer CustomerID="LAZYK" ContactName="The Lazy K"></Customer>
<Customer CustomerID="THECR" ContactName="Cracker Box"></Customer>
</ROOT>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT xtab.ContactName, rtab.Address
FROM Customers rtab,
OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20)) xtab
WHERE rtab.CustomerID = xtab.CustomerID

 

[Vinodk]

1 - What is the size limitation on the XML string that can be passed to

sp_xml_preparedocument?

You can also pass a text column to the sp_preparedocument. But this as you
have discussed has to be passed to the SP from your middleware and then
used.

2 - Are there any drawbacks to passing the XML to our own stored proc as a
TEXT parameter (not a local variable) and sending that directly to
sp_xml_preparedocument?

I donot see any pitfalls as such in using the same. But I strongly suggest
that you release the memory used ... This code has not been shown in your
example.

3- Most importantly -- Given what we want to do (join an XML-based set of
IDs to SQL Server data), is there a better/cleaner/faster way to go than
this OPENXML route?

The support for XML Data manipulation is very limited in the current version
of SQL Server. The OpenXML is the way out for sure. Its prety fast and
decent in its attempt to extract data from XML files.

These are some of my comments from personal experience. I am open for
comments from others also.

 

[Jeff Donnici]

Hi, Bill. Thanks for your response...

The concerns I have at this point are:

1 - The XML that I need to join with the SQL Server data isn't going to fit
easily into a regular Dataset. There will be multiple hierarchies and
element structures -- most of those won't be needed for the join process,
but they're in there nonetheless.

2 - The XML file/Dataset that would result from this approach is on the
client, but the SQL Server data to join to is on the server. If I'm going to
go this route, I'd need to pull the data over from SQL Server (into another
Dataset) and then try to join the two Datasets.. yes? Seems like that would
be a lot of overhead.

I'd prefer some mechanism for sending the XML to the server (even just the
portion of the XML that is used for filtering the SQL data), do the
join/filter and then get a result set back. But maybe I'm missing something?

Thanks again.

J

 

[Jeff Donnici]

Thanks vinodk... The sample code I saw for OPENXML didn't have an example of
releasing the memory used by the doc-preparation process, so I'll dig
further into that. I definitely don't want to waste anything.

Any other advice/tips/ideas are most welcome.

Regards,

J

 

[Vinodk]

Hi Jeff,

Well I was mentioning the usage of sp_xml_removedocument. You can look at my
article: http://www.extremeexperts.com//sql/articles/OpenXML.aspx for
understanding usage of OPENXML.

 

[Jeff Donnici]

Thanks, Vinodk!

Hi Jeff,

Well I was mentioning the usage of sp_xml_removedocument. You can look at my
article: http://www.extremeexperts.com//sql/articles/OpenXML.aspx for
understanding usage of OPENXML.

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com


example proc proc