Example 4: Marshal with Combine and ORDER BY

The XML Structure tab offers different ways to sort XML output. The default approach is to use an ORDER BY clause when sending the SELECT statement for marshalling data to the database. To order LineItem and PurchaseOrder elements in the XML output from the sample project using ORDER BY:

Click in the Order By column for OrderID and choose ascending order to make sure that the XML output is arranged by OrderID number from lowest to highest.
Click in the Order By column for UnitPrice and choose ascending order to make sure that in the XML output, line items are arranged by unit price from lowest to highest.

<xml>
<PurchaseOrder OrderID="1">
<Customer>
<Company>Berman</Company>
<Address street="85 Walk Ave" city="Los Angeles" state="CA" zip="78554" country="USA" />
<Contact name="John Mena" title="Mr." phone="(882) 452 5568" mobile="(882) 123 5688" />
</Customer>
<SalesRepresentative Name="JEAN Holtzman" Phone="(558) 899-5899" />
<LineItem Quantity="10" UnitPrice="225.80" Discount="0" Total="2258.00">
  <Product>
       <Name>tall vertical file</Name>
       <UnitsInStock>35</UnitsInStock>
       <InventoryDate>2002-09-20T00:00:00.0</InventoryDate>
       <Category Name="Filing Cabinet" Description="Lateral and vertical filing cabinets" />
  </Product>
</LineItem>
<LineItem Quantity="8" UnitPrice="290.00" Discount="0" Total="2320.00">
  <Product>
       <Name>tall bookshelf</Name>
       <UnitsInStock>19</UnitsInStock>
       <InventoryDate>2002-09-20T00:00:00.0</InventoryDate>
       <Category Name="Shelving" Description="Bookshelves and storage shelves" />
  </Product>
</LineItem>
<LineItem Quantity="50" UnitPrice="365.50" Discount="0" Total="18275.00">
<Product>
       <Name>side chair</Name>
       <UnitsInStock>11</UnitsInStock>
       <InventoryDate>2002-09-20T00:00:00.0</InventoryDate>
       <Category Name="Chair" Description="Office chairs" />
</Product>
</LineItem>
<LineItem Quantity="5" UnitPrice="999.99" Discount="0" Total="4999.95">
<Product>
      <Name>conference table</Name>
      <UnitsInStock>32</UnitsInStock>
      <InventoryDate>2002-09-20T00:00:00.0</InventoryDate>
      <Category Name="Desk" Description="Workstations and executive desks" />
</Product>
</LineItem>
<OrderDate>2002-12-06T00:00:00.0</OrderDate>
<RequiredDate>2002-12-20T00:00:00.0</RequiredDate>
<ShipDate>2002-12-15T00:00:00.0</ShipDate>
<Taxrate>0.0000</Taxrate>
<SubTotal>27852.95</SubTotal>
<TotalAmount>27852.95</TotalAmount>
<Terms>0</Terms>
</PurchaseOrder>
<PurchaseOrder OrderID="2">
<Customer>
<Company>Fischer Enterprises</Company>
<Address street="18 Firebird Ave." city="Seattle" state="WA" zip="45210" country="USA" />
<Contact name="Jonathan Miller" title="Mr." phone="(739) 452 6621" mobile="(883) 334 4545"/>
</Customer>
<SalesRepresentative Name="BERNARDO Bultain" Phone="(558) 778-8988"/>
<LineItem Quantity="1" UnitPrice="89.99" Discount="0" Total="89.99">
  <Product>
       <Name>left pedestal desk</Name>
       <UnitsInStock>29</UnitsInStock>
       <InventoryDate>2002-09-20T00:00:00.0</InventoryDate>
       <Category Name="Desk" Description="Workstations and executive desks" />
  </Product>
</LineItem>
<LineItem Quantity="1" UnitPrice="159.99" Discount="0" Total="159.99">
<Product>
      <Name>left pedestal desk</Name>
      <UnitsInStock>29</UnitsInStock>
      <InventoryDate>2002-09-20T00:00:00.0</InventoryDate>
      <Category Name="Desk" Description="Workstations and executive desks" />
</Product>
</LineItem>
<OrderDate>2002-11-05T00:00:00.0</OrderDate>
<RequiredDate>2002-11-30T00:00:00.0</RequiredDate>
<ShipDate>2002-11-18T00:00:00.0</ShipDate>
<Taxrate>0.0000</Taxrate>
<SubTotal>249.98</SubTotal>
<TotalAmount>249.98</TotalAmount>
<Terms>0</Terms>
</PurchaseOrder>
</xml>

Examples

1.	Marshal Without Combine	No settings have been made in the XML Structure tab
2.	Marshal with Combine	XML output combines all PurchaseOrder nodes that have a matching value for the OrderID node.
3.	Marshal with Combine and Group	XML output groups all matching elements (LineItem) together
4.	Marshal with Combine and Order By	This page.
5.	Marshal with Optimized Combine	XML output generated using the Optimized Combine feature in the XML Structure tab.

Example 4: Marshal with Combine and ORDER BY

Examples

See Also