Section 3.7. Exporting Through XML Maps

3.7. Exporting Through XML Maps

Once you have created lists containing XML data, you can export that data to a new XML file from Excel two ways:

By saving the workbook using the XML Data file type
By clicking the Export XML toolbar button or selecting Export from the Data menu's XML submenu

In either case, you can only export data using one XML map at a time. If a workbook contains more than one XML map, you are prompted to choose the map to use (Figure 3-13).

Figure 3-13. Exporting XML uses only one XML map at a time

3.7.1. How it works

When Excel exports a list as XML, it uses the schema stored in the workbook to generate XML that matches the XML source file that the list was created from. However, Excel omits the following items:

Schema definitions
Processing instructions
XML nodes not included in the list

For example, if you create a list from SimpleOrder.xml containing only names and totals, only those elements are saved when you export the list as XML:

      <Orders>          <Order>              <BillTo>                  <Address>                      <Name>Joe Magnus</Name>                  </Address>              </BillTo>              <Total>                  <Due>572.4</Due>              </Total>          </Order>          <Order>...</Order      </Orders>

In the preceding XML, the original address and order information is omitted because it wasn't included in the list. From Excel's point of view, the data doesn't exist if it doesn't reside on a worksheet somewhere.

3.7.2. What this means

The limitations that come with XML maps imply a set of approaches when using them with XML. You can't just assume that you will be able to successfully import, edit, and export arbitrary XML data using Excel. XML maps are best suited for XML structured a certain way.

For example, the preceding SimpleOrder.xml sample requires some changes if you want to be able to view and edit orders via XML maps. Specifically:

Each order should be stored in a separate file. XML maps can't export lists of lists, so including multiple orders, each with multiple line items, prevents you from exporting the orders.
Line items must be presented as a separate list. Simply importing an order as a single list results in denormalized data, which can't be exported from the list.

These changes and other recommendations are explained in the following sections.

3.7.2.1 Avoid lists of lists

Excel can import XML that contains lists more than one level deep, but it can't export those lists. In XML schema terminology, a list is an element with a maxOccurs attribute greater than one. Therefore, XML using the following schema can't be exported from an XML map:

      <xsd:element minOccurs="0" maxOccurs="unbounded" nillable="true" name="Order"  form="qualified">          <xsd:complexType>              <xsd:sequence minOccurs="0"> ... </xsd:sequence>              <xsd:element minOccurs="0" maxOccurs="unbounded" nillable="true"               name="Line" form="qualified">                  <xsd:complexType>                      <xsd:sequence minOccurs="0"> ...</xsd:sequence>                  </xsd:complexType>               </xsd:element>          </xsd:complexType>      </xsd:element>

You can solve this problem by breaking the source XML into smaller pieces. In the case of SimpleOrder.xml , this means creating a separate file for each Order node. The XML map's root node then becomes Order (Figure 3-14).

Figure 3-14. Break XML into smaller files to avoid lists of lists

You can organize the new, smaller files into a separate folder or by using a unique file extension, such as ".ord". For example, the following code allows the user to select an order file to open in Excel:

      Sub cmdOpenOrder_Click(  )          ' Get a file name to open. Use ".ord" extension for orders.          Dim fname As String          fname = Application.GetOpenFilename("Orders (*.ord),*.ord", 1, _           "Open an Order", "Open", False)          If fname <> "" Then              ThisWorkbook.XmlMaps("Order_Map").Import (fname)          End If      End Sub

Using the unique .ord file extension organizes orders (Figure 3-15). Excel (and XML) don't care what file extension you use when importing or exporting files.

Figure 3-15. Organize XML files using a unique file extension

3.7.2.2 Avoid denormalized data

If you drag the Order node shown in Figure 3-14 onto a worksheet, you get a list containing denormalized data, as shown in Figure 3-16.

Denormalized means that non-repeating data elements appear multiple times on the worksheet. A user could change one of the non-repeating items, such as Name, on one row, making that item inconsistent with other rows that are supposed to show the same data. There is no way for Excel to reconcile this inconsistency, so the list can't be exported.

To avoid this, create non-repeating and repeating nodes in separate lists (Figure 3-17).

Figure 3-16. A list with denormalized data

Figure 3-17. Put repeating and non-repeating data items in separate lists to avoid denormalized data

3.7.2.3 Create an XML schema

