Using SQL Server's XML Support

QL 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.

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 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 textvariables. 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.

Listing 18.88

USE masterGOIF OBJECT_ID('sp_xml_concat','P') IS NOT NULL  DROP PROC sp_xml_concatGOCREATE PROC sp_xml_concat  @hdl int OUT,  @table sysname,  @column sysnameASEXEC('SET TEXTSIZE 4000DECLARE  @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 = 0WHILE (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+'ENDIF (@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 hdlcursorFETCH hdlcursor INTO @hdlDEALLOCATE hdlcursorGO

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

Listing 18.89

(Code abridged)USE NorthwindGOCREATE 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 intEXEC 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 @hdlSELECT DATALENGTH(doc) from xmldocGODROP TABLE xmldoc

(Results)

CustomerID--------------------------------------------------VINETLILAS-----------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.

sp_run_xml_proc

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.

Listing 18.90

USE masterGOIF OBJECT_ID('sp_run_xml_proc','P') IS NOT NULL  DROP PROC sp_run_xml_procGOCREATE PROC sp_run_xml_proc  @procname sysname  -- Proc to runASDECLARE @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 messagesIF (@procname='/?') GOTO Help-- Create a SQLServer objectEXEC @hr=sp_OACreate 'SQLDMO.SQLServer', @sqlobject OUTIF (@hr <> 0) BEGIN  EXEC sp_displayoaerrorinfo @sqlobject, @hr  RETURNEND-- Set SQLServer object to use a trusted connectionEXEC @hr = sp_OASetProperty @sqlobject, 'LoginSecure', 1IF (@hr <> 0) BEGIN  EXEC sp_displayoaerrorinfo @sqlobject, @hr  RETURNEND-- Turn off ODBC prefixes on messagesEXEC @hr = sp_OASetProperty @sqlobject, 'ODBCPrefix', 0IF (@hr <> 0) BEGIN  EXEC sp_displayoaerrorinfo @sqlobject, @hr  RETURNEND-- Open a new connection (assumes a trusted connection)EXEC @hr = sp_OAMethod @sqlobject, 'Connect', NULL, @@SERVERNAMEIF (@hr <> 0) BEGIN  EXEC sp_displayoaerrorinfo @sqlobject, @hr  RETURNEND-- Get a pointer to the SQLServer object's Databases collectionEXEC @hr = sp_OAGetProperty @sqlobject, 'Databases', @object OUTIF @hr <> 0 BEGIN  EXEC sp_displayoaerrorinfo @sqlobject, @hr  RETURNEND-- Get a pointer from the Databases collection for the-- current databaseSET @dbname=DB_NAME()EXEC @hr = sp_OAMethod @object, 'Item', @object OUT, @dbnameIF @hr <> 0 BEGIN  EXEC sp_displayoaerrorinfo @object, @hr  RETURNEND-- Call the Database object's ExecuteWithResultsAndMessages2-- method to run the procEXEC @hr = sp_OAMethod @object, 'ExecuteWithResultsAndMessages2',    @results OUT, @procname, @msgs OUTIF @hr <> 0 BEGIN  EXEC sp_displayoaerrorinfo @object, @hr  RETURNEND-- Display any messages returned by the procPRINT @msgsDECLARE @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 setEXEC @hr = sp_OAMethod @results, 'Rows',@rows OUTIF @hr <> 0 BEGIN  EXEC sp_displayoaerrorinfo @object, @hr  RETURNEND-- Call the QueryResult object's Columns method to get the number-- of columns in the result setEXEC @hr = sp_OAMethod @results, 'Columns',@cols OUTIF @hr <> 0 BEGIN  EXEC sp_displayoaerrorinfo @object, @hr  RETURNENDDECLARE @table TABLE (XMLText varchar(8000))-- Retrieve the result set column-by-column using the-- GetColumnString methodSET @y=1WHILE (@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+1ENDSELECT * FROM @tableEXEC sp_OADestroy @sqlobject    -- For cleanlinessRETURN 0Help:PRINT 'You must specify a procedure name to run'RETURN -1GO

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.

Listing 18.91

USE pubsGODROP PROC testxmlGOCREATE PROC testxml asPRINT 'a message here'SELECT * FROM pubs..authors FOR XML AUTOGOEXEC [TUK\PHRIP].pubs.dbo.sp_run_xml_proc 'testxml'

(Results abridged)

a message hereXMLText------------------------------------------------------------------<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.

Listing 18.92

SET NOCOUNT ONGOUSE pubsGODROP PROC testxmlGOCREATE PROC testxml asSELECT au_lname, au_fname FROM authors FOR XML AUTOGOCREATE TABLE #XMLText1(XMLText varchar(8000))GO-- Insert the XML document into a table-- using sp_run_xml_procINSERT #XMLText1EXEC sp_run_xml_proc 'testxml'-- Put the document in a variable-- and add a root elementDECLARE @doc varchar(8000)SET @doc=''SELECT @doc=@doc+XMLText FROM #XMLText1SET @doc='<root>'+@doc+'</root>'-- Put the document back in a table-- so that we can pass it into sp_xml_concatSELECT @doc AS XMLText INTO #XMLText2GODECLARE @hdl intEXEC sp_xml_concat @hdl OUT, '#XMLText2', 'XMLText'SELECT * FROM OPENXML(@hdl, '/root/authors') WITH    (au_lname nvarchar(40))EXEC sp_xml_removedocument @hdlGODROP 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----------------------------------------BennetBlotchet-HallsCarsonDeFrance...RingerRingerSmithStraightStringerWhiteYokomoto

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 FORXML 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.

• By Ken Henderson
• Jan 16, 2004