Custom XML Mappings

Custom XML Mappings enable binding of arbitrary XML data structures and arbitrary XML schema definitions to the workbook. Once a DataBinding has been established, then various XML nodes can be mapped to table columns, ranges of cells, or even single cells (for non-repeating attributes and elements). Once an XML Mapping is fully defined, the application is able to import and export XML instance structures according to the schema definition.

While the original schema or XML definition may reside on disk or at some file location outside the workbook, a copy of the schema is stored in the workbook.

Every time an XML instance or schema is added to the workbook, a new map object is created which ties together the schemas and where the various elements are mapped in the workbook.

example:

<MapInfo SelectionNamespaces="">
  <Schema ID="Schema1">
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <xsd:element nillable="true" name="Root">
        <xsd:complexType>
          <xsd:sequence minOccurs="0">
            <xsd:element minOccurs="0" nillable="true" name="EmployeeInfo"
              form="unqualified">
              <xsd:complexType>
                <xsd:sequence minOccurs="0">
                  <xsd:element minOccurs="0" nillable="true" type="xsd:string" 
                    name="Name" form="unqualified"></xsd:element>
                  <xsd:element minOccurs="0" nillable="true" type="xsd:date" 
                    name="Date" form="unqualified"></xsd:element>
                  <xsd:element minOccurs="0" nillable="true" type="xsd:integer" 
                    name="Code" form="unqualified"></xsd:element>
                </xsd:sequence>
              </xsd:complexType></xsd:element>

              <xsd:element minOccurs="0" maxOccurs="unbounded" nillable="true" 
                name="ExpenseItem" form="unqualified">
                <xsd:complexType>
                  <xsd:sequence minOccurs="0">
                    <xsd:element minOccurs="0" nillable="true" type="xsd:date" 
                      name="Date" form="unqualified"></xsd:element>
                    <xsd:element minOccurs="0" nillable="true" type="xsd:string" 
                      name="Description" form="unqualified"></xsd:element>
                    <xsd:element minOccurs="0" nillable="true" type="xsd:double" 
                      name="Amount" form="unqualified"></xsd:element>
                  </xsd:sequence>
                </xsd:complexType></xsd:element>
          </xsd:sequence>
          <xsd:attribute name="Currency" form="unqualified" 
            type="xsd:string"></xsd:attribute>
          <xsd:attribute name="Approved" form="unqualified" 
            type="xsd:string"></xsd:attribute>
        </xsd:complexType>
      </xsd:element>
    </xsd:schema>
  </Schema>
  <Map ID="1" Name="Root_Map" RootElement="Root" SchemaID="Schema1"
    ShowImportExportValidationErrors="false" AutoFit="true" Append="false" 
    PreserveSortAFLayout="true" PreserveFormat="true">
    <DataBinding ConnectionID="1" FileBinding="true" DataBindingLoadMode="1"/>
  </Map>
</MapInfo>

For XML mapped into a SpreadsheetML Table there will also be additional information in the SpreadsheetML file which refers back to the XML <Map >and XPath of the element or attribute mapped. This information is stored in a <xmlColumnPr >element, under the <tableColumn >node.

example:

<table xmlns="…" id="1" name="Table1" displayName="Table1" ref="A1:H11"
  tableType="xml" totalsRowShown="0" connectionId="1">
  <tableColumns count="5">
    <tableColumn id="1" uniqueName="Name" name="Name">
      <xmlColumnPr mapId="1" xpath="/Root/EmployeeInfo/Name" 
        xmlDataType="string"/>
    </tableColumn>
    <tableColumn id="2" uniqueName="Date" name="Date">
      <xmlColumnPr mapId="1" xpath="/Root/EmployeeInfo/Date" 
        xmlDataType="date"/>
    </tableColumn>
    <tableColumn id="3" uniqueName="Code" name="Code">
      <xmlColumnPr mapId="1" xpath="/Root/EmployeeInfo/Code" 
        xmlDataType="integer"/>
    </tableColumn>
    <tableColumn id="4" uniqueName="Description" name="Description">
      <xmlColumnPr mapId="1" xpath="/Root/ExpenseItem/Description" 
        xmlDataType="string"/>
    </tableColumn>
    <tableColumn id="5" uniqueName="Amount" name="Amount">
      <xmlColumnPr mapId="1" xpath="/Root/ExpenseItem/Amount" 
        xmlDataType="double"/>
    </tableColumn>
  </tableColumns>
  <tableStyleInfo name="TableStyleMedium9" showFirstColumn="0" 
    showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>
</table>

For XML mapped into a single SpreadsheetML cell there will also be additional information in the TableSingleCells part which refers back to the XML <Map >and XPath of the element or attribute mapped. This information is stored in the <xmlPr >element under the <xmlCellPr >node.

example:

<singleXmlCells xmlns="…">
  <singleXmlCell id="2" name="Table2" displayName="Table2" r="D19" 
    connectionId="1">
    <xmlCellPr id="1" uniqueName="Currency">
      <xmlPr mapId="1" xpath="/Root/@Currency" xmlDataType="string"/>
    </xmlCellPr>
  </singleXmlCell>
  <singleXmlCell id="3" name="Table3" displayName="Table3" r="D20"
    connectionId="1">
    <xmlCellPr id="1" uniqueName="Approved">
      <xmlPr mapId="1" xpath="/Root/@Approved" xmlDataType="string"/>
    </xmlCellPr>
  </singleXmlCell
  <singleXmlCell id="4" name="Table4" displayName="Table4" r="D18" 
    connectionId="1">
    <xmlCellPr id="1" uniqueName="Name">
      <xmlPr mapId="1" xpath="/Root/EmployeeInfo/Name" xmlDataType="string"/>
    </xmlCellPr>
  </singleXmlCell>
</singleXmlCells>