How to return one xml doument per row

When I do something like "select * from customer for XML auto" I get a single
string containing the xml for all customers in the customer table. I want one
xml string (i.e., document) per row so I can process one document at a time.
Is this possible?
Use FOR XML Explicit. This query should be written in a specific way.
something like this:
select 1 as Tag,
null as Parent,
customers.customerID as [Customer!1!CustomerID!id]
from customers
for xml explicit
Please refer to "EXPLICIT mode" in Books online.
Regards,
Deepak
[I Code, therefore I am]
"ACROWN" wrote:

> When I do something like "select * from customer for XML auto" I get a single
> string containing the xml for all customers in the customer table. I want one
> xml string (i.e., document) per row so I can process one document at a time.
> Is this possible?
|||FOR XML (both auto and explicit mode) is a rowset to XML aggregator. In SQL
Server 2000, this aggregator can only aggregate all the rows.
In SQL Server 2005, you will be able to write something along the lines of
Create table t (id int, name varchar(5))
go
insert into t values (1, 'a')
insert into t values (1, 'b')
insert into t values (2, 'c')
select (select * from t t1 where t1.id = t2.id for xml raw('t'), type)
from t t2
group by t2.id
HTH
Michael
"ACROWN" <ACROWN@.discussions.microsoft.com> wrote in message
news:6D633481-D083-4A18-9FB5-37AA9258FEEE@.microsoft.com...
> When I do something like "select * from customer for XML auto" I get a
> single
> string containing the xml for all customers in the customer table. I want
> one
> xml string (i.e., document) per row so I can process one document at a
> time.
> Is this possible?