Creating Xml using SQL

Question

Hello Im trying to create an xml using sql. my code is as follows:

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 > '2011-08-08'
FOR XML PATH('Order'),ROOT('message')

my output looks liek this:

<message>
  <Order>
    <OrderNumber>ORD-01003-H8M0J</OrderNumber>
    <created>2011-08-16T13:52:48</created>
    <Sub_total>233.9400</Sub_total>
    <Order_total>228.4400</Order_total>
    <oder_tax>2.5000</oder_tax>
    <order_source>The Story</order_source>
    <order_discount>8.0000</order_discount>
    <Products>
      <Product>
        <zon_isbn10>0310591635</zon_isbn10>
        <Quantity>10.0000000000</Quantity>
        <Price>4.0000</Price>
      </Product>
      <Product>
        <zon_isbn10>0310649781</zon_isbn10>
        <Quantity>3.0000000000</Quantity>
        <Price>43.9800</Price>
        <Tax>1.4000</Tax>
        <Product_discount>10.0000</Product_discount>
      </Product>
      <Product>
        <zon_isbn10>031023414X</zon_isbn10>
        <Quantity>4.0000000000</Quantity>
        <Price>20.0000</Price>
        <Tax>1.1000</Tax>
        <Product_discount>8.0000</Product_discount>
      </Product>
    </Products>
    <ShippingAddress>
      <Shipto_Line1>1211 E BADILLO ST</Shipto_Line1>
      <Shipto_Line3>WEST COVINA CA 91790</Shipto_Line3>
      <postal>91790</postal>
    </ShippingAddress>
    <MailingAddress></MailingAddress>
  </Order>

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.

<message>
  <Order>
<orderheader>
    <OrderNumber>ORD-01003-H8M0J</OrderNumber>
    <created>2011-08-16T13:52:48</created>
    <Sub_total>233.9400</Sub_total>
    <Order_total>228.4400</Order_total>
    <oder_tax>2.5000</oder_tax>
    <order_source>The Story</order_source>
    <order_discount>8.0000</order_discount>
</orderheader>
    <Products>
      <Product>
        <zon_isbn10>0310591635</zon_isbn10>
        <Quantity>10.0000000000</Quantity>
        <Price>4.0000</Price>
      </Product>
      <Product>
        <zon_isbn10>0310649781</zon_isbn10>
        <Quantity>3.0000000000</Quantity>
        <Price>43.9800</Price>
        <Tax>1.4000</Tax>
        <Product_discount>10.0000</Product_discount>
      </Product>
      <Product>
        <zon_isbn10>031023414X</zon_isbn10>
        <Quantity>4.0000000000</Quantity>
        <Price>20.0000</Price>
        <Tax>1.1000</Tax>
        <Product_discount>8.0000</Product_discount>
      </Product>
    </Products>
    <ShippingAddress>
      <Shipto_Line1>1211 E BADILLO ST</Shipto_Line1>
      <Shipto_Line3>WEST COVINA CA 91790</Shipto_Line3>
      <postal>91790</postal>
    </ShippingAddress>
    <MailingAddress></MailingAddress>
  </Order>

Thanks in advance

renil · Accepted Answer · 2011-08-31 16:50:48Z

2

You can write the alias for the items you need in orderheader as 'orderheader/created' and so on to get the desired output.

SELECT
so.OrderNumber as 'orderheader/OrderNumber',
so.createdon As 'orderheader/created',
so.totallineitemamount As 'orderheader/Sub_total',
so.totalamount as 'orderheader/Order_total',
...

will produce the output as

<orderheader>
    <OrderNumber>something</OrderNumber>
    <created>something</created>
    <Sub_total>something</Sub_total>
    <Order_total>something</Order_total>
</orderheader>

edited Aug 31, 2011 at 16:50

answered Aug 31, 2011 at 16:10

renil

4096 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sharat Over a year ago

I need to use this xml as a source in ssis package so xml source divides all the tags into one output so to reduce the no of outputs i want to put these details under order header so that i need not seperately split them again and all come at once under order header

Collectives™ on Stack Overflow

Creating Xml using SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related