In this article, I talked about how to create multilayer xml from SQL Server Database table.
Following is the XML which we want to create:
[ad#post]

<Orders>
  <Order OrderNumber="99503" OrderDate="2013-10-20">
    <Address Type="Shipping">
      <Name>Ellen Adams</Name>
      <Street>123 Maple Street</Street>
      <City>Mill Valley</City>
      <State>CA</State>
      <Zip>CA</Zip>
      <Country>USA</Country>
    </Address>
    <Address Type="Billing">
      <Name>Tai Yee</Name>
      <Street>8 Oak Avenue</Street>
      <City>Old Town</City>
      <State>PA</State>
      <Zip>PA</Zip>
      <Country>USA</Country>
    </Address>
    <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
    <Items>
      <Item PartNumber="872-AA">
        <ProductName>Lawnmower</ProductName>
        <Quantity>1</Quantity>
        <USPrice>148.95</USPrice>
        <Comment>Confirm this is electric</Comment>
      </Item>
      <Item PartNumber="926-AA">
        <ProductName>Baby Monitor</ProductName>
        <Quantity>2</Quantity>
        <USPrice>39.98</USPrice>
        <ShipDate>2013-05-21</ShipDate>
      </Item>
    </Items>
  </Order>
</Orders>

We have two table called Orders and Order_Products

SQL Script to generate above XML:

SELECT [Order] as '@OrderNumber' , OrderDate as '@OrderDate',
(
     SELECT 'Shipping' as '@Type' 
     , Shipping_Name as 'Name'
     ,Shipping_Street as 'Street'
     ,Shipping_City as 'City'
     ,Shipping_State as 'State'
     ,Shipping_State as 'Zip'
     ,Shipping_Country as 'Country'
     FOR XML PATH('Address'),Type
  ),
  (
     SELECT 'Billing' as '@Type' 
     , Billing_Name as 'Name'
     ,Billing_Street as 'Street'
     ,Billing_City as 'City'
     ,Billing_State as 'State'
     ,Billing_State as 'Zip'
     ,Billing_Country as 'Country'
     FOR XML PATH('Address'),Type
  ),DeliveryNotes,
  (
    SELECT PartNumber as '@PartNumber', ProductName, Quantity,convert(numeric(18,2), USPrice)as USPrice,Comment,ShipDate 			
		From [temp].[dbo].Order_Products P where 	P.[Order] =Ord.[Order]
		FOR XML PATH('Item'),ROOT('Items'),Type		
   )   
FROM [Orders] Ord
FOR XML PATH('Order'),ROOT('Orders')

Thanks