Importing your XML into a spreadsheet

I'm going to try to get back on top of all the e-mails folks have been sending me. If you've e-mailed me over the past 6 months the odds are that I didn't reply yet, and I'm sorry about that. I'll start reading through the e-mails again and rather than just replying directly I'll actually try to post it online so that other folks can take advantage of it as well (I'll keep your name and e-mail confidential unless you specify otherwise). Since there is far too much to get through if I look back too far, I'm going to start with June and move forward. If you e-mailed me before that and didn't hear back, please submit it again (sorry).

The first question was one dealing with how to import custom XML data into Excel for editing (Excel allows you to import and export your own custom XML data):

I have an issue mapping an Excel spreadsheet to an XML schema. The problem is that I have to accomodate data for both English and French customers. So, I might have an XML schema such as:

<SoftwareTitles>
  <Entry>
    <Title>Microsoft Office 2007</Title> 
    <desc lang="EN">...</desc> 
    <desc lang="FR">...</desc> 
  </Entry>
  <Entry>
    <Title>Visual Studio .NET</Title>
    <desc lang="EN">...</desc>
    <desc lang="FR">...</desc> 
  </Entry>
</SoftwareTitles>

(where "desc" refers to the "description" of the software).

The plan is to have these "desc" tags correspond to two separate columns in my spreadsheet (description - English, and Description - French, respectively).

I guess my question for you is, is there an easy way to map this type of XML schema in Excel? Any help would be much appreciated.

Basically the goal is to get a table that looks like this:

While the schema used above is totally fine (and in fact it's probably the best way to represent this info), it doesn't work well with the XML mapping functionality. The mapping functionality tries to turn the data into a table, and it does this by looking at the element name. It treats each node as a map-able value, so attribute values are used more as data, and not as a way of classifying the date. So if you wanted to map this into a grid, the best approach would be to first transform it into a more "table like" structure.

The easiest way to get this into Excel would be to transform the data into something more like this:

<

SoftwareTitles>
<Entry>
<Title>Microsoft Office 2007</Title>
<descEN>...</descEN>
<descFR>...</descFR>
</Entry>
<Entry>
<Title>Visual Studio .NET</Title>
<descEN>...</descEN>
<descFR>...</descFR>
</Entry>
</SoftwareTitles>

It's a pretty simple XSLT to go back and forth between these two schemas, and this will then give you a really easy schema to map into the spreadsheet for editing. I think an XSLT like this would probably allow you to go either way:

<?

xml version="1.0" encoding="utf-8"?>

<

xsl:stylesheet version="1.0" xmlns:xsl="https://www.w3.org/1999/XSL/Transform">

<xsl:template match="*">
<xsl:copy>
<xsl:copy-of select="@*" />
<xsl:apply-templates />
</xsl:copy>
</xsl:template>

<xsl:template match="desc[@lang='EN']">
<descEN>
<xsl:apply-templates/>
</descEN>
</xsl:template>

<xsl:template match="desc[@lang='FR']">
<descFR>
<xsl:apply-templates/>
</descFR>
</xsl:template>

<xsl:template match="descEN">
<desc lang="EN">
<xsl:apply-templates/>
</desc>
</xsl:template>

<xsl:template match="descFR">
<desc lang="FR">
<xsl:apply-templates/>
</desc>
</xsl:template>

</

xsl:stylesheet>

-Brian