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
<Bill … >
use the FOR XML AUTO clause like
select * from Clients Client inner join Bills Bill on Client.Id = Bill.ClientId for xml auto.
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).