Converting XML to XLS: Handling Simple XML Structure

Multiple levels of XML

This example shows how Exult handles multiple levels within the XML. The example XML is a database of customers and the orders placed by those customers. The Order information for each Customer appears as one or more child elements within the Customer element. Some customers have placed more than one Order so there could be multiple Order elements under each Customer element.

Building Table Relationships with Keys

Exult captures information about levels within XML using primary and foreign keys. In this example, the Customer and Order information is stored in separate tables as follows: Each row in the Orders table is linked to the correct row in the Customers table with foreign keys. Exult automatically figures out the relationship between the Customers and Orders from the XML structure.

Check out the Microsoft Excel Spreadsheet generated by Exult for the data sample shown below. Note that there are two worksheets, one for each table, within the XLS file.

The XML Sample

<Customers>
    <Customer CustomerID="AROUT">
        <CompanyName>Around the Horn</CompanyName>
        <ContactName>Thomas Hardy</ContactName>
        <ContactTitle>Sales Representative</ContactTitle>
        <Address>120 Hanover Sq.</Address>
        <City>London</City>
        <PostalCode>WA1 1DP</PostalCode>
        <Country>UK</Country>
        <Phone>(171) 555-7788</Phone>
        <Fax>(171) 555-6750</Fax>
        <Order OrderID="10355">
            <EmployeeID>6</EmployeeID>
            <OrderDate>1994-12-16</OrderDate>
            <RequiredDate>1995-01-13</RequiredDate>
            <ShippedDate>1994-12-21</ShippedDate>
            <ShipVia>1</ShipVia>
            <Freight>41.9500</Freight>
            <ShipName>Around the Horn</ShipName>
            <ShipAddress>Brook Farm Stratford St. Mary</ShipAddress>
            <ShipCity>Colchester</ShipCity>
            <ShipRegion>Essex</ShipRegion>
            <ShipPostalCode>CO7 6JX</ShipPostalCode>
        </Order>
        <Order OrderID="10383">
            <EmployeeID>8</EmployeeID>
            <OrderDate>1995-01-16</OrderDate>
            <RequiredDate>1995-02-13</RequiredDate>
            <ShippedDate>1995-01-18</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>34.2400</Freight>
            <ShipName>Around the Horn</ShipName>
            <ShipAddress>Brook Farm Stratford St. Mary</ShipAddress>
            <ShipCity>Colchester</ShipCity>
            <ShipRegion>Essex</ShipRegion>
            <ShipPostalCode>CO7 6JX</ShipPostalCode>
        </Order>
        <Order OrderID="10453">
            <EmployeeID>1</EmployeeID>
            <OrderDate>1995-03-24</OrderDate>
            <RequiredDate>1995-04-21</RequiredDate>
            <ShippedDate>1995-03-29</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>25.3600</Freight>
            <ShipName>Around the Horn</ShipName>
            <ShipAddress>Brook Farm Stratford St. Mary</ShipAddress>
            <ShipCity>Colchester</ShipCity>
            <ShipRegion>Essex</ShipRegion>
            <ShipPostalCode>CO7 6JX</ShipPostalCode>
        </Order>
        <Order OrderID="10558">
            <EmployeeID>1</EmployeeID>
            <OrderDate>1995-07-05</OrderDate>
            <RequiredDate>1995-08-02</RequiredDate>
            <ShippedDate>1995-07-11</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>72.9700</Freight>
            <ShipName>Around the Horn</ShipName>
            <ShipAddress>Brook Farm Stratford St. Mary</ShipAddress>
            <ShipCity>Colchester</ShipCity>
            <ShipRegion>Essex</ShipRegion>
            <ShipPostalCode>CO7 6JX</ShipPostalCode>
        </Order>
        <Order OrderID="10707">
            <EmployeeID>4</EmployeeID>
            <OrderDate>1995-11-16</OrderDate>
            <RequiredDate>1995-11-30</RequiredDate>
            <ShippedDate>1995-11-23</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>21.7400</Freight>
            <ShipName>Around the Horn</ShipName>
            <ShipAddress>Brook Farm Stratford St. Mary</ShipAddress>
            <ShipCity>Colchester</ShipCity>
            <ShipRegion>Essex</ShipRegion>
            <ShipPostalCode>CO7 6JX</ShipPostalCode>
        </Order>
        <Order OrderID="10741">
            <EmployeeID>4</EmployeeID>
            <OrderDate>1995-12-15</OrderDate>
            <RequiredDate>1995-12-29</RequiredDate>
            <ShippedDate>1995-12-19</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>10.9600</Freight>
            <ShipName>Around the Horn</ShipName>
            <ShipAddress>Brook Farm Stratford St. Mary</ShipAddress>
            <ShipCity>Colchester</ShipCity>
            <ShipRegion>Essex</ShipRegion>
            <ShipPostalCode>CO7 6JX</ShipPostalCode>
        </Order>
        <Order OrderID="10743">
            <EmployeeID>1</EmployeeID>
            <OrderDate>1995-12-18</OrderDate>
            <RequiredDate>1996-01-15</RequiredDate>
            <ShippedDate>1995-12-22</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>23.7200</Freight>
            <ShipName>Around the Horn</ShipName>
            <ShipAddress>Brook Farm Stratford St. Mary</ShipAddress>
            <ShipCity>Colchester</ShipCity>
            <ShipRegion>Essex</ShipRegion>
            <ShipPostalCode>CO7 6JX</ShipPostalCode>
        </Order>
        <Order OrderID="10768">
            <EmployeeID>3</EmployeeID>
            <OrderDate>1996-01-08</OrderDate>
            <RequiredDate>1996-02-05</RequiredDate>
            <ShippedDate>1996-01-15</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>146.3200</Freight>
            <ShipName>Around the Horn</ShipName>
            <ShipAddress>Brook Farm Stratford St. Mary</ShipAddress>
            <ShipCity>Colchester</ShipCity>
            <ShipRegion>Essex</ShipRegion>
            <ShipPostalCode>CO7 6JX</ShipPostalCode>
        </Order>
        <Order OrderID="10793">
            <EmployeeID>3</EmployeeID>
            <OrderDate>1996-01-24</OrderDate>
            <RequiredDate>1996-02-21</RequiredDate>
            <ShippedDate>1996-02-08</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>4.5200</Freight>
            <ShipName>Around the Horn</ShipName>
            <ShipAddress>Brook Farm Stratford St. Mary</ShipAddress>
            <ShipCity>Colchester</ShipCity>
            <ShipRegion>Essex</ShipRegion>
            <ShipPostalCode>CO7 6JX</ShipPostalCode>
        </Order>
        <Order OrderID="10864">
            <EmployeeID>4</EmployeeID>
            <OrderDate>1996-03-04</OrderDate>
            <RequiredDate>1996-04-01</RequiredDate>
            <ShippedDate>1996-03-11</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>3.0400</Freight>
            <ShipName>Around the Horn</ShipName>
            <ShipAddress>Brook Farm Stratford St. Mary</ShipAddress>
            <ShipCity>Colchester</ShipCity>
            <ShipRegion>Essex</ShipRegion>
            <ShipPostalCode>CO7 6JX</ShipPostalCode>
        </Order>
        <Order OrderID="10920">
            <EmployeeID>4</EmployeeID>
            <OrderDate>1996-04-02</OrderDate>
            <RequiredDate>1996-04-30</RequiredDate>
            <ShippedDate>1996-04-08</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>29.6100</Freight>
            <ShipName>Around the Horn</ShipName>
            <ShipAddress>Brook Farm Stratford St. Mary</ShipAddress>
            <ShipCity>Colchester</ShipCity>
            <ShipRegion>Essex</ShipRegion>
            <ShipPostalCode>CO7 6JX</ShipPostalCode>
        </Order>
        <Order OrderID="10953">
            <EmployeeID>9</EmployeeID>
            <OrderDate>1996-04-15</OrderDate>
            <RequiredDate>1996-04-29</RequiredDate>
            <ShippedDate>1996-04-24</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>23.7200</Freight>
            <ShipName>Around the Horn</ShipName>
            <ShipAddress>Brook Farm Stratford St. Mary</ShipAddress>
            <ShipCity>Colchester</ShipCity>
            <ShipRegion>Essex</ShipRegion>
            <ShipPostalCode>CO7 6JX</ShipPostalCode>
        </Order>
        <Order OrderID="11016">
            <EmployeeID>9</EmployeeID>
            <OrderDate>1996-05-10</OrderDate>
            <RequiredDate>1996-06-07</RequiredDate>
            <ShippedDate>1996-05-13</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>33.8000</Freight>
            <ShipName>Around the Horn</ShipName>
            <ShipAddress>Brook Farm Stratford St. Mary</ShipAddress>
            <ShipCity>Colchester</ShipCity>
            <ShipRegion>Essex</ShipRegion>
            <ShipPostalCode>CO7 6JX</ShipPostalCode>
        </Order>
    </Customer>
    <Customer CustomerID="CHOPS">
        <CompanyName>Chop-suey Chinese</CompanyName>
        <ContactName>Yang Wang</ContactName>
        <ContactTitle>Owner</ContactTitle>
        <Address>Hauptstr. 29</Address>
        <City>Bern</City>
        <PostalCode>3012</PostalCode>
        <Country>Switzerland</Country>
        <Phone>0452-076545</Phone>
        <Order OrderID="10254">
            <EmployeeID>5</EmployeeID>
            <OrderDate>1994-08-11</OrderDate>
            <RequiredDate>1994-09-08</RequiredDate>
            <ShippedDate>1994-08-23</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>22.9800</Freight>
            <ShipName>Chop-suey Chinese</ShipName>
            <ShipAddress>Hauptstr. 31</ShipAddress>
            <ShipCity>Bern</ShipCity>
            <ShipPostalCode>3012</ShipPostalCode>
        </Order>
        <Order OrderID="10370">
            <EmployeeID>6</EmployeeID>
            <OrderDate>1995-01-03</OrderDate>
            <RequiredDate>1995-01-31</RequiredDate>
            <ShippedDate>1995-01-27</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>1.1700</Freight>
            <ShipName>Chop-suey Chinese</ShipName>
            <ShipAddress>Hauptstr. 31</ShipAddress>
            <ShipCity>Bern</ShipCity>
            <ShipPostalCode>3012</ShipPostalCode>
        </Order>
        <Order OrderID="10519">
            <EmployeeID>6</EmployeeID>
            <OrderDate>1995-05-29</OrderDate>
            <RequiredDate>1995-06-26</RequiredDate>
            <ShippedDate>1995-06-01</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>91.7600</Freight>
            <ShipName>Chop-suey Chinese</ShipName>
            <ShipAddress>Hauptstr. 31</ShipAddress>
            <ShipCity>Bern</ShipCity>
            <ShipPostalCode>3012</ShipPostalCode>
        </Order>
        <Order OrderID="10731">
            <EmployeeID>7</EmployeeID>
            <OrderDate>1995-12-07</OrderDate>
            <RequiredDate>1996-01-04</RequiredDate>
            <ShippedDate>1995-12-15</ShippedDate>
            <ShipVia>1</ShipVia>
            <Freight>96.6500</Freight>
            <ShipName>Chop-suey Chinese</ShipName>
            <ShipAddress>Hauptstr. 31</ShipAddress>
            <ShipCity>Bern</ShipCity>
            <ShipPostalCode>3012</ShipPostalCode>
        </Order>
        <Order OrderID="10746">
            <EmployeeID>1</EmployeeID>
            <OrderDate>1995-12-20</OrderDate>
            <RequiredDate>1996-01-17</RequiredDate>
            <ShippedDate>1995-12-22</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>31.4300</Freight>
            <ShipName>Chop-suey Chinese</ShipName>
            <ShipAddress>Hauptstr. 31</ShipAddress>
            <ShipCity>Bern</ShipCity>
            <ShipPostalCode>3012</ShipPostalCode>
        </Order>
        <Order OrderID="10966">
            <EmployeeID>4</EmployeeID>
            <OrderDate>1996-04-19</OrderDate>
            <RequiredDate>1996-05-17</RequiredDate>
            <ShippedDate>1996-05-08</ShippedDate>
            <ShipVia>1</ShipVia>
            <Freight>27.1900</Freight>
            <ShipName>Chop-suey Chinese</ShipName>
            <ShipAddress>Hauptstr. 31</ShipAddress>
            <ShipCity>Bern</ShipCity>
            <ShipPostalCode>3012</ShipPostalCode>
        </Order>
        <Order OrderID="11029">
            <EmployeeID>4</EmployeeID>
            <OrderDate>1996-05-16</OrderDate>
            <RequiredDate>1996-06-13</RequiredDate>
            <ShippedDate>1996-05-27</ShippedDate>
            <ShipVia>1</ShipVia>
            <Freight>47.8400</Freight>
            <ShipName>Chop-suey Chinese</ShipName>
            <ShipAddress>Hauptstr. 31</ShipAddress>
            <ShipCity>Bern</ShipCity>
            <ShipPostalCode>3012</ShipPostalCode>
        </Order>
        <Order OrderID="11041">
            <EmployeeID>3</EmployeeID>
            <OrderDate>1996-05-22</OrderDate>
            <RequiredDate>1996-06-19</RequiredDate>
            <ShippedDate>1996-05-28</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>48.2200</Freight>
            <ShipName>Chop-suey Chinese</ShipName>
            <ShipAddress>Hauptstr. 31</ShipAddress>
            <ShipCity>Bern</ShipCity>
            <ShipPostalCode>3012</ShipPostalCode>
        </Order>
    </Customer>
    <Customer CustomerID="GREAL">
        <CompanyName>Great Lakes Food Market</CompanyName>
        <ContactName>Howard Snyder</ContactName>
        <ContactTitle>Marketing Manager</ContactTitle>
        <Address>2732 Baker Blvd.</Address>
        <City>Eugene</City>
        <Region>OR</Region>
        <PostalCode>97403</PostalCode>
        <Country>USA</Country>
        <Phone>(503) 555-7555</Phone>
        <Order OrderID="10528">
            <EmployeeID>6</EmployeeID>
            <OrderDate>1995-06-06</OrderDate>
            <RequiredDate>1995-06-20</RequiredDate>
            <ShippedDate>1995-06-09</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>3.3500</Freight>
            <ShipName>Great Lakes Food Market</ShipName>
            <ShipAddress>2732 Baker Blvd.</ShipAddress>
            <ShipCity>Eugene</ShipCity>
            <ShipRegion>OR</ShipRegion>
            <ShipPostalCode>97403</ShipPostalCode>
        </Order>
        <Order OrderID="10589">
            <EmployeeID>8</EmployeeID>
            <OrderDate>1995-08-04</OrderDate>
            <RequiredDate>1995-09-01</RequiredDate>
            <ShippedDate>1995-08-14</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>4.4200</Freight>
            <ShipName>Great Lakes Food Market</ShipName>
            <ShipAddress>2732 Baker Blvd.</ShipAddress>
            <ShipCity>Eugene</ShipCity>
            <ShipRegion>OR</ShipRegion>
            <ShipPostalCode>97403</ShipPostalCode>
        </Order>
        <Order OrderID="10616">
            <EmployeeID>1</EmployeeID>
            <OrderDate>1995-08-31</OrderDate>
            <RequiredDate>1995-09-28</RequiredDate>
            <ShippedDate>1995-09-05</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>116.5300</Freight>
            <ShipName>Great Lakes Food Market</ShipName>
            <ShipAddress>2732 Baker Blvd.</ShipAddress>
            <ShipCity>Eugene</ShipCity>
            <ShipRegion>OR</ShipRegion>
            <ShipPostalCode>97403</ShipPostalCode>
        </Order>
        <Order OrderID="10617">
            <EmployeeID>4</EmployeeID>
            <OrderDate>1995-08-31</OrderDate>
            <RequiredDate>1995-09-28</RequiredDate>
            <ShippedDate>1995-09-04</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>18.5300</Freight>
            <ShipName>Great Lakes Food Market</ShipName>
            <ShipAddress>2732 Baker Blvd.</ShipAddress>
            <ShipCity>Eugene</ShipCity>
            <ShipRegion>OR</ShipRegion>
            <ShipPostalCode>97403</ShipPostalCode>
        </Order>
        <Order OrderID="10656">
            <EmployeeID>6</EmployeeID>
            <OrderDate>1995-10-05</OrderDate>
            <RequiredDate>1995-11-02</RequiredDate>
            <ShippedDate>1995-10-11</ShippedDate>
            <ShipVia>1</ShipVia>
            <Freight>57.1500</Freight>
            <ShipName>Great Lakes Food Market</ShipName>
            <ShipAddress>2732 Baker Blvd.</ShipAddress>
            <ShipCity>Eugene</ShipCity>
            <ShipRegion>OR</ShipRegion>
            <ShipPostalCode>97403</ShipPostalCode>
        </Order>
        <Order OrderID="10681">
            <EmployeeID>3</EmployeeID>
            <OrderDate>1995-10-26</OrderDate>
            <RequiredDate>1995-11-23</RequiredDate>
            <ShippedDate>1995-10-31</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>76.1300</Freight>
            <ShipName>Great Lakes Food Market</ShipName>
            <ShipAddress>2732 Baker Blvd.</ShipAddress>
            <ShipCity>Eugene</ShipCity>
            <ShipRegion>OR</ShipRegion>
            <ShipPostalCode>97403</ShipPostalCode>
        </Order>
        <Order OrderID="10816">
            <EmployeeID>4</EmployeeID>
            <OrderDate>1996-02-06</OrderDate>
            <RequiredDate>1996-03-05</RequiredDate>
            <ShippedDate>1996-03-06</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>719.7800</Freight>
            <ShipName>Great Lakes Food Market</ShipName>
            <ShipAddress>2732 Baker Blvd.</ShipAddress>
            <ShipCity>Eugene</ShipCity>
            <ShipRegion>OR</ShipRegion>
            <ShipPostalCode>97403</ShipPostalCode>
        </Order>
        <Order OrderID="10936">
            <EmployeeID>3</EmployeeID>
            <OrderDate>1996-04-08</OrderDate>
            <RequiredDate>1996-05-06</RequiredDate>
            <ShippedDate>1996-04-17</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>33.6800</Freight>
            <ShipName>Great Lakes Food Market</ShipName>
            <ShipAddress>2732 Baker Blvd.</ShipAddress>
            <ShipCity>Eugene</ShipCity>
            <ShipRegion>OR</ShipRegion>
            <ShipPostalCode>97403</ShipPostalCode>
        </Order>
        <Order OrderID="11006">
            <EmployeeID>3</EmployeeID>
            <OrderDate>1996-05-07</OrderDate>
            <RequiredDate>1996-06-04</RequiredDate>
            <ShippedDate>1996-05-15</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>25.1900</Freight>
            <ShipName>Great Lakes Food Market</ShipName>
            <ShipAddress>2732 Baker Blvd.</ShipAddress>
            <ShipCity>Eugene</ShipCity>
            <ShipRegion>OR</ShipRegion>
            <ShipPostalCode>97403</ShipPostalCode>
        </Order>
        <Order OrderID="11040">
            <EmployeeID>4</EmployeeID>
            <OrderDate>1996-05-22</OrderDate>
            <RequiredDate>1996-06-19</RequiredDate>
            <ShipVia>3</ShipVia>
            <Freight>18.8400</Freight>
            <ShipName>Great Lakes Food Market</ShipName>
            <ShipAddress>2732 Baker Blvd.</ShipAddress>
            <ShipCity>Eugene</ShipCity>
            <ShipRegion>OR</ShipRegion>
            <ShipPostalCode>97403</ShipPostalCode>
        </Order>
        <Order OrderID="11061">
            <EmployeeID>4</EmployeeID>
            <OrderDate>1996-05-30</OrderDate>
            <RequiredDate>1996-07-11</RequiredDate>
            <ShipVia>3</ShipVia>
            <Freight>14.0100</Freight>
            <ShipName>Great Lakes Food Market</ShipName>
            <ShipAddress>2732 Baker Blvd.</ShipAddress>
            <ShipCity>Eugene</ShipCity>
            <ShipRegion>OR</ShipRegion>
            <ShipPostalCode>97403</ShipPostalCode>
        </Order>
    </Customer>
    <Customer CustomerID="BSBEV">
        <CompanyName>B's Beverages</CompanyName>
        <ContactName>Victoria Ashworth</ContactName>
        <ContactTitle>Sales Representative</ContactTitle>
        <Address>Fauntleroy Circus</Address>
        <City>London</City>
        <PostalCode>EC2 5NT</PostalCode>
        <Country>UK</Country>
        <Phone>(171) 555-1212</Phone>
        <Order OrderID="10289">
            <EmployeeID>7</EmployeeID>
            <OrderDate>1994-09-26</OrderDate>
            <RequiredDate>1994-10-24</RequiredDate>
            <ShippedDate>1994-09-28</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>22.7700</Freight>
            <ShipName>B's Beverages</ShipName>
            <ShipAddress>Fauntleroy Circus</ShipAddress>
            <ShipCity>London</ShipCity>
            <ShipPostalCode>EC2 5NT</ShipPostalCode>
        </Order>
        <Order OrderID="10471">
            <EmployeeID>2</EmployeeID>
            <OrderDate>1995-04-11</OrderDate>
            <RequiredDate>1995-05-09</RequiredDate>
            <ShippedDate>1995-04-18</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>45.5900</Freight>
            <ShipName>B's Beverages</ShipName>
            <ShipAddress>Fauntleroy Circus</ShipAddress>
            <ShipCity>London</ShipCity>
            <ShipPostalCode>EC2 5NT</ShipPostalCode>
        </Order>
        <Order OrderID="10484">
            <EmployeeID>3</EmployeeID>
            <OrderDate>1995-04-24</OrderDate>
            <RequiredDate>1995-05-22</RequiredDate>
            <ShippedDate>1995-05-02</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>6.8800</Freight>
            <ShipName>B's Beverages</ShipName>
            <ShipAddress>Fauntleroy Circus</ShipAddress>
            <ShipCity>London</ShipCity>
            <ShipPostalCode>EC2 5NT</ShipPostalCode>
        </Order>
        <Order OrderID="10538">
            <EmployeeID>9</EmployeeID>
            <OrderDate>1995-06-15</OrderDate>
            <RequiredDate>1995-07-13</RequiredDate>
            <ShippedDate>1995-06-16</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>4.8700</Freight>
            <ShipName>B's Beverages</ShipName>
            <ShipAddress>Fauntleroy Circus</ShipAddress>
            <ShipCity>London</ShipCity>
            <ShipPostalCode>EC2 5NT</ShipPostalCode>
        </Order>
        <Order OrderID="10539">
            <EmployeeID>6</EmployeeID>
            <OrderDate>1995-06-16</OrderDate>
            <RequiredDate>1995-07-14</RequiredDate>
            <ShippedDate>1995-06-23</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>12.3600</Freight>
            <ShipName>B's Beverages</ShipName>
            <ShipAddress>Fauntleroy Circus</ShipAddress>
            <ShipCity>London</ShipCity>
            <ShipPostalCode>EC2 5NT</ShipPostalCode>
        </Order>
        <Order OrderID="10578">
            <EmployeeID>4</EmployeeID>
            <OrderDate>1995-07-25</OrderDate>
            <RequiredDate>1995-08-22</RequiredDate>
            <ShippedDate>1995-08-25</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>29.6000</Freight>
            <ShipName>B's Beverages</ShipName>
            <ShipAddress>Fauntleroy Circus</ShipAddress>
            <ShipCity>London</ShipCity>
            <ShipPostalCode>EC2 5NT</ShipPostalCode>
        </Order>
        <Order OrderID="10599">
            <EmployeeID>6</EmployeeID>
            <OrderDate>1995-08-15</OrderDate>
            <RequiredDate>1995-09-26</RequiredDate>
            <ShippedDate>1995-08-21</ShippedDate>
            <ShipVia>3</ShipVia>
            <Freight>29.9800</Freight>
            <ShipName>B's Beverages</ShipName>
            <ShipAddress>Fauntleroy Circus</ShipAddress>
            <ShipCity>London</ShipCity>
            <ShipPostalCode>EC2 5NT</ShipPostalCode>
        </Order>
        <Order OrderID="10943">
            <EmployeeID>4</EmployeeID>
            <OrderDate>1996-04-10</OrderDate>
            <RequiredDate>1996-05-08</RequiredDate>
            <ShippedDate>1996-04-18</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>2.1700</Freight>
            <ShipName>B's Beverages</ShipName>
            <ShipAddress>Fauntleroy Circus</ShipAddress>
            <ShipCity>London</ShipCity>
            <ShipPostalCode>EC2 5NT</ShipPostalCode>
        </Order>
        <Order OrderID="10947">
            <EmployeeID>3</EmployeeID>
            <OrderDate>1996-04-12</OrderDate>
            <RequiredDate>1996-05-10</RequiredDate>
            <ShippedDate>1996-04-15</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>3.2600</Freight>
            <ShipName>B's Beverages</ShipName>
            <ShipAddress>Fauntleroy Circus</ShipAddress>
            <ShipCity>London</ShipCity>
            <ShipPostalCode>EC2 5NT</ShipPostalCode>
        </Order>
        <Order OrderID="11023">
            <EmployeeID>1</EmployeeID>
            <OrderDate>1996-05-14</OrderDate>
            <RequiredDate>1996-05-28</RequiredDate>
            <ShippedDate>1996-05-24</ShippedDate>
            <ShipVia>2</ShipVia>
            <Freight>123.8300</Freight>
            <ShipName>B's Beverages</ShipName>
            <ShipAddress>Fauntleroy Circus</ShipAddress>
            <ShipCity>London</ShipCity>
            <ShipPostalCode>EC2 5NT</ShipPostalCode>
        </Order>
    </Customer>
