SQL Server's XML support has some fundamental limitations that make it difficult to use in certain situations. In this section, we'll explore a couple of these and look at ways to work around them.
Given that sp_xml_preparedocument accepts document text of virtually any length (up to 2GB), you'd think that SQL Server's XML facilities would be able to handle long documents just fine—but that's not the case. Although sp_xml_preparedocument's xmltext parameter accepts text as well as varchar parameters, Transact-SQL doesn't support local text variables. About the closest you can get to a local text variable in Transact-SQL is to set up a procedure with a text parameter. However, this parameter cannot be assigned to nor can it be the recipient of the text data returned by the READTEXT command. About the only thing you can do with it is insert it into a table.
The problem is painfully obvious when you try to store a large XML document in a table and process it with sp_xml_preparedocument. Once the document is loaded into the table, how do you extract it in order to pass it into sp_xml_preparedocument? Unfortunately, there's no easy way to do so. Since we can't declare local text variables, about the only thing we can do is break the document into multiple 8,000-byte varchar variables and use parameter concatenation when we call sp_xml_preparedocument. This is a ridiculously difficult task, so I've written a stored procedure to do it for you. It's called sp_xml_concat, and you can use it to process large XML documents stored in a table in a text, varchar, or char column.
The sp_xml_concat procedure takes three parameters: the names of the table and column in which the document resides and an output parameter that returns the document handle as generated by sp_xml_preparedocument. You can take the handle that's returned by sp_xml_concat and use it with OPENXML and sp_xml_unpreparedocument.
The table parameter can be either an actual table or view name or a Transact-SQL query wrapped in parentheses that will function as a derived table. The ability to specify a derived table allows you to filter the table that the procedure sees. So, if you want to process a specific row in the table or otherwise restrict the procedure's view of the table, you can do so using a derived table expression.
Listing 18.88 shows the full source code for sp_xml_concat.
USE master GO IF OBJECT_ID('sp_xml_concat','P') IS NOT NULL DROP PROC sp_xml_concat GO CREATE PROC sp_xml_concat @hdl int OUT, @table sysname, @column sysname AS EXEC(' SET TEXTSIZE 4000 DECLARE @cnt int, @c nvarchar(4000) DECLARE @declare varchar(8000), @assign varchar(8000), @concat varchar(8000) SELECT @c = CONVERT(nvarchar(4000),'+@column+') FROM '+@table+' SELECT @declare = ''DECLARE'', @concat = '''''''''''''''', @assign = '''', @cnt = 0 WHILE (LEN(@c) > 0) BEGIN SELECT @declare = @declare + '' @c''+CAST(@cnt as nvarchar(15)) +''nvarchar(4000),'', @assign = @assign + ''SELECT @c''+CONVERT(nvarchar(15),@cnt) +''= SUBSTRING(' + @column+',''+ CONVERT(nvarchar(15), 1+@cnt*4000)+ '', 4000) FROM '+@table+' '', @concat = @concat + ''+@c''+CONVERT(nvarchar(15),@cnt) SET @cnt = @cnt+1 SELECT @c = CONVERT(nvarchar(4000),SUBSTRING('+@column+', 1+@cnt*4000,4000)) FROM '+@table+' END IF (@cnt = 0) SET @declare = '''' ELSE SET @declare = SUBSTRING(@declare,1,LEN(@declare)-1) SET @concat = @concat + ''+'''''''''''''' EXEC(@declare+'' ''+@assign+'' ''+ ''EXEC( ''''DECLARE @hdl_doc int EXEC sp_xml_preparedocument @hdl_doc OUT, ''+@concat+'' DECLARE hdlcursor CURSOR GLOBAL FOR SELECT @hdl_doc AS DocHandle'''')'' ) ') OPEN hdlcursor FETCH hdlcursor INTO @hdl DEALLOCATE hdlcursor GO
This procedure dynamically generates the necessary DECLARE and SELECT statements to break up a large text column into nvarchar(4000) pieces (e.g., DECLARE @c1 nvarchar(4000) SELECT @c1= …). As it does this, it also generates a concatenation expression that includes all of these variables (e.g., @c1+@c2+@c3, … ). Since the EXEC() function supports concatenation of strings up to 2GB in size, we pass this concatenation expression into it dynamically and allow EXEC() to perform the concatenation on-the-fly. This basically reconstructs the document that we extracted from the table. This concatenated string is then passed into sp_xml_preparedocument for processing. The end result is a document handle that you can use with OPENXML. Listing 18.89 shows an example.
(You'll find the full test query in the CH18 subfolder on the CD accompanying this book.)
(Code abridged) USE Northwind GO CREATE TABLE xmldoc (id int identity, doc text) INSERT xmldoc VALUES('<Customers> <Customer CustomerID="VINET" ContactName="Paul Henriot"> <Order CustomerID="VINET" EmployeeID="5" OrderDate= "1996-07-04T00:00:00"> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/> // More code lines here... </Order> </Customer> <Customer CustomerID="LILAS" ContactName="Carlos GOnzlez"> <Order CustomerID="LILAS" EmployeeID="3" OrderDate= "1996-08-16T00:00:00"> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/> </Order> </Customer> </Customers>') DECLARE @hdl int EXEC sp_xml_concat @hdl OUT, '(SELECT doc FROM xmldoc WHERE id=1) a', 'doc' SELECT * FROM OPENXML(@hdl, '/Customers/Customer') WITH (CustomerID nvarchar(50)) EXEC sp_xml_removedocument @hdl SELECT DATALENGTH(doc) from xmldoc GO DROP TABLE xmldoc
CustomerID -------------------------------------------------- VINET LILAS ----------- 36061
Although I've abridged the XML document in the test query, the one on the CD is over 36,000 bytes in size, as you can see from the result of the DATALENGTH() query at the end of the test code.
We pass a derived table expression into sp_xml_concat along with the column name we want to extract, and the procedure does the rest. It's able to extract the nodes we're searching for, even though one of them is near the end of a fairly large document.
Another limitation of SQL Server's XML support exists because XML results are not returned as traditional rowsets. Returning XML results as streams has many advantages, but one of the disadvantages is that you can't call a stored procedure that returns an XML result using a four-part name or OPENQUERY() and get a useful result. The result set you'll get will be an unrecognizable binary result set because SQL Server's linked server architecture doesn't support XML streams.
You'll run into similar limitations if you try to insert the result of a FOR XML query into a table or attempt to trap it in a variable—SQL Server simply won't let you do either of these. Why? Because the XML documents returned by SQL Server are not traditional rowsets.
To work around this, I've written a stored procedure named sp_run_ xml_proc. You can use it to call linked server stored procedures (it needs to reside on the linked server) that return XML documents as well as local XML procedures whose results you'd like to store in a table or trap in a variable. This procedure does its magic by opening its own connection into the server (it assumes Windows Authentication is being used) and running your procedure. Once your procedure completes, sp_run_xml_proc processes the XML stream it returns using SQL-DMO calls, then translates it into a traditional rowset and returns that rowset. This result set can be inserted into a table or processed further just like any other result set. Listing 18.90 presents the source code for sp_run_xml_proc.
USE master GO IF OBJECT_ID('sp_run_xml_proc','P') IS NOT NULL DROP PROC sp_run_xml_proc GO CREATE PROC sp_run_xml_proc @procname sysname -- Proc to run AS DECLARE @dbname sysname, @sqlobject int, -- SQL Server object @object int, -- Work variable for accessing COM objects @hr int, -- Contains HRESULT returned by COM @results int, -- QueryResults object @msgs varchar(8000) -- Query messages IF (@procname='/?') GOTO Help -- Create a SQLServer object EXEC @hr=sp_OACreate 'SQLDMO.SQLServer', @sqlobject OUT IF (@hr <> 0) BEGIN EXEC sp_displayoaerrorinfo @sqlobject, @hr RETURN END -- Set SQLServer object to use a trusted connection EXEC @hr = sp_OASetProperty @sqlobject, 'LoginSecure', 1 IF (@hr <> 0) BEGIN EXEC sp_displayoaerrorinfo @sqlobject, @hr RETURN END -- Turn off ODBC prefixes on messages EXEC @hr = sp_OASetProperty @sqlobject, 'ODBCPrefix', 0 IF (@hr <> 0) BEGIN EXEC sp_displayoaerrorinfo @sqlobject, @hr RETURN END -- Open a new connection (assumes a trusted connection) EXEC @hr = sp_OAMethod @sqlobject, 'Connect', NULL, @@SERVERNAME IF (@hr <> 0) BEGIN EXEC sp_displayoaerrorinfo @sqlobject, @hr RETURN END -- Get a pointer to the SQLServer object's Databases collection EXEC @hr = sp_OAGetProperty @sqlobject, 'Databases', @object OUT IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @sqlobject, @hr RETURN END -- Get a pointer from the Databases collection for the -- current database SET @dbname=DB_NAME() EXEC @hr = sp_OAMethod @object, 'Item', @object OUT, @dbname IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @object, @hr RETURN END -- Call the Database object's ExecuteWithResultsAndMessages2 -- method to run the proc EXEC @hr = sp_OAMethod @object, 'ExecuteWithResultsAndMessages2', @results OUT, @procname, @msgs OUT IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @object, @hr RETURN END -- Display any messages returned by the proc PRINT @msgs DECLARE @rows int, @cols int, @x int, @y int, @col varchar(8000), @row varchar(8000) -- Call the QueryResult object's Rows method to get the number of -- rows in the result set EXEC @hr = sp_OAMethod @results, 'Rows',@rows OUT IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @object, @hr RETURN END -- Call the QueryResult object's Columns method to get the number -- of columns in the result set EXEC @hr = sp_OAMethod @results, 'Columns',@cols OUT IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @object, @hr RETURN END DECLARE @table TABLE (XMLText varchar(8000)) -- Retrieve the result set column-by-column using the -- GetColumnString method SET @y=1 WHILE (@y<=@rows) BEGIN SET @x=1 SET @row='' WHILE (@x<=@cols) BEGIN EXEC @hr = sp_OAMethod @results, 'GetColumnString', @col OUT, @y, @x IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @object, @hr RETURN END SET @row=@row+@col+' ' SET @x=@x+1 END INSERT @table VALUES (@row) SET @y=@y+1 END SELECT * FROM @table EXEC sp_OADestroy @sqlobject -- For cleanliness RETURN 0 Help: PRINT 'You must specify a procedure name to run' RETURN -1 GO
Although the prospect of having to open a separate connection into the server in order to translate the document is not particularly exciting, it is unfortunately the only way to do this without resorting to client-side processing—at least for now. The test code in Listing 18.91 shows how to use sp_run_xml_proc.
USE pubs GO DROP PROC testxml GO CREATE PROC testxml as PRINT 'a message here' SELECT * FROM pubs..authors FOR XML AUTO GO EXEC [TUK\PHRIP].pubs.dbo.sp_run_xml_proc 'testxml'
a message here XMLText ------------------------------------------------------------------ <pubs..authors au_id="172-32-1176" au_lname="White" au_fname="John <pubs..authors au_id="672-71-3249" au_lname="Yokomoto" au_fname="A
Although I've clipped the resulting document considerably, if you run this code from Query Analyzer (replace the linked server reference in the example with your own), you'll see that the entire document is returned as a result set. You can then insert this result set into a table using INSERT…EXEC for further processing. For example, you could use this technique to assign the document that's returned to a variable (up to the first 8,000 bytes) or to change it in some way using Transact-SQL. And once the document is modified to your satisfaction, you could call sp_xml_concat (listed earlier in the chapter) to return a document handle for it so that you can query it with OPENXML. Listing 18.92 does just that.
SET NOCOUNT ON GO USE pubs GO DROP PROC testxml GO CREATE PROC testxml as SELECT au_lname, au_fname FROM authors FOR XML AUTO GO CREATE TABLE #XMLText1 (XMLText varchar(8000)) GO -- Insert the XML document into a table -- using sp_run_xml_proc INSERT #XMLText1 EXEC sp_run_xml_proc 'testxml' -- Put the document in a variable -- and add a root element DECLARE @doc varchar(8000) SET @doc='' SELECT @doc=@doc+XMLText FROM #XMLText1 SET @doc='<root>'+@doc+'</root>' -- Put the document back in a table -- so that we can pass it into sp_xml_concat SELECT @doc AS XMLText INTO #XMLText2 GO DECLARE @hdl int EXEC sp_xml_concat @hdl OUT, '#XMLText2', 'XMLText' SELECT * FROM OPENXML(@hdl, '/root/authors') WITH (au_lname nvarchar(40)) EXEC sp_xml_removedocument @hdl GO DROP TABLE #XMLText1, #XMLText2
After the document is returned by sp_run_xml_proc and stored in a table, we load it into a variable, wrap it in a root element and store it in a second table so that we may pass it into sp_xml_concat. Once sp_xml_concat returns, we pass the document handle it returns into OPENXML and extract part of the document:
au_lname ---------------------------------------- Bennet Blotchet-Halls Carson DeFrance ... Ringer Ringer Smith Straight Stringer White Yokomoto
So, using sp_xml_concat and sp_run_xml_proc in conjunction with SQL Server's built-in XML tools, we're able to run the entire XML processing gamut. We start with an XML fragment returned by FOR XML AUTO, then we store this in a table, retrieve it from the table, wrap it in a root node, and pass it into OPENXML in order to extract a small portion of the original document as a rowset. You should find that these two procedures enhance SQL Server's own XML abilities significantly.