Accessing FOR XML Output in SQL Server
I was fascinated with the amount of features that SQL
Server 2005 brought wrt to XML datatype and related features. In exploration
into the same has been my latest work. And you get to see some of the
interesting works on the same. This is a very common requirement that I felt is
worth documenting too. In the present SQL Server 2000 world if we were to take
the output of a FOR XML into a variable, it is next to impossible.
More often we donot have a solution that is so direct
that we can use it. This is because the FOR XML Clause output was a binary
stream data that we required to capitalize. And for the same to work we had to
take the data to the middle tier and then return the same to the database for
manipulating or storing the same. Lets look at one work around that we have in
SQL Server 2000 to collect this XML output into a variable.
Declare
@var Varchar(8000)
SELECT
@var = CAST (a.[XML_F52E2B61-18A1-11d1-B105-00805F49916B] as VARCHAR(8000))
FROM
OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=localhost;UID=sa;PWD=password',
'Select * from pubs..authors Authors FOR XML
AUTO, ELEMENTS') AS a
Select
@var [XMLOutput]
Now a close look at the solution you must be aware
that this is a dirty little trick that we initiate another connection back to
SQL Server and stream the data back as a column data before we use the same.
This is NOT a clean solution. Works fine with simple queries. You can check for
yourself that this solution fails in conjunction with ORDER BY Clause
etc. I DONOT recommend this as a bigtime solution on live databases. Its a
worth trick to know but.
Welcome SQL Server 2005
As discussed before, SQL Server 2005 does bring XML
datatypes as a firstclass datatype. The capabilities of the same are not seen
explicitly. Having given the above problem in hand the solution in SQL Server
2005 would look like:
Declare
@XML XML
SET
@XML = ( Select * from pubs..authors Authors FOR XML
AUTO,ELEMENTS )
Select
@XML
The first glance at this solution you can easily
make out what it is supposed to do. Moreover, as in our SQL 2k solution we had
a limitation of 8000 characters. And the limitation on XML datatype is upto
2GB. And this is most unlikely that we are going to reach. Something
interesting is when you execute the FOR XML AUTO subquery alone using a text
view (Ctrl+T) in SQL Management Studio you will find the outout comes in a
special tab called "XML Outout" rather than the conventional "Messages"
tab. The enhancement I see in this new and latest version is the ability
to nest our FOR XML outputs. This is new and interesting in implementation. A
typical case would be:
Select
* ,(Select * from [Order Details] OrderDetails Where Orders.OrderID =
OrderDetails.OrderID
FOR
XML
AUTO,ELEMENTS ) from orders
FOR
XML
AUTO,ELEMENTS
This is a quick tour to what we can expect from the
new version. This is just the begining, we have loads and loads to explore. As
I start exploring into more XML features I would keep you posted. Feel free to
post your comments down to me, will be more than happy to hear
.
|