Solved

Problems with FOR XML RAW output in ASP

Posted on 2004-08-25
9
657 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

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…
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 tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

786 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