Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Problems with FOR XML RAW output in ASP

Posted on 2004-08-25
9
Medium Priority
?
663 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
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

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!

Question has a verified solution.

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

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…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

782 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