Limitations

for RuBoard

Even with the new features in the forthcoming Web Release 1, 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 of working around them.

sp_xml_concat

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 this is not the case. Although sp_xml_preparedocument's xmltext parameter accepts text as well as varchar parameters, the problem is that 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 to pass it into sp_xml_preparedocument? Unfortunately , there's no easy way to do this. Because 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.

Sp_xml_concat 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 either be an actual table or view name , or it can be a derived table complete with a table alias. 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.

Here's the full source code to 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 

What this procedure does is dynamically generate the necessary DECLARE and SELECT statements to break up a large text column into nvarchar(4,000) 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(). Here's an example:

 (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 

(Results)

 CustomerID -------------------------------------------------- VINET LILAS ----------- 36061 

You'll find the full test query on the CD accompanying this book. Although I've abridged the XML document in the test query, the one on the CD is more than 36,000 bytes, as the DATALENGTH() query at the end of the test code reports .

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. As you can see, it's able to extract the nodes for which we're searching, even though one of them is near the end of a fairly large document.

sp_run_xml_proc

Another limitation of SQL Server's XML support is that XML results are not returned as traditional rowsets. This 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, as I mentioned earlier.

To work around this, I wrote a stored procedure called 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 with results that you'd like to store in a table or trap in a variable. It does its magic by opening its own connection into the server (it assumes Windows authentication) 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. Here's the source code to 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 -- 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, 'Column',@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 to translate the document is not a particularly pretty one, it is unfortunately the only way to do this without resorting to client-side processingat least for now. Here's some test code that 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 [BUC\FRYIN].pubs.dbo.sp_run_xml_proc 'testxml' 

(Results abridged)

 a message here XMLText --------------------------------------------------------------------------- <pubs..authors au_id="172-32-1176" au_lname="White" au_fname="Johnson" <pubs..authors au_id="672-71-3249" au_lname="Yokomoto" au_fname="Akiko" 

Although I've clipped the resulting document down 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(). Here's an example that 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:

(Results abridged)

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

for RuBoard