FOR XML Output

How do I get an output from the query below (1) one
record at a time, (2) insert the output one row at a time
into a XMLType column of a table? In addition, how do I
assign the output to a variable before step (2) so that I
can manipulate the output?
select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
FROM Customers
FOR XML PATH('Customer')
***Please disregard the new Path feature in Yukon
Thanks,
C TO
I presume you use SQL Server 2005 Express or Beta 2.
If you need to insert each customer info in XML format into a separate row
of another table you can do (I use FOR XML ..., TYPE for FOR XML to generate
XML type directly):
insert into your_table_with_xml_col
select
(select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
for xml path('Customer'), TYPE
)
FROM Customers
If you want all customers into an XML variable you can write:
declare @.x xml
set @.x=
(select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
FROM Customers
FOR XML PATH('Customer'), TYPE
)
Does it answer your question?
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and
confers no rights.
"C TO" <anonymous@.discussions.microsoft.com> wrote in message
news:867101c47843$77008040$a601280a@.phx.gbl...
> How do I get an output from the query below (1) one
> record at a time, (2) insert the output one row at a time
> into a XMLType column of a table? In addition, how do I
> assign the output to a variable before step (2) so that I
> can manipulate the output?
>
<skip/>
> Thanks,
> C TO
|||Dear Eugene,
Beautiful!!!!!
Thanks, thank, thanks!!!
TO

>--Original Message--
>I presume you use SQL Server 2005 Express or Beta 2.
>If you need to insert each customer info in XML format
into a separate row
>of another table you can do (I use FOR XML ..., TYPE for
FOR XML to generate
>XML type directly):
>insert into your_table_with_xml_col
>select
> (select CustomerID as "@.ID",
> (select OrderID as "data()"
> from Orders
> where Customers.CustomerID=Orders.CustomerID
> FOR XML PATH('')
> ) as "@.OrderIDs",
> CompanyName,
> ContactTitle as "ContactName/@.ContactTitle",
> ContactName as "ContactName/text()",
> PostalCode as "Address/@.ZIP",
> Address as "Address/Street",
> City as "Address/City"
> for xml path('Customer'), TYPE
> )
>FROM Customers
>If you want all customers into an XML variable you can
write:
>declare @.x xml
>set @.x=
>(select CustomerID as "@.ID",
> (select OrderID as "data()"
> from Orders
> where Customers.CustomerID=Orders.CustomerID
> FOR XML PATH('')
> ) as "@.OrderIDs",
> CompanyName,
> ContactTitle as "ContactName/@.ContactTitle",
> ContactName as "ContactName/text()",
> PostalCode as "Address/@.ZIP",
> Address as "Address/Street",
> City as "Address/City"
> FROM Customers
> FOR XML PATH('Customer'), TYPE
>)
>Does it answer your question?
>Regards,
>Eugene
>--
>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>"C TO" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:867101c47843$77008040$a601280a@.phx.gbl...
time[vbcol=seagreen]
I
><skip/>
>
>.
>