XQuery/Excel and XML

Excel spreadsheet to XML

Simple tabular spreadsheet data in Excel 2003 and above can be converted to XML using an additional plugin.

When downloaded, to install, go to Tools>Add-ins>>Browse to locate the downloaded file and then install. An additional item 'XML tools' should appear on the tool bar.

Converting the spreadsheet

Select the XML tools in the menu bar and 'Convert range to a list'. Guided by the tool, select the required cell range, set option for headings and choose 'Advanced' to enter your own name for the root of the document and for each row.
Save the spreadsheet as XML data with an xml extension
If you need to change the spreadsheet to add or remove columns, convert the XML back to a data list by going to Data>> List >> Convert to range. Make the changes required and then reconvert back to XML
If the spreadsheet contains dates or times, you may have problems in conversion. Set the date format to yyyy-mm-dd. When you convert to XML you will be informed that date formats are incompatible - click "use existing formatting"
As a last resort, export the sheet as tab-delimited text, then re-import, ensuring all data is imported as Text rather than General (which will recognize the dates and set a date type).

Creating an XML spreadsheet from scratch

To create a new spreadsheet, enter the headings first. Select all columns and set the formatting to text. If you don’t you will likely run into problems with dates and times where it is often difficult to wrest control away from Microsoft.
Enter dates in the XML format yyyy-mm-dd - you can set the date format to this
Enter the data in the table
Finally convert to XML as above

Export from XQuery to Spreadsheet

Data is commonly exported as CSV files but Excel will load tabular XML files which are straightforward to generate with XQuery.

For example, this table of Employee data displays in a browser as indented XML with folding symbols. Saving the file saves the XML data prior to transformation to this display format.

In Firefox, when the page is saved as XML, the filename is the script name with an additional XML suffix. The user will usually need to change this file name. The saved file can then be opened in Excel and after a couple of challenges, the data will be imported.

In IE6, the right-button menu provides an option to export directly to Excel.

The default file name can be set by the exporting script using the HTTP header Disposition....(need example)

Reading and Writing XML Spreadsheet 2003 Format

An example of reading and writing Excel files in the XML Spreadsheet 2003 format is located at https://github.com/lcahlander/codelist-compare

The Microsoft reference for XML Spreadsheet 2003 is located at XML Spreadsheet Reference