7.7. More complex XML documents Excel works best with tabular data that naturally fits into rows and columns. XML documents, on the other hand, can have virtually any structure. This can make it challenging to map some XML document types to a worksheet. In our prior example, we had a simple XML document with some header information and some repeating element types that fit conveniently into one column each. 7.7.1 Different structures Worldwide accumulates all of its expense reports in a database for long-term analysis of trends. The database format has a slightly different structure. Instead of individual mileage, airFare, etc. elements, it has a single repeating amount element with an expenseTypeCode attribute indicating the type of expense. The corresponding XML document is shown in Example 7-4. Example 7-4. Modified expense report (expenses modified.xml) <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <expenseReport xmlns="http://xmlinoffice.com/expenses"> <empName>Ellen Sandler</empName> <empNum>305</empNum> <purpose>Sales meeting with ABC Corp.</purpose> <deptCode>305</deptCode> <billToCode>1104</billToCode> <periodFrom>2004-04-16</periodFrom> <periodTo>2004-04-18</periodTo> <expense> <date>2004-04-16</date> <explanation>Phone call from the hotel</explanation> <amount expenseTypeCode="801">.75</amount> </expense> <!-- ... --> <expense> <date>2004-04-16</date> <explanation>Dry Cleaning</explanation> <amount expenseTypeCode="883">16.75</amount> </expense> </expenseReport>
The schema for this document does not map directly to the expense report worksheet because the data that belongs in the various expense type columns comes from the same amount element. Excel does not allow you to map, say, the "Phone" column to "all amount elements with an expenseTypeCode attribute whose value is 801". XSLT stylesheets can be used to create compatibility between the worksheet-friendly XML and the database-compatible alternative. from worksheet to database Export the data to XML in the usual way. A stylesheet will then transform the XML data from its exported structure, shown in Example 7-3, to the database structure shown in Example 7-4. from database to worksheet For analysis of the historical data, you can create an XSLT stylesheet that works in the opposite direction. It transforms the database-compatible document structure into a structure that can be mapped directly to Excel. Paired transforms can also be used with other document types that don't naturally fit into a tabular structure. For example, those with multiple levels of hierarchy, or that contain element types whose content must be combined or split apart for use in the worksheet. | 
