FOR XML - Root Element

Hi,
Does anybody know how to add a root element to the output of a stored
procedure that returns data, using FOR XML statement. I am using SQL server
2000.
ThanksYour provider should have the ability to set the root property. For example,
the ADO ICommandStream has a property that will add a root element. Ditto
for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks|||Michael,
Thanks for your response.
I can't find any property that will allow me to specify a root element. I
am using a XmlReader and sqlCommand to serialize an object based on the
results of a stored procedure which returns XML.
Due to our environment it is not possible to use a SqlXMLcommand object.
Thanks for any further help.
Alex
"Michael Rys [MSFT]" wrote:

> Your provider should have the ability to set the root property. For exampl
e,
> the ADO ICommandStream has a property that will add a root element. Ditto
> for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
> Best regards
> Michael
> "Redowl" <Redowl@.discussions.microsoft.com> wrote in message
> news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
>
>|||I think you have to use the SQLXML interfaces to add the root property.
In SQL Server 2000, you can also add a select N'<root>' before and select
N'</root>' after the stored proc invocation, IF you use the stream
interface.
Note that this is however not guaranteed to work well with the XML datatype
in 2005.
Also, in SQL Server 2005, you will be able to specify the root in your FOR
XML clause using a new ROOT directive...
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:0DACE436-377C-489B-A378-166ADEB6F0D5@.microsoft.com...
> Michael,
> Thanks for your response.
> I can't find any property that will allow me to specify a root element. I
> am using a XmlReader and sqlCommand to serialize an object based on the
> results of a stored procedure which returns XML.
> Due to our environment it is not possible to use a SqlXMLcommand object.
> Thanks for any further help.
>
> Alex
> "Michael Rys [MSFT]" wrote:
>|||In case you are still contemplating the answer to this in SQL Server 2000,
here is a great white paper from Dan Sullivan on how to achieve this:
http://www.sqlservicebroker.com/sam...rmedxmlauto.zip
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks