Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problems with FOR XML RAW output in ASP

Posted on 2004-08-25
9
Medium Priority
?
662 Views
Last Modified: 2012-05-05
Here is my problem,

I have an SP that performs some query's and then generates a result using FOR XML RAW.  
and example of what the results will be are:
<row ERROR="-2" Description="Mailbox name already used" Mode="Setup" />
My select statement in the sp is:

Select "-2" as ERROR, "Mailbox name already used" as Description, "Setup" As Mode
FOR XML RAW

As you see I need to use the RAW method rather than AUTO as I'm not selecting from a table.

in my ASP Code I'm using the command and stream object to read the results.

With objCmd
      .ActiveConnection = objConn
      .CommandText = sSQL
      objStream.Open
      .Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
      .Properties("Output Stream").Value = objStream
      .Properties("Output Encoding") = "utf-8"
      .Execute , , 1024
End With
objStream.Position = 0
objStream.Charset = "utf-8"      
executeEFORMethod = objStream.ReadText(-1)

The error I"m getting is:

Microsoft XML Extensions to SQL Server error '80040e14'
MSXML2: Invalid at the top level of the document.


on the Execute line.  

Any help with this would be most appreciated.
      
0
Comment
Question by:raizon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11893771
What I do instead is have the SP return a recordset and then persist that to XML instead:

objRS.Save "D:\My Documents\InProgress\Northwind.xml",1


FtB
0
 
LVL 5

Author Comment

by:raizon
ID: 11893811
I'm adding the XML returned from the SP as a node into a parent XML that will then be returned to the client and parsed.

Sorry should have posted that info in the question.  

So saving this to a file, then reading it from the file would decrease the performance to much.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11897726
I see.

The error seems to indicate a mismatch in the structure? One thought might be to take the out put and examine it before appending it to the existing file. The you can try to append outside of the SP and see what sort of problems occur.

FtB

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11899185
Change:
Select "-2" as ERROR, "Mailbox name already used" as Description, "Setup" As Mode
FOR XML RAW

To:
Select '-2' as ERROR, 'Mailbox name already used' as Description, 'Setup' As Mode
FOR XML RAW
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 11899281
Actually while using double quotes instead of single quote is a problem it is not the root of the problem. Make the following changes, if you need an explanation as to why and can provide, but you will find a better explanation in BOL (tested)

With objCmd
     .ActiveConnection = objConn
     .CommandText = sSQL
     .CommandType = 1          ' Not entirely necessary, but should be faster
     objStream.Open
     '.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"           ' Comment this line out.  It is the cause of the error
     .Properties("Output Stream").Value = objStream
     .Properties("Output Encoding") = "utf-8"
     .Execute , , 1024
End With
objStream.Position = 0
objStream.Charset = "utf-8"    
executeEFORMethod = objStream.ReadText(-1)
objStream.Close                    ' Not entirely necessary, but always a good idea
Set objStream = Nothing         ' Not entirely necessary, but always a good idea
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11899284
And this:
"if you need an explanation as to why and can provide"
Should have read:
"if you need an explanation as to why I can provide"
0
 
LVL 5

Author Comment

by:raizon
ID: 11902464
AC!!

That worked Perfectly.  Thanks :)

The double quotes was a typo on my part when I posted the question.  

Why does setting the dialect to the guid cause the error?  Every resource I found on doing this says to do that?

Just curious :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11903218
>>Why does setting the dialect to the guid cause the error? Every resource I found on doing this says to do that?<<
Look carefully and you will see they are actually inputing the command input as a stream (and for this they set the CommandStream to adoStreamQuery).  In your case there is no input, just output.
0
 
LVL 5

Author Comment

by:raizon
ID: 11904067
That makes sence.

Thanks again :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 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