title: Creating Xml using SQL
body: <p>Hello Im trying to create an xml using sql. my code is as follows:</p>
  
  <pre><code>SELECT
      --so.SalesOrderID,
      --so.name as 'OrderName',
      so.zon_cartid as "zcom_order_id",
      c.zon_emailaddress1 As "email",
      so.OrderNumber,
      so.createdon As "created",
      so.totallineitemamount As "Sub_total",
      so.totalamount as "Order_total",
      so.totaltax As "oder_tax",
      so.[description] As "Order_note",
      so.zon_ordersubsource as "order_source",
      so.submitstatus As "order_status",
      so.discountamount As "order_discount",
      --c.FirstName,
      --c.LastName,
      --a.name as 'AccountName',
     (SELECT --sod.SalesOrderDetailID,
          p.zon_isbn10,
          sod.Zon_producttype,
          --p.Zon_title,
          --sod.Zon_status,
          SOD.Quantity As "Quantity", 
         SOD.PricePerUnit As "Price",
         SOD.Tax As "Tax",
         SOD.ManualDiscountAmount As "Product_discount"
         --sod.extendedamount
      FROM SalesOrderDetail sod
      inner join Product p on sod.productid = p.productid
      WHERE so.SalesOrderId = sod.SalesOrderId
      ORDER by sod.SalesOrderDetailID
      FOR XML PATH('Product'),Type) As "Products", 
      (Select so3.ShippingMethodCode,
                    so3.freightamount, 
      (Select    c.firstname,
                       c.lastname, 
                 so2.Shipto_Line1,
                 so2.Shipto_Line2,
                 so2.Shipto_Line3,
                 so2.Shipto_City As "city",
                 so2.Shipto_StateorProvince As "state",
                 so2.Shipto_PostalCode As "postal",
                 so2.shipto_country As "country",
                 so2.Shipto_telephone As "phone",
                 so2.Shipto_fax As "fax"
                From SalesOrder so2
                WHERE so2.SalesOrderId = so.SalesOrderId
               FOR XML PATH(''),Type) As "ShippingAddress" 
                From SalesOrder so3
                WHERE so3.SalesOrderId = so.SalesOrderId
                FOR XML PATH(''),Type),
     (Select     c.firstname,
                 c.lastname, 
                 so4.billto_Line1,
                 so4.billto_Line2,
                 so4.billto_Line3,
                 so4.billto_City As "city",
                 so4.billto_StateorProvince As "state",
                 so4.billto_PostalCode As "postal",
                 so4.billto_country As "country",
                 so4.billto_telephone As "phone",
                 so4.billto_fax As "fax"
                From SalesOrder so4
                WHERE so4.SalesOrderId = so.SalesOrderId
               FOR XML PATH(''),Type) As "MailingAddress"
  FROM SalesOrder so
  left outer join Contact c on c.contactid = so.customerid 
  left outer join Account a on a.accountid = so.customerid
  WHERE so.CreatedOn &gt; '2011-08-08'
  FOR XML PATH('Order'),ROOT('message')
  </code></pre>
  
  <p>my output looks liek this:</p>
  
  <pre><code>&lt;message&gt;
    &lt;Order&gt;
      &lt;OrderNumber&gt;ORD-01003-H8M0J&lt;/OrderNumber&gt;
      &lt;created&gt;2011-08-16T13:52:48&lt;/created&gt;
      &lt;Sub_total&gt;233.9400&lt;/Sub_total&gt;
      &lt;Order_total&gt;228.4400&lt;/Order_total&gt;
      &lt;oder_tax&gt;2.5000&lt;/oder_tax&gt;
      &lt;order_source&gt;The Story&lt;/order_source&gt;
      &lt;order_discount&gt;8.0000&lt;/order_discount&gt;
      &lt;Products&gt;
        &lt;Product&gt;
          &lt;zon_isbn10&gt;0310591635&lt;/zon_isbn10&gt;
          &lt;Quantity&gt;10.0000000000&lt;/Quantity&gt;
          &lt;Price&gt;4.0000&lt;/Price&gt;
        &lt;/Product&gt;
        &lt;Product&gt;
          &lt;zon_isbn10&gt;0310649781&lt;/zon_isbn10&gt;
          &lt;Quantity&gt;3.0000000000&lt;/Quantity&gt;
          &lt;Price&gt;43.9800&lt;/Price&gt;
          &lt;Tax&gt;1.4000&lt;/Tax&gt;
          &lt;Product_discount&gt;10.0000&lt;/Product_discount&gt;
        &lt;/Product&gt;
        &lt;Product&gt;
          &lt;zon_isbn10&gt;031023414X&lt;/zon_isbn10&gt;
          &lt;Quantity&gt;4.0000000000&lt;/Quantity&gt;
          &lt;Price&gt;20.0000&lt;/Price&gt;
          &lt;Tax&gt;1.1000&lt;/Tax&gt;
          &lt;Product_discount&gt;8.0000&lt;/Product_discount&gt;
        &lt;/Product&gt;
      &lt;/Products&gt;
      &lt;ShippingAddress&gt;
        &lt;Shipto_Line1&gt;1211 E BADILLO ST&lt;/Shipto_Line1&gt;
        &lt;Shipto_Line3&gt;WEST COVINA CA 91790&lt;/Shipto_Line3&gt;
        &lt;postal&gt;91790&lt;/postal&gt;
      &lt;/ShippingAddress&gt;
      &lt;MailingAddress&gt;&lt;/MailingAddress&gt;
    &lt;/Order&gt;
  </code></pre>
  
  <p>but my required output is it should show the basic details in the order as order header as shown below i tried all different ways but iam missing something if someone can help that would be great.</p>
  
  <pre><code>&lt;message&gt;
    &lt;Order&gt;
  &lt;orderheader&gt;
      &lt;OrderNumber&gt;ORD-01003-H8M0J&lt;/OrderNumber&gt;
      &lt;created&gt;2011-08-16T13:52:48&lt;/created&gt;
      &lt;Sub_total&gt;233.9400&lt;/Sub_total&gt;
      &lt;Order_total&gt;228.4400&lt;/Order_total&gt;
      &lt;oder_tax&gt;2.5000&lt;/oder_tax&gt;
      &lt;order_source&gt;The Story&lt;/order_source&gt;
      &lt;order_discount&gt;8.0000&lt;/order_discount&gt;
  &lt;/orderheader&gt;
      &lt;Products&gt;
        &lt;Product&gt;
          &lt;zon_isbn10&gt;0310591635&lt;/zon_isbn10&gt;
          &lt;Quantity&gt;10.0000000000&lt;/Quantity&gt;
          &lt;Price&gt;4.0000&lt;/Price&gt;
        &lt;/Product&gt;
        &lt;Product&gt;
          &lt;zon_isbn10&gt;0310649781&lt;/zon_isbn10&gt;
          &lt;Quantity&gt;3.0000000000&lt;/Quantity&gt;
          &lt;Price&gt;43.9800&lt;/Price&gt;
          &lt;Tax&gt;1.4000&lt;/Tax&gt;
          &lt;Product_discount&gt;10.0000&lt;/Product_discount&gt;
        &lt;/Product&gt;
        &lt;Product&gt;
          &lt;zon_isbn10&gt;031023414X&lt;/zon_isbn10&gt;
          &lt;Quantity&gt;4.0000000000&lt;/Quantity&gt;
          &lt;Price&gt;20.0000&lt;/Price&gt;
          &lt;Tax&gt;1.1000&lt;/Tax&gt;
          &lt;Product_discount&gt;8.0000&lt;/Product_discount&gt;
        &lt;/Product&gt;
      &lt;/Products&gt;
      &lt;ShippingAddress&gt;
        &lt;Shipto_Line1&gt;1211 E BADILLO ST&lt;/Shipto_Line1&gt;
        &lt;Shipto_Line3&gt;WEST COVINA CA 91790&lt;/Shipto_Line3&gt;
        &lt;postal&gt;91790&lt;/postal&gt;
      &lt;/ShippingAddress&gt;
      &lt;MailingAddress&gt;&lt;/MailingAddress&gt;
    &lt;/Order&gt;
  
  Thanks in advance
  </code></pre>