لطفا منتظر باشید ...
Limitations
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.
