Return XML Document

  • Thread starter Thread starter Anthony Bollinger
  • Start date Start date
A

Anthony Bollinger

This is my first attempt at writing an XML web service.

I have a query successfully written for SQL Server that returns XML data
using FOR XML AUTO. It seems rather than processing the XML and building a
text string in XML format to return, there should be some way to directly
return the XML I get from SQL Server. What I seem to be getting is a
properly formatted XML string that is not identified as XML (not sure why),
but I want this to work pretty much like an RSS feed. I don't know how those
are generated, but whatever requests them knows what to do with them. What I
return should also be recognizable as an XML document. What is the best way
to do this?

Thanks!
Tony
 
I do not have an answer right this second, as it has been awhile, but the
answer is located in books online.

By default, you get an XML snippet from the FOR XML features in SQL Server.
There is either a flag that returns a proper root or an easy way to get it
to return. Then you will have valid XML.

If I get the chance, I will post, but take a look at the SQL books online
FOR XML documentation and you should find the answer.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss

or just read it:
http://gregorybeamer.spaces.live.com/

********************************************
| Think outside the box! |
********************************************
 
properly formatted XML string that is not identified as XML (not sure
why), but I want this to work pretty much like an RSS feed. I don't know
how those
Click to expand...


I think the reason is that the XML string does not have a root.

You can use:

SELECT * FROM TableName FOR XML RAW('NameOfRootElement')


HTH
 
properly formatted XML string that is not identified as XML (not sure
why), but I want this to work pretty much like an RSS feed. I don't know
how those
Click to expand...


I think the reason is that the XML string does not have a root.

You can use:

SELECT * FROM TableName FOR XML RAW('NameOfRootElement')


HTH
 
Thank you for your answer. I am not sure which SQL books online you mean.
Could you post a URL or two?

I do not think the issue I am having is proper XML, since I have that, but
rather it is not identified on the receiving end as XML, even though the
returned file is properly formatted. In fact, my first line is:

<?xml version="1.0" encoding="utf-8" ?>

And the remainder is a full XML document.

Unfortunately, I must need to set something additional in the web service
response, because consumers are not recognizing this as an XML file. Is
there a way to set the type of the response? I think I have seen this.

Many thanks! --Tony
 
Thanks for that. Also, I think you are referring to the ROOT
keyword/function which will generate a root node around your XML elements in
query results returned from SQL 2005. --Joe
 
Back
Top