Converting XML to Microsoft Excel Spreadsheet (XLS)

Let us look at a simple example of how the Exult XML Conversion Wizard can convert XML to a Microsoft Excel Spreadsheet (XLS). The XML for this example is quite simple and serves to illustrate how the data is handled. However, Exult can handle far more complex XML than is shown below.:

Here is the Microsoft Excel Spreadsheet generated by Exult for the data sample shown below.

The XML Sample

<Customers>
    <Customer CustomerID="AROUT">
        <CompanyName>Around the Horn</CompanyName>
        <ContactName>Thomas Hardy</ContactName>
        <Address>120 Hanover Sq.</Address>
        <City>London</City>
        <PostalCode>WA1 1DP</PostalCode>
        <Country>UK</Country>
    </Customer>

    <Customer CustomerID="CHOPS">
        <CompanyName>Chop-suey Chinese</CompanyName>
        <ContactName>Yang Wang</ContactName>
        <Address>Hauptstr. 29</Address>
        <City>Bern</City>
        <PostalCode>3012</PostalCode>
        <Country>Switzerland</Country>
    </Customer>

    <Customer CustomerID="GREAL">
        <CompanyName>Great Lakes Food Market</CompanyName>
        <ContactName>Howard Snyder</ContactName>
        <Address>2732 Baker Blvd.</Address>
        <City>Eugene</City>
        <Region>OR</Region>
        <PostalCode>97403</PostalCode>
        <Country>USA</Country>
    </Customer>

    <Customer CustomerID="BSBEV">
        <CompanyName>B's Beverages</CompanyName>
        <ContactName>Victoria Ashworth</ContactName>
        <Address>Fauntleroy Circus</Address>
        <City>London</City>
        <PostalCode>EC2 5NT</PostalCode>
        <Country>UK</Country>
    </Customer>

    <Customer CustomerID="HUNGC">
        <CompanyName>Hungry Coyote Import Store</CompanyName>
        <ContactName>Yoshi Latimer</ContactName>
        <Address>City Center Plaza 516 Main St.</Address>
        <City>Elgin</City>
        <Region>OR</Region>
        <PostalCode>97827</PostalCode>
        <Country>USA</Country>
    </Customer>

    <Customer CustomerID="LETSS">
        <CompanyName>Let's Stop N Shop</CompanyName>
        <ContactName>Jaime Yorres</ContactName>
        <Address>87 Polk St. Suite 5</Address>
        <City>San Francisco</City>
        <Region>CA</Region>
        <PostalCode>94117</PostalCode>
        <Country>USA</Country>
    </Customer>

    <Customer CustomerID="WARTH">
        <CompanyName>Wartian Herkku</CompanyName>
        <ContactName>Pirkko Koskitalo</ContactName>
        <Address>Torikatu 38</Address>
        <City>Oulu</City>
        <PostalCode>90110</PostalCode>
        <Country>Finland</Country>
    </Customer>
</Customers>

Data in Tabular format

Exult extracts data from the above XML into to a table of the following form. Note that primary and foreign key information has been removed from the output generated by Exult and a distinctive styling has been applied for display purposes.

Note the following important points about the extraction:

  • 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, BSBEV or WARTH have a NULL value for the Region.

Customer

CustomerID CompanyName ContactName Address City PostalCode Country Region
AROUT Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK  
CHOPS Chop-suey Chinese Yang Wang Hauptstr. 29 Bern 3012 Switzerland  
GREAL Great Lakes Food Market Howard Snyder 2732 Baker Blvd. Eugene 97403 USA OR
BSBEV B's Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK  
HUNGC Hungry Coyote Import Store Yoshi Latimer City Center Plaza 516 Main St. Elgin 97827 USA OR
LETSS Let's Stop N Shop Jaime Yorres 87 Polk St. Suite 5 San Francisco 94117 USA CA
WARTH Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland  

Argon DataViz

Create Pivot Tables and Pivot Charts from Large Datasets.

Swift XML Converter

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

Exult Standard

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

Exult SQL Server

Shred and import XML into SQL Server.

Exult MySQL

Import XML data into automatically created MySQL tables.

Exult Oracle

Import XML into Oracle.