for auto question

Hi,
The following TSQL command ;
select * from tbldata for xml auto, elements
brings back the xml form of all the data records.
Hence, for a table with multiple records on the output is;
<tblrecord>
<text>Hi</text>
</tblrecord>
<tblrecord>
<text>Goodbye</text>
</tblrecord>
Is there any way (with the transact SQL command) to get it to put a top
level element around the data - so it can be immediately viewable in I.E.
i.e. To put the <records> tags into the start and end.
<records>
<tblrecord>
<text>Hi</text>
</tblrecord>
<tblrecord>
<text>Goodbye</text>
</tblrecord>
</records>
Many thanks in advance,
Mike.
No - the FOR XML clause is designed to return an XML fragment, not a
complete document. You'd have to use the SQLXML client-side technology to
add the root tag. Basically, the choices are to use an annotated schema
(with an "is-constant" root element), an XML template, the "xml root"
property of the ADO command object, the RootTag property of the ADO.NET
SqlXmlCommand object, or the root parameter in a SQLISAPI URL. Most of these
(apart from the ADO.NET approach) are available in the original release of
SQL Server 2000 and are discussed in the SQL Server Books Online. However,
you'd be best installing the latest SQLXML 3.0 release
(http://www.microsoft.com/downloads/d...33A9-CF10-4E22
-8004-477098A407AC&displaylang=en).
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Mike G" <mickyg@.mickyg.com> wrote in message
news:%235WFDHMcEHA.2844@.TK2MSFTNGP12.phx.gbl...
Hi,
The following TSQL command ;
select * from tbldata for xml auto, elements
brings back the xml form of all the data records.
Hence, for a table with multiple records on the output is;
<tblrecord>
<text>Hi</text>
</tblrecord>
<tblrecord>
<text>Goodbye</text>
</tblrecord>
Is there any way (with the transact SQL command) to get it to put a top
level element around the data - so it can be immediately viewable in I.E.
i.e. To put the <records> tags into the start and end.
<records>
<tblrecord>
<text>Hi</text>
</tblrecord>
<tblrecord>
<text>Goodbye</text>
</tblrecord>
</records>
Many thanks in advance,
Mike.
|||Note that FOR XML in SQL Server 2005 is adding a ROOT directive. But for SQL
Server 2000, Graeme's answer is correct.
Best regards
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:e8XX9EXcEHA.1000@.TK2MSFTNGP12.phx.gbl...
> No - the FOR XML clause is designed to return an XML fragment, not a
> complete document. You'd have to use the SQLXML client-side technology to
> add the root tag. Basically, the choices are to use an annotated schema
> (with an "is-constant" root element), an XML template, the "xml root"
> property of the ADO command object, the RootTag property of the ADO.NET
> SqlXmlCommand object, or the root parameter in a SQLISAPI URL. Most of
> these
> (apart from the ADO.NET approach) are available in the original release of
> SQL Server 2000 and are discussed in the SQL Server Books Online. However,
> you'd be best installing the latest SQLXML 3.0 release
> (http://www.microsoft.com/downloads/d...33A9-CF10-4E22
> -8004-477098A407AC&displaylang=en).
> Hope that helps,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "Mike G" <mickyg@.mickyg.com> wrote in message
> news:%235WFDHMcEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> The following TSQL command ;
> select * from tbldata for xml auto, elements
> brings back the xml form of all the data records.
> Hence, for a table with multiple records on the output is;
> <tblrecord>
> <text>Hi</text>
> </tblrecord>
> <tblrecord>
> <text>Goodbye</text>
> </tblrecord>
> Is there any way (with the transact SQL command) to get it to put a top
> level element around the data - so it can be immediately viewable in I.E.
> i.e. To put the <records> tags into the start and end.
> <records>
> <tblrecord>
> <text>Hi</text>
> </tblrecord>
> <tblrecord>
> <text>Goodbye</text>
> </tblrecord>
> </records>
>
> Many thanks in advance,
> Mike.
>
>