Return XML Document

[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

 

[Cowboy \(Gregory A. Beamer\)]

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.

 

[Gaurav Vaish \(a.k.a. MasterGaurav\)]

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

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

 

[Gaurav Vaish \(a.k.a. MasterGaurav\)]

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

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

 

[Anthony Bollinger]

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

 

[Juan T. Llibre]

re:
!> I am not sure which SQL books online you mean.
!> Could you post a URL or two?

SQL Server Books Online...

General info :
http://msdn.microsoft.com/en-us/library/ms166018.aspx

Download :
http://www.microsoft.com/downloads/...5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

 

[Anthony Bollinger]

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

 

[Cowboy \(Gregory A. Beamer\)]

Yes, that is it. I assume you found the SQL books online file?