Link to home
Start Free TrialLog in
Avatar of brothertom
brothertom

asked on

XML SELECT into an XML field

The following query gives me XML output.
select * from myTable FOR XML AUTO

I want to put that XML output into a table containing an XML field type (data field in this case)
CREATE TABLE [dbo].[_xmltest](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [data] [xml] NOT NULL
) ON [PRIMARY]

I have tried the following but FOR XML is not allowed in INSERT queries.

insert into _xmltext(data)
select  * from Security FOR XML AUTO

Any ideas?
BT
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Now if you are in SQL 2000, then the above will not work, so check
http://stackoverflow.com/questions/914009/saving-the-for-xml-auto-results-to-variable-in-sql 
Avatar of brothertom
brothertom

ASKER

Fantastic, thanks :)