How can I retrieve the results of a FOR XML query using VB? (33191 Requests)

Here is some sample code that I use to test my FOR XML queries. To run it follow these steps:

1. Create a new EXE project in VB.
2. Add references to MSXML 3.0 and ADO 2.6
3. Copy the code below into your form's code. Be sure to change the connection string and put a real query in the sSQL variable.

Option Explicit

Const CONNSTRING = "<your connection string here>"

Private Sub Form_Load()

    Dim oCmd As Command
    Dim sSQL As String
    Dim oDom As IXMLDOMDocument2
   
    Set oDom = New DOMDocument30
   
    Set oCmd = New Command
    oCmd.ActiveConnection = CONNSTRING
    
   
    sSQL = "<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
           "select * from table for xml auto</sql:query></root>"
     
    oCmd.CommandText = sSQL
    oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
        
    oCmd.Properties("Output Stream") = oDom
    oCmd.Execute , , 1024
       
    oDom.Save "c:\temp\results.xml"
    
    Unload Me

End Sub

Other Resources:
HOWTO: Retrieve XML Data by Using a SQL XML Query in a Visual Basic Client

HOWTO: Retrieve XML Data with a Template File from a Visual Basic Client

Feedback

# How can I retrieve the results of a FOR XML query using VB? 7/24/2001 12:25 PM Brian Harrison

I used the exact code that you are asking for and the data does come out, because I have been able to pull it out of a stream if I send the output to a stream object, but everytime that I try to assign the data to a DOMDocument, I am unable to get any data from it. I can't assign the stream using the .load method nor can readtext the stream to a string variable and then use the .loadXML method. What could I be doing wrong?

# How can I retrieve the results of a FOR XML query using VB? 7/31/2001 10:08 AM Bryant

Sorry for the delayed reply. I didn't see your message until today. The code above should already load the data into a DOMDocument. The variable d (I really should make my names more descriptive) is a DOMDocument. So you shouldn't have to load the data into another variable. Send me an email if you still have questions. bryant@sqlxml.org.

# How can I retrieve the results of a FOR XML query using VB? 6/18/2002 9:12 AM ajoy

Thanks for your information. Can you explain me what is this {oCmd.Properties("Output Stream") } Is the Output Stream is key word or it is just any name. Thanks.

# How can I retrieve the results of a FOR XML query using VB? 6/18/2002 9:59 AM ajoy

Moreover i forgot to ask you how do you get this guid as "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" .Can we change this id.

# Error 6/18/2002 10:19 AM ajoy

I am getting an error as "Item cannot be found in the collection corresponding to the requested name or ordinal" at the statement "oCmd.Properties("Output Stream") = oDom". Can you please tell me what could be the problem

# Error 7/25/2002 8:31 PM Robert

Maybe you did not specify "Provider=SQLOLEDB" in your ConnectionString,and that does matter. Const CONNSTRING = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;User ID=SA;Password=;" Will this help?

# Returned XML using a Template 10/30/2002 11:18 AM Mark G

When using a template with for XML, nested to retrieve data, how do I know that the format will be constant? And if there is any change to the DB table structure the XSLT will no longer work. How is this eventuallity handled?

# re: How can I retrieve the results of a FOR XML query using VB? 3/12/2004 5:12 AM Mike Misconin

This reference is great but I would like to know how can I query a sql table to see if a record exists with http and report back to the client as a variable which can be used with vbscript.

I run a vbscript on the client wks to collect data. If the clients wks name does not exist in the workstation table then I create a update-gram which will post the data. If the workstation does exist, then I create a different update-gram which will post different data.

Can you help?

Mike Misconin
michael.misconin@nationalcity.com

# How can I retrieve data from the .xml file and how to give data to the same using VB6.? 6/13/2004 10:51 PM Raja

Sir, I don't have much knowledge on XML. Now i am studying. Could you please tell me the basic retrivel of data from XML using VB.
Please give the sample coding..
Thanks.

# Reg : How to get the xml structure and all datas from vb 1/10/2005 3:21 AM V.Sivakumar

Hi..

I read your article in the web "How can I retrieve the results of a FOR XML query using VB 7/24/2001 12:25 PM " it was very useful to me. I have a doubt reg. how to retrive the structure and data from xml through vb.

I need your help regarding this, i am expecting your valuable feedback to this mail id

unisoft_hr@rediffmail.com


Thanks
V.Sivakumar
Unisoft System Consultancy
INDIA.

# re: how to make a datareport in vb6 with parameters? 1/18/2006 5:14 PM joy

pls help....(beginner)

# re: How can I retrieve the results of a FOR XML query using VB? 5/31/2006 5:31 PM shirley

ineed my password to get my e-mail

# re: How can I retrieve the results of a FOR XML query using VB? 7/25/2006 3:29 AM tomy

select topic,author,max(timeofpost) as lastpost,count(contentid) as posts from forumdemo group by topic,author order by max(timeofpost)

# re: How can I retrieve the results of a FOR XML query using VB? 8/10/2006 2:44 AM Prakash T

How to create a xml file using query, the xml file contain a xsd file reference in vb 6?

# How can I retrieve the data from XML query using VBScript? 1/24/2007 8:47 PM Sivaprakasam S

I want to retrieve data from xml file by using the vbscript.

# lyxfn crpvams 3/13/2007 2:39 AM vobmywksu@mail.com

luxv nivkt fydokpj fuosxc wqzhiteb wmbphkrya srbempd

# vkio exbnf 3/13/2007 2:41 AM lnwxak@mail.com

ryqkmc dfwo rclyu dlqv cevwigj yjtwv zxawtmrg

# A name was started with an invalid character. 3/15/2007 12:11 AM A.Raj deep

Hi,

I am getting an error when i have query like this SELECT top 1 * FROM PRODDTA.F4801 a WHERE a.WASRST<> '8'.

The error message was

d:\public\XML_Generation3.vbs(16, 5) Microsoft XML Extensions to SQL Server: MSX
ML3: A name was started with an invalid character.

If anybody has experienced error message like this and got resolved ,pls reply.
Thank you,
Raj deep.A

# A name was started with an invalid character 6/8/2007 4:00 AM mark

I get the same error :(

# ptjx nafgv 9/22/2007 11:47 AM bqiwp@mail.com

mlihwzcta ahwfgbnvx ovscun wmxynp yfbqvpxwz wvcxqfubr vhyofzbul

# re: How can I retrieve the results of a FOR XML query using VB? 3/8/2008 1:59 PM Roskolnikov

I eat fish and speak suahilli

# re: How can I retrieve the results of a FOR XML query using VB? 3/15/2009 8:10 PM Michael A Klausmeyer

How do I retrieve the results of a dataview form and generate xml when I click the submit button?

# re: How can I retrieve the results of a FOR XML query using VB? 3/15/2009 8:11 PM Michael A Klausmeyer

How do I retrieve the results of a dataview form and generate xml when I click the submit button?

# re: How can I retrieve the results of a FOR XML query using VB? 3/15/2009 8:12 PM Michael

How do I retrieve the results of a dataview form and generate xml when I click the submit button?

# re: How can I retrieve the xml data using vb script? 4/6/2009 9:43 AM yathendra

how can extract the data from the xml file by creating the xmldom object and store it in
database