Capturing XML

Is there any way to perform a SELECT...FOR XML and capture the XML result into a local variable or column?

I was actually trying that today but I couldn't get it right, if you look in BOL there are alot of limitations to using FOR XML, in the end I had to write a seperate stored procedure to retrieve the data using FOR XML onto the client, I couldn't get it into a variable within a stored procedure.

If you decide to bring it down to the client and you're using ADO, you might be surprised to find out you can't bring it down into an ADO recordset either, you have to use an ADO Stream object to get at the xml.. I wish microsoft had made this all a little easier!

I'd be interetested to hear from anyone who can get the results of a FOR XML statement into a tsql variable as well.

Cheers
Shaun

World Domination Through Superior Software

Thanks, Satya, I suppose there's some consilation in knowing that it can't be done in TSQL <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Cheers<br />Shaun<br /><br />World Domination Through Superior Software

The FOR XML Clause violates the ANSI standard SQL and like many extensions to this standard we will often meet with numerous limitations when applying them within T-SQL.

With XML Web services receiving a lot of publicity out there, this would be a good item for the SQL Server Wish list.

NHO

I had a similar problem attempting to access the results of a SHOWPLAN_TEXT output via ADO.
After spending days on it, trying any idea I could find, I came to the conclusion it simply wasnt possible. very frustrating

Actually Chappy, you can read the output of the SHOWPLAN_TEXT via ADO. The trick is to remember that the output is contained in the second recordset that comes back from SQL server to the client. To get the second recordset, you have to use the NextRecordset method of the recordset object.

To cut the long story short, try this ADO code:


Private Sub Command1_Click()
Dim cn As Connection
Dim rs As Recordset
Dim rs1 As Recordset
Dim strSHOWPLAN As String

Set cn = New Connection
cn.Open "Provider=SQLOLEDB.1;Server=myServer;database=Northwind;Integrated Security=SSPI;"

cn.Execute "SET SHOWPLAN_TEXT ON"

Set rs = cn.Execute("Select * from Orders")

Do While rs.EOF = False
'//Do whatever stuff with your data
rs.MoveNext
Loop

'// Get the second recordset:-Output from the SHOWPLAN_TEXT
Set rs1 = rs.NextRecordset

Do While rs1.EOF = False
'// store the output in a string
'// Or do whatever else you want to do with the data.
strSHOWPLAN = strSHOWPLAN & vbcrlf & rs1.Fields(0).Value
rs1.MoveNext
Loop

'//Display the string
MsgBox strSHOWPLAN

End Sub


I havent looked at how to do it from ADO.NET but I am sure it is also very possible.

NHO

Superb! Many thanks VBKeyna!!!!
Finally I have this working after porting to my language. I can now proceed to allow my application to self tune itself.

I had previously been misinformed via websearches and forums to read the SHOWPLAN from the Errors property, or other people said its a standard result set (singular), but you just need to retrieve it using certain connection properties.

PS: Apologies for my email claiming your post had disappeared, it had indeed been here all along.
I had been simply checking the wrong thread.

As a DBA/Developer, you will use the Errors collection to read output from DBCC commands and SQL server error messages because these output is not formatted as a resultset( cursor/recordset etc.) before being returned to the client.

On the other hand, any SQL server output that you can see in the Query Analyzer as a resultset will be accessed on the client through a recorset object (if you are using ADO) or a dataset object (for ADO.NET)

The idea, from my experience, can be summarily stated as: "Output that is seen in the Query Analyzer 'Grid' tab goes to a recordset object and that which is seen in the 'Messages' tab will generally go to the connection object."

PS: Apology accepted..these things happen sometimes.

Nathan H.O.