guys,

I have bunch of tables and what i need to do is get xml data from all the tables and insert into a main table. I tried using for xml raw, it throws me an error say its not allowed in insert statement.

Any suggestions? Thanks

Have you tried to use SSIS (SQL Server Integration Services), it have a bunch of predefined tasks that you can use, furthermore you may want some data transformation..

If you prefer not to use SSIS this script may give you an idea:
(it creates a sample table with one column of xml datatype in populate it from other table that stores xml data, the key is using insert into tablename SELECT ...)

USE AdventureWorks
GO

CREATE TABLE dbo.SampleXMLTable
    (
    XMLData xml NULL
    ) ON [PRIMARY]

GO


INSERT INTO dbo.SampleXMLTable
SELECT AdditionalContactInfo FROM Person.Contact
WHERE AdditionalContactInfo IS NOT NULL

GO

SELECT * FROM dbo.SampleXMLTable


Best regards,


Marko Jagodic,
Pexim Solutions, Serbia

not a likely problem but a possible one is that you may have special characters in your xml data that can not be inserted into a sql table. I ran across this once. Sql does not accept all ascii values.