Compose SQL Data From SELECT Into XML Field 1

[Skittle]

Never needed to do much with XML before on SQL Server but it's becoming a requirement.

I want to take the contents of a standard T-SQL query and push it into an XML field. I exepcted the following to be valid:-

INSERT INTO MyTable(MyXMLField)
SELECT * FROM MyFile
FOR XML AUTO, ELEMENTS

...but I get an error message stating INSERT INTO cannot be used with INSERT INTO. Whats the best way to do this and more importantly, whats the point of the FOR XML clauses if you cannot use them as a basis for input into an XML field?
I think I'm missing something obvious here.


Dazed and confused

 

[bborissov]

declare @Test TABLE (Fld1 int, fld2 datetime)
INSERT INTO @Test VALUES(1, GETDATE())
INSERT INTO @Test VALUES(2, GETDATE()-1)
INSERT INTO @Test VALUES(3, GETDATE()+1)

declare @Test1 TABLE (Fld1 XML)
INSERT INTO @test1
SELECT DISTINCT (SELECT * FROM @Test FOR XML AUTO, ELEMENTS) AS Test FROM @Test

SELECT * from @Test1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Skittle]

Thanks.

I was going crazy over that one.

Dazed and confused