Improve company productivity with a Business Account.Sign Up

x
?
Solved

Problems with FOR XML RAW output in ASP

Posted on 2004-08-25
9
Medium Priority
?
668 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
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…
From store locators to asset tracking and route optimization, learn how leading companies are using Google Maps APIs throughout the customer journey to increase checkout conversions, boost user engagement, and optimize order fulfillment. Powered …
Watch the video which demonstrates the easy migration process from GroupWise to Outlook with the help of Kernel Novell GroupWise to Outlook software. User can export single or multiple mailboxes either by Online mode or via Command line mode with ea…

580 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