For xml into insert

Hi,
For reasons beyond the scope of this question, i need to insert into a
varchar field of a table, the output of a select * from table2 for xml auto,
elements .
I haven't figured out a way to do this. any ideas?
thanksHello Ishmael,

> For reasons beyond the scope of this question, i need to insert into a
> varchar field of a table, the output of a select * from table2 for xml
> auto, elements . I haven't figured out a way to do this. any ideas?
If you are using SQL Server 2000, you'll need to do this via a round trip
due to the way that the XML Aggregator works -- have some client fetch the
XML and then insert it into the target table column. If you are using SQL
Server 2005, it's pretty easy:
insert into schema.tabke values (xmlCol)
select ... for xml auto,elements
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

For xml into insert

Hi,
For reasons beyond the scope of this question, i need to insert into a
varchar field of a table, the output of a select * from table2 for xml auto,
elements .
I haven't figured out a way to do this. any ideas?
thanks
Hello Ishmael,

> For reasons beyond the scope of this question, i need to insert into a
> varchar field of a table, the output of a select * from table2 for xml
> auto, elements . I haven't figured out a way to do this. any ideas?
If you are using SQL Server 2000, you'll need to do this via a round trip
due to the way that the XML Aggregator works -- have some client fetch the
XML and then insert it into the target table column. If you are using SQL
Server 2005, it's pretty easy:
insert into schema.tabke values (xmlCol)
select ... for xml auto,elements
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

FOR XML and subselects

I thought SQL 2005 allowed this but perhaps i misread something.
SELECT column1, column2, ...
, (SELECT column1, column2, ... FROM table2 WHERE table2.column1 = table1.column1 FOR XML AUTO, TYPE) data
FROM Table1
That would return all the columns of table1 and a column of type xml for
the data in table2.
I have this:
select productid ,
(
select rtrim(xrefnum) xrefnum, qty from productxref where productid = '13737'
FOR XML path ('xref'),type
) xref
from product p
where productid = '13737'
which always fails with this:
Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'XML'.
Is this possible?
danHello Dan,
Yes, it's possible.
Try this:
use northwind
go
select ProductID,
(select rtrim(productName) name
,supplierID
,categoryID
from dbo.products pi
where productName = 'Chai'
for xml path('pd'),type) xref
from dbo.products p
where productName = 'Chai'
go
If it works, I can't explain your problem unless your DBCOMPAT isn't 90.
If it doesn't work, post back what error message you get along with the output
of SELECT @.@.VERSION.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/