Solved

Problems with FOR XML RAW output in ASP

Posted on 2004-08-25
9
656 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
A short film showing how OnPage and Connectwise integration works.

932 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

14 Experts available now in Live!

Get 1:1 Help Now