Programming Excel with VBA and .NET

Limitations of XML Maps

The preceding tutorial demonstrates a subtle limitation of XML maps—optional nodes, such as Street2 in SimpleOrder.xml, are sometimes not imported. This occurs because Excel generates the schema from the first instance of each node it encounters.

Figure 15-10. Mapped XML with summary/detail lists

To correct this, add an empty Street2 node to the first address node as shown next and open the XML as a new workbook:

<Address>
    <Name>Joe Magnus</Name>
    <Street1>1234 Made Up Place</Street1>
    <Street2 />
    <City>Somewhere</City>
    <State>FL</State>
    <Zip>33955</Zip>
</Address>

You can’t update an existing XML map; you can only create new ones and delete existing ones from within Excel. This means that lists created from XML maps must be re-created any time the source XML schema changes.

Since XML maps are row-based, you can’t conditionally omit optional nodes as you can with XSLT. For example, the sample transformation OrderToExcel.xslt omits the optional Street2 node if it is empty, using the following xsl:if element:

<xsl:if test="./Address/Street2 != ''">
    <xsl:element name="Row">
        <xsl:element name="Cell" />
        <xsl:element name="Cell">
            <xsl:element name="Data">
                <xsl:attribute name="ss:Type">String</xsl:attribute>
                   <xsl:value-of select="Street2" />
              </xsl:element>
        </xsl:element>
    </xsl:element>
</xsl:if>

You can’t do that type of conditional processing with XML maps.

Another limitation ...