</Customers>

Data in Tabular format

Exult extracts data from the above XML into two tables as shown below: a Customer and an Order table. Rows within the Order table are linked to the correct row in the Customer table with a foreign key. Note that the tables as shown below have been edited slightly as follows:

Information has been removed from the tables shown below for clarity. This is shown by ellipsis (...) within the tables.
A distinctive styling has been applied for display purposes.

Important Points to Note

In the Order table, column Customer::FKEY contains the foreign key value refering to the relevant row in the Customer table. For example, looking at the XML and comparing the key values, one can see that customer AROUT placed 13 orders: Order::KEY 1 through 13 refers to Customer::FKEY 1 which is the row in the Customer table for that customer.
Attribute information has been extracted to a column in a manner similar to the element information. For example, CustomerID in the above example XML is an attribute and has been extracted as a column in the table below.
When no value is specified for a column within the XML, it is extracted with a NULL for its value. In the example XML above, customers AROUT, CHOPS, etc. have a NULL value for the Region.

Customer

Customer::KEY	Customers::FKEY	CustomerID	CompanyName	ContactName	...	Region
1	1	AROUT	Around the Horn	Thomas Hardy	...
2	1	CHOPS	Chop-suey Chinese	Yang Wang	...
3	1	GREAL	Great Lakes Food Market	Howard Snyder	...	OR
4	1	BSBEV	B's Beverages	Victoria Ashworth	...

