There are indeed many solutions to creating XML from SQL Server.  I have had good success for several years using stored procedures that return multiple recordsets with embedded instructions for converting them to XML.  The only non-SQL code required is a generic utility class that executes the stored procedures, then interprets and converts the results.  For example:

create procedure shpClientOrders( @clientID int ) as
select 'DATASET clients.client' AS _shapeInfo
select * from clients where id=@clientID

select 'DATASET orders.order RELATE TO clients' as _shapeInfo
select * from orders where clientID=@clientID

select 'DATASET orderItems.item RELATE orderID to orders.id GROUPRECORDS' as _shapeInfo
select * from orderItems where clientID=@clientID


results in:

<client id='1234' name='Client Name'...>
    <order id='92342' orderDate='2004-10-12'>
        <items>
            <item id='00093' description='Skippy 12oz.' quantity='3'/>
            <item id='00093' description='Welch's 8oz.' quantity='2'/>
        </items>
    </order>
</client>