Allowing Excel to infer a schema for an XML map is fine if the nodes don't contain optional items or if the first occurrence of each node contains all of its possible children. Otherwise, Excel may omit items from the schema it creates and some nodes won't appear in the XML map.

You can solve this problem by creating an XML schema and referencing that schema in the XML file you import. Excel copies the referenced XML schema into the XML map when the XML map is created.

Having an external XML schema is also useful for making changes to the XML map. As mentioned earlier, you can't update an XML map inside of Excel; you can, however, modify the XML schema stored in the workbook by editing it outside of Excel. To edit an XML map schema:

In Excel, save the workbook as an XML spreadsheet.
Close the workbook in Excel.
Open the XML spreadsheet in an XML editor. It is a good idea to use a full-featured XML editor here because the schema generated by Excel does not include whitespace such as tabs and linefeeds.
Edit the items in the MapInfo/Schema node as needed, or simply replace the entire Schema node with the contents of your external schema definition file.
Save the file.
Open the workbook in Excel and click Refresh XML Data to verify that the schema is still valid.

The XML spreadsheet nodes for the schema appear as follows. The nodes to edit or replace are highlighted in bold .

      <x2:MapInfo x2:HideInactiveListBorder="false"       x2:SelectionNamespaces="xmlns:ns1='http://www.mstrainingkits.com'">      <x2:Schema x2:       x2:Namespace="http://www.mstrainingkits.com">              <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"                targetNamespace="http://www.mstrainingkits.com"                xmlns:ns0="http://www.mstrainingkits.com">                       <xsd:element nillable="true" name="Order">                          ...                          </xsd:element>               </xsd:schema>          </x2:Schema>      </x2:MapInfo>

3.7.2.4 Include all nodes if exporting

When you export XML, Excel takes the data found in mapped items on worksheets, applies the XML map, and generates XML nodes defined in the XML map's schema. If some of the XML map's data nodes are not mapped, that data is omitted from the exported XML.

In some cases, this is what you want. But if you are trying to read and write an XML file without losing content, you need to make sure that all elements from the XML map appear somewhere on the worksheet (even if they are hidden).

In cases where a node contains a calculated value, you will need to perform the calculation in a non-mapped cell, then copy that value to the mapped cell before exporting (see Figure 3-17).

The Save Order button in the sample copies the calculated subtotal, tax, and total values to cells created from the XML map before exporting the XML using the following code:

      Sub cmdSaveOrder_Click(  )          ' Update mapped cells with calculated values.          Range("XmlSubTotal") = Range("SubTotal")          Range("XmlTax") = Range("Tax")          Range("XmlTotal") = Range("Total")          ' Create file name to save.          Dim fname As String          fname = ThisWorkbook.path & "\" & Range("OrderID") & ".ord"          ' Save the order.          ThisWorkbook.XmlMaps("Order_Map").Export fname, True      End Sub

3.7.2.5 Other things to avoid

There are a number of other XML schema constructs that Excel does not support when importing XML, and a number of schema constructs that are not supported when exporting XML. These constructs are listed in Table 3-1 and Table 3-2.

Table 3-1. XML schema features not supported when importing XML
Feature	Description
any, anyAttribute	The any and anyAttribute elements allow you to include items that are not declared by the schema. Excel requires imported schemas to be explicit.
Recursive structures	Excel does not support recursive structures that are more than one level deep.
Abstract elements	Abstract elements are meant to be declared in the schema, but never used as elements. Abstract elements depend on other elements being substituted for the abstract element.
Substitution groups	Substitution groups allow an element to be swapped wherever another element is referenced. An element indicates that it's a member of another element's substitution group through the `substitutionGroup` attribute.
Mixed content	Mixed content is declared using mixed="true" on a complex type definition. Excel does not support the simple content of the complex type, but does support the child tags and attributes defined in that complex type.

Table 3-2. XML schema features not supported when exporting XML
Feature	Description
Lists of lists	Excel can only export repeating items that are one level deep. See the previous section "Avoid Lists of Lists".
Denormalized data	See the previous section "Avoid Denormalized Data".
Non-repeating siblings	If nonrepeating items are mapped to lists, they will result in denormalized data.
Repeating elements	If the repetition is not defined by an ancestor, the data relationships can't be preserved.
Child elements from different parents	If children from different XML maps are mapped to the same list, the relationship can't be preserved.
Choice	Elements that are part of an XML schema choice construct can't be exported.