One of the coolest things to come with SQL 2005 is the new 'for XML PATH' syntax. There are a lot of things that can be done with it, I'm going to demonstrate something moderately complex here. The task is to generate an XML file that has nested nodes. In other words, we want the following structure:
<Receivable documentNumber="CREDT000000000010" documentDate="08/26/2008">
<Type>Credit Adjustment</Type>
<Amount>300.00000</Amount>
<Items>
<Item type="Statement" itemNumber="346.001">
<AppliedAmount>55.00000</AppliedAmount>
<UnappliedAmount>0</UnappliedAmount>
</Item>
<Item type="Statement" itemNumber="346.002">
<AppliedAmount>11.00000</AppliedAmount>
<UnappliedAmount>0</UnappliedAmount>
</Item>
</Items>
</Receivable>
Note the nested 'Items' node. We achieve this using a nested SQL Select statement inside the main query, like this:
select
'Credit Adjustment' as Type,
ortrxamt as Amount,
(
select
case when apply.aptodcnm is null then 'Case' else 'Statement' end as <a href="mailto:'@type'">'@type'</a>,
rtrim(isnull(apply.aptodcnm,'')) as <a href="mailto:'@itemNumber'">'@itemNumber'</a>,
isnull(apply.apfrmaplyamt,0) as 'AppliedAmount',
0 as 'UnappliedAmount'
from RM20201 apply
where apply.Apfrdcnm = rm.docnumbr and rm.rmdtypal = apply.apfrdcty
for xml path('Item') , type
) as 'Items'
from RM20101 rm
where rm.dex_row_id = 306
for xml path('Receivable')
The 'type' clause in the nested select tells the code to output XML