Posted on 2009-12-18
Medium Priority
Last Modified: 2012-05-08
I have a query in a classic web application, using an ODBC connection to sql server.  The query contains FOR XML PATH.  The query runs fine, but that data returned is blank.  If I run the query in the management studio, it works fine.  Any ideas?
Question by:cartch2008
LVL 15

Expert Comment

ID: 26085026
I think this could help you
OdbcConnection conn = new OdbcConnection("DSN=AdventureWorks");
OdbcDataAdapter daProducts = new OdbcDataAdapter("SELECT ProductID, Name FROM Production.Product FOR XML PATH('Product'), ROOT('Products')", conn);

DataSet dsResult = new DataSet();

string yourXML = dsResult.GetXml();

Open in new window


Author Comment

ID: 26085030
classic asp, not .net
LVL 28

Accepted Solution

sybe earned 2000 total points
ID: 26086124

or see attached code
Set oXML = Server.CreateObject("MSXML2.DomDocument.6.0")
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = yourconnectionobject
oCmd.Properties("xml root") = "root"
oCmd.Properties("Output Stream") = oXML
oCmd.CommandText = sSQL    'your FOR XML statement here
oCmd.Execute , , 1025      ' =  adExecuteStream + adCmdText

Open in new window


Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 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