Order

Order::KEY	Customer::FKEY	OrderID	EmployeeID	OrderDate	...	ShipPostalCode
1	1	10355	6	1994-12-16	...	CO7 6JX
2	1	10383	8	1995-01-16	...	CO7 6JX
3	1	10453	1	1995-03-24	...	CO7 6JX
4	1	10558	1	1995-07-05	...	CO7 6JX
5	1	10707	4	1995-11-16	...	CO7 6JX
6	1	10741	4	1995-12-15	...	CO7 6JX
7	1	10743	1	1995-12-18	...	CO7 6JX
8	1	10768	3	1996-01-08	...	CO7 6JX
9	1	10793	3	1996-01-24	...	CO7 6JX
10	1	10864	4	1996-03-04	...	CO7 6JX
11	1	10920	4	1996-04-02	...	CO7 6JX
12	1	10953	9	1996-04-15	...	CO7 6JX
13	1	11016	9	1996-05-10	...	CO7 6JX
14	2	10254	5	1994-08-11	...	3012
15	2	10370	6	1995-01-03	...	3012
16	2	10519	6	1995-05-29	...	3012
17	2	10731	7	1995-12-07	...	3012
18	2	10746	1	1995-12-20	...	3012
19	2	10966	4	1996-04-19	...	3012
20	2	11029	4	1996-05-16	...	3012
21	2	11041	3	1996-05-22	...	3012
22	3	10528	6	1995-06-06	...	97403
23	3	10589	8	1995-08-04	...	97403
24	3	10616	1	1995-08-31	...	97403
25	3	10617	4	1995-08-31	...	97403
26	3	10656	6	1995-10-05	...	97403
27	3	10681	3	1995-10-26	...	97403
28	3	10816	4	1996-02-06	...	97403
29	3	10936	3	1996-04-08	...	97403
30	3	11006	3	1996-05-07	...	97403
31	3	11040	4	1996-05-22	...	97403
32	3	11061	4	1996-05-30	...	97403
33	4	10289	7	1994-09-26	...	EC2 5NT
34	4	10471	2	1995-04-11	...	EC2 5NT
35	4	10484	3	1995-04-24	...	EC2 5NT
36	4	10538	9	1995-06-15	...	EC2 5NT
37	4	10539	6	1995-06-16	...	EC2 5NT
38	4	10578	4	1995-07-25	...	EC2 5NT
39	4	10599	6	1995-08-15	...	EC2 5NT
40	4	10943	4	1996-04-10	...	EC2 5NT
41	4	10947	3	1996-04-12	...	EC2 5NT
42	4	11023	1	1996-05-14	...	EC2 5NT

Argon DataViz

Create Pivot Tables and Pivot Charts from Large Datasets.

Download
Free Trial
Buy Now
for US~~$499~~ $299/yr

Swift XML Converter

Query & extract XML data into Microsoft Excel (XLS), Microsoft Access (MDB or ACCDB), or CSV.

Download
Free Trial
Buy Now
for US~~$250~~ $195

Exult Standard

Convert XML into Microsoft Excel (XLS), Microsoft Access (MDB or ACCDB), or CSV.