MS SQL XML - FOR XML - Root Element

Author

FOR XML - Root Element

Redowl

2005-10-27, 9:25 am

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 Rys [MSFT]

2005-10-27, 9:25 am

Your 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@microso
ft.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

Redowl

2005-10-27, 9:25 am

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 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@microso
ft.com...
>
>
>

Michael Rys [MSFT]

2005-10-27, 9:26 am

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@microso
ft.com...[color=darkred]
> 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:
>

SriSamp

2005-10-27, 9:26 am

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@microso
ft.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

Sponsored Links