SQL Server Performance

Capturing XML

Discussion in 'General Developer Questions' started by JackW, Jun 12, 2003.

  1. JackW New Member

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

    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
  3. satya Moderator

  4. trifunk New Member

    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
  5. vbkenya New Member

    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
  6. Chappy New Member

    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
  7. vbkenya New Member

    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
  8. Chappy New Member

    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.

  9. vbkenya New Member

    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.

Share This Page