Suppose you have two tables, Clients and Bills.

A bill belongs to a client and only one client.

One client can have 0 or more bills.

No bill can exist without a client.

To retrieve an xml document with the structure

<Client …>

<Bill …/>

<Bill … >

</Client>

<Client …>

<Bill …/>

</Client>

use the FOR XML AUTO clause like

select * from Clients Client inner join Bills Bill on Client.Id = Bill.ClientId for xml auto.

 

Use

select Client.id as ClientId, Client.Name as ClientName, Bill.Id as BillId, Bill.TotalAmount as BillAmount from Clients Client inner join Bills Bill on Client.Id = Bill.ClientId for xml raw(‘Client’), elements xsinil

to get a list of bills together with client information (no hierarchy info in the results).

Advertisements