XSLT: Multiple Levels on a Flat File

[schenz]

I have a Flat XML file which contains order information. The problem I am having is that I need to separate it for my database into <Customers>, <Orders>, and <OrderDetails>. A customer may have more than one order, but should only be listed once. An Order may have more than one OrderDetail, but should only be listed once. Here is an example of my source XML:

<?xml version="1.0" standalone="yes"?>
<Orders_Dataset>
<Orders>
<Order>15480599</Order>
<Ship_To_First_Name>Matthew</Ship_To_First_Name>
<Ship_To_Last_Name>Wade</Ship_To_Last_Name>
<Ship_To_Address_1>3970 Laura Court</Ship_To_Address_1>
<Ship_To_Address_2/>
<Ship_To_City>Tucker</Ship_To_City>
<Ship_To_State>GA</Ship_To_State>
<Ship_To_Zip>30084</Ship_To_Zip>
<Ship_Method>GRND</Ship_Method>
<Product_ID>21BU013M41</Product_ID>
<Sold_For>N/A</Sold_For>
<Our_Cost>43.5</Our_Cost>
<Qty>1</Qty>
<Description>Betula By Birkenstock Rock Mens Black Suede Clogs - 41 (US W 10-M 8)</Description>
<Carrier_Requested>BEST</Carrier_Requested>
<Shipping_Cost>0</Shipping_Cost>
</Orders>
<Orders>
<Order>15480599</Order>
<Ship_To_First_Name>Matthew</Ship_To_First_Name>
<Ship_To_Last_Name>Wade</Ship_To_Last_Name>
<Ship_To_Address_1>3970 Laura Court</Ship_To_Address_1>
<Ship_To_Address_2/>
<Ship_To_City>Tucker</Ship_To_City>
<Ship_To_State>GA</Ship_To_State>
<Ship_To_Zip>30084</Ship_To_Zip>
<Ship_Method>GRND</Ship_Method>
<Product_ID>21BU011M43</Product_ID>
<Sold_For>N/A</Sold_For>
<Our_Cost>43.5</Our_Cost>
<Qty>1</Qty>
<Description>Betula Birkenstock Mens Rock Mocha Suede Clog - 43 (US M 10)</Description>
<Carrier_Requested>BEST</Carrier_Requested>
<Shipping_Cost>0</Shipping_Cost>
</Orders>
<Orders>
<Order>15481583</Order>
<Ship_To_First_Name>Ami</Ship_To_First_Name>
<Ship_To_Last_Name>Rivera</Ship_To_Last_Name>
<Ship_To_Address_1>127 Kathryn Road</Ship_To_Address_1>
<Ship_To_Address_2/>
<Ship_To_City>Griffin</Ship_To_City>
<Ship_To_State>GA</Ship_To_State>
<Ship_To_Zip>30223</Ship_To_Zip>
<Ship_Method>GRND</Ship_Method>
<Product_ID>21BU01839</Product_ID>
<Sold_For>N/A</Sold_For>
<Our_Cost>42.5</Our_Cost>
<Qty>1</Qty>
<Description>Betula Birkenstock Rock Cocoa Nubuk Clog - 39 (US W 8-M 6)</Description>
<Carrier_Requested>BEST</Carrier_Requested>
<Shipping_Cost>0</Shipping_Cost>
</Orders>
<Orders>
<Order>15485795</Order>
<Ship_To_First_Name>Regina</Ship_To_First_Name>
<Ship_To_Last_Name>Lockhart</Ship_To_Last_Name>
<Ship_To_Address_1>5325 Columbus blvd.</Ship_To_Address_1>
<Ship_To_Address_2/>
<Ship_To_City>Sebring</Ship_To_City>
<Ship_To_State>FL</Ship_To_State>
<Ship_To_Zip>33872</Ship_To_Zip>
<Ship_Method>2D</Ship_Method>
<Product_ID>1101015A</Product_ID>
<Sold_For>N/A</Sold_For>
<Our_Cost>50</Our_Cost>
<Qty>1</Qty>
<Description>Head Intelligence i.Tour Tennis Racquet - 4 1/8</Description>
<Carrier_Requested>BEST</Carrier_Requested>
<Shipping_Cost>10.21</Shipping_Cost>
</Orders>
<Orders>
<Order>15490839</Order>
<Ship_To_First_Name>Mike</Ship_To_First_Name>
<Ship_To_Last_Name>Dooley</Ship_To_Last_Name>
<Ship_To_Address_1>6 Lakeside Club SW</Ship_To_Address_1>
<Ship_To_Address_2/>
<Ship_To_City>Lakewood</Ship_To_City>
<Ship_To_State>WA</Ship_To_State>
<Ship_To_Zip>98498</Ship_To_Zip>
<Ship_Method>GRND</Ship_Method>
<Product_ID>213200739</Product_ID>
<Sold_For>N/A</Sold_For>
<Our_Cost>32.95</Our_Cost>
<Qty>2</Qty>
<Description>Prince QuikTrac SE Mens Tennis Shoes - 9</Description>
<Carrier_Requested>BEST</Carrier_Requested>
<Shipping_Cost>0</Shipping_Cost>
</Orders>
<Orders>
<Order>15488024</Order>
<Ship_To_First_Name>Carletta</Ship_To_First_Name>
<Ship_To_Last_Name>Faletti</Ship_To_Last_Name>
<Ship_To_Address_1>5311 NE 84th Loop</Ship_To_Address_1>
<Ship_To_Address_2/>
<Ship_To_City>Vancouver</Ship_To_City>
<Ship_To_State>WA</Ship_To_State>
<Ship_To_Zip>98662</Ship_To_Zip>
<Ship_Method>GRND</Ship_Method>
<Product_ID>21BU02238</Product_ID>
<Sold_For>N/A</Sold_For>
<Our_Cost>32</Our_Cost>
<Qty>1</Qty>
<Description>Betula Birkenstock Grace Blue Suede Sandals - 38</Description>
<Carrier_Requested>BEST</Carrier_Requested>
<Shipping_Cost>0</Shipping_Cost>
</Orders>
<Orders>
<Order>15490582</Order>
<Ship_To_First_Name>alice</Ship_To_First_Name>
<Ship_To_Last_Name>bothwell</Ship_To_Last_Name>
<Ship_To_Address_1>1230 smyth road</Ship_To_Address_1>
<Ship_To_Address_2/>
<Ship_To_City>hooksett</Ship_To_City>
<Ship_To_State>NH</Ship_To_State>
<Ship_To_Zip>03106-2020</Ship_To_Zip>
<Ship_Method>GRND</Ship_Method>
<Product_ID>21BU01638</Product_ID>
<Sold_For>N/A</Sold_For>
<Our_Cost>42.5</Our_Cost>
<Qty>1</Qty>
<Description>Betula Birkenstock Rock Jet Black Nubuk Clog - 38 (US W 7-M 5)</Description>
<Carrier_Requested>BEST</Carrier_Requested>
<Shipping_Cost>0</Shipping_Cost>
</Orders>
</Orders_Dataset>

And Ideas on how I get an output such as:

<Orders_Dataset>
<Customers>
<!-- Info pertaining to the Customer goes here-->
<Orders>
<!-- Info pertaining to the Order goes here -->
<OrderDetails>
<!-- Info pertaining to each OrderDetail goes here -->
</OrderDetails>
</Orders>
</Customers>
</Orders_Dataset>


I tried the Muenchian method to find unique Customers, but if the customer ordered more than one item the Order was listed twice which breaks the DB rules. I am still new to XSLT and I started looking at using variables, but I became confused rather quickly. Any ideas would be appreciated.

[anotherVBnewbie]

What language are you wanting to use to parse the xml? If you wanted to use perl there is almost assuredly a module available to do it with. The one at http://cpan.uwinnipeg.ca/htdocs/DBD-AnyData/DBD/AnyData.html could be just what you're looking for.

[schenz]

I'm using .NET, and I have written some stuff in .NET that does what I need. However XSLT is supposed to be able to do what I want in one step.