Implementing XML Parser in VB6 to extract dtat from sql server and output to xml file - why doesn't it work?

Posted on 2006-03-27
Last Modified: 2013-11-23
Please, I don't know if I should post this in VB, sql server or xml quetsions so I apoogise if this is in the wrong area.

I have built a small vb6 application that adds data to a sql server table.

With every addition to the database I want to also output the uploaded data in xml format for another system to analyse.

After the data has been uploaded to the table I hope to draw out the most recent addition to the table using the code below:

     'Instance of XML Parser
    Dim oCmd, sSQL, oDom

    ''' If MSXML 4.0 is not installed this will not work!
    Set oDom = CreateObject("Msxml2.DOMDocument.4.0")

    Set oCmd = CreateObject("ADODB.Command")
    oCmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=SCLAnodising;UID=GCroxford;Password=xxxxx"

    sSQL = "<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>" & "select * from Build_Validation 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:\Documents and Settings\GCroxford\Desktop\testdts.xml"

The application dies giving a syntax error message relating to the line:

 sSQL = "<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>" & "select * from Build_Validation for xml auto</sql:query></ROOT>"

Can anybody help?
Question by:ReneLMadsen
    1 Comment
    LVL 26

    Accepted Solution

    use this

    sSQL = "<ROOT xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>" & "select * from Build_Validation for xml auto</sql:query></ROOT>"

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now