inserting xml data

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,thomson (saintthomson@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||xmlbulkload no good?

Inserting Nulls with DTS

I am using a DTS package to copy data from a flat file to a table. I need for fields that are empty to be changed to nulls in the output. I wrote a VB Script to do this, but now it takes about 10 times longer that when I used the Copy transformation. Is there a faster way to do this?

I haven't tried the Trim transformation yet. (I'm waiting for my load to finish.) Will that place nulls in the output if a field is empty?Originally posted by jsneeringer
I am using a DTS package to copy data from a flat file to a table. I need for fields that are empty to be changed to nulls in the output. I wrote a VB Script to do this, but now it takes about 10 times longer that when I used the Copy transformation. Is there a faster way to do this?

I haven't tried the Trim transformation yet. (I'm waiting for my load to finish.) Will that place nulls in the output if a field is empty?

Hi,
1-in your SQL Server Table ( Destination DB) set a default value for the field(s), so when you attemp to insert Null value in that Field, the specified default vale will be insert. ( the way of inserting is not important . It can be DTS!!)

2- you can also write a Instead Of Trigger on your Table For Insert, so process the field value, if it's Null , write empty.

Hope that help you