This chapter is from the book
This chapter is from the book
This chapter is from the book
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.
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 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.
Listing 18.88
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.)
Listing 18.89
(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
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 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.
Listing 18.91
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'
(Results abridged)
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.
Listing 18.92
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 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.