inserting xml data

[thomson]

Hi,
Is there any way to insert the output of xml_auto into a table
for eg:

select * from categories for xml auto

i need the output of the abouve query to be inserted into another table
the destination table has one column,

[Erland Sommarskog]

thomson (sa**********@yahoo.com) writes:

Is there any way to insert the output of xml_auto into a table

for eg:

select * from categories for xml auto

i need the output of the abouve query to be inserted into another table
the destination table has one column,

I think the only way you can do this in SQL 2000 is to use OPENQUERY:

INSERT tbl (col)
SELECT * FROM OPENQUERY (LOOPBACK,
'SELECT * FROM categories FROM XML AUTO')

Here LOOPBACK is a linked server back to your own, and here is a real
funny thing: you must set it up to use MSDASQL, that is the OLE DB over
ODBC provider! If you use SQLOLEDB which is the recommended provider,
you will get binary data back.

But when I did a quick test, the result was not entirely acceptable, since
the XML string was split up over six rows.

In the next version of SQL Server, SQL 2005, currency in beta, there
are significant enhancements in XML support, including a specific xml
datatype, and you can do this easily.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[andy]

xmlbulkload no good?