You could also look into using an xsd. It's a pretty simple document that
allows you to define the structure of your xml file and assign excel
columns to fields. That way, when you save excel as xml, you can have the
proper hierarchy in your xml. There are some quirks, but overall it's
pretty effective.

Amelia Mowry
Metadata & Discovery Services Librarian
Wayne State University


On Friday, June 13, 2014, Dana Pearson <[log in to unmask]> wrote:

> I don't use Excel but a client did who wanted to use XSL I had created ONIX
> to MARC to transform    bibliographic metadata in Excel to XML.  The built
> in Excel XML converter was not very helpful since empty cells were skipped
> so that it was impossible to use that result.
>
> There is an add on that allow you to map your data to XML elements by
> creating a schema which is pretty cool.
>
> http://bit.ly/1jpwtqM
>
> This might be helpful.
>
> regards,
> dana
>
>
> On Fri, Jun 13, 2014 at 6:53 PM, Terry Brady <[log in to unmask]> wrote:
>
> > The current version of Excel offers a save as XML option.
> >
> > It will produce something like this.  There is other wrapping metadata,
> but
> > the table is pretty easy to parse.
> >
> >   <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="7"
> > x:FullColumns="1"
> >    x:FullRows="1" ss:DefaultRowHeight="15">
> >    <Row>
> >     <Cell ss:StyleID="s62"><Data ss:Type="String">row 1</Data></Cell>
> >     <Cell><Data ss:Type="String">question 1</Data></Cell>
> >     <Cell><Data ss:Type="String">answer 1</Data></Cell>
> >    </Row>
> >    <Row>
> >     <Cell ss:StyleID="s62"><Data ss:Type="String">row 2</Data></Cell>
> >     <Cell ss:Index="3"><Data ss:Type="String">answer 2</Data></Cell>
> >    </Row>
> >    <Row>
> >     <Cell ss:StyleID="s62"><Data ss:Type="String">row 3</Data></Cell>
> >     <Cell ss:Index="3"><Data ss:Type="String">answer 3</Data></Cell>
> >    </Row>
> >    <Row>
> >     <Cell ss:StyleID="s62"><Data ss:Type="String">row 4</Data></Cell>
> >     <Cell><Data ss:Type="String">question 2</Data></Cell>
> >     <Cell><Data ss:Type="String">answer 1</Data></Cell>
> >    </Row>
> >    <Row>
> >     <Cell ss:StyleID="s62"><Data ss:Type="String">row 5 </Data></Cell>
> >     <Cell ss:Index="3"><Data ss:Type="String">answer 2</Data></Cell>
> >    </Row>
> >    <Row>
> >     <Cell ss:StyleID="s62"><Data ss:Type="String">row 6</Data></Cell>
> >     <Cell><Data ss:Type="String">quest </Data></Cell>
> >     <Cell><Data ss:Type="String">answer 3</Data></Cell>
> >    </Row>
> >    <Row>
> >     <Cell ss:StyleID="s62"/>
> >    </Row>
> >   </Table>
> >
> >
> > On Fri, Jun 13, 2014 at 2:28 PM, Ryan Engel <[log in to unmask]> wrote:
> >
> > > Hello -
> > >
> > > I have an Excel spreadsheet that, for the purposes of an easy import
> into
> > > a Drupal site, I'd like to convert to XML.  I know people more
> > > knowledgeable than I could code up something in Python or Perl to
> > convert a
> > > CSV version of the data to XML (and I have a colleague who offered to
> do
> > > just that for me), but I am looking for recommendations for something
> > more
> > > immediately accessible.
> > >
> > > Here's an idea of how the spreadsheet is structured:
> > >
> > > Row1    Question1    Q1Answer1
> > > Row2                        Q1Answer2
> > > Row3                        Q1Answer3
> > > Row4    Question2    Q2Answer1
> > > Row5                        Q2Answer2
> > > Row6    Question3    Q3Answer1
> > > etc.
> > >
> > > How do other people approach this?  Import the data to an SQL database,
> > > write some clever queries, and then export that to XML?  Work some
> > wizardry
> > > in GoogleRefine/OpenRefine?  Are scripting languages really the best
> all
> > > around solution?  Excel's built in XML mapping function wasn't able to
> > > process the one-to-many relationship of questions to answers, though
> > maybe
> > > I just don't know how to build the mapping structure correctly.<--
> Dana Pearson
> dbpearsonmlis.com
> Metadata and Bibliographic Services for Libraries
>