2)
*.xsd is a schema file, defining the data
‘layout’ and structure. If this is available it
will be can be used to ‘map’ the data, but can
also be used to inform another data consumer of
the xml ‘layout’ of the data.
3)
*.xsl is a ‘format’ file containing
formatting data for the rows and columns, such
as currency, centred, etc.
XML data can be imported into Excel, either
simply by File>Open… (as below) or by using this
menu option.n.
And selecting ‘Files of type’ as XML, as belowow
You will then be presented with three choices in
the ‘Open XML’ dialog, as below
If you select ‘XML list’, Excel will open it as
an Excel ‘List’, as below, working out the
‘mapping’ for itself and setting out the data as
below.
The ‘As a read-only workbook’ option, opens the
XML file as a read only dataset, with the
hierarchy ‘flattened’. If Excel again is not
directed to a schema file (*.xsd) it uses the
one in the xml file
We shall take the last option ‘Use the XML
source task pane’ option. This does not import
the data, but simply open the task pane with the
schema of the XML file or schema file (*.xsd) if
one is referred to (as below).
This gives great control over the mapping to the
worksheet of the data. For example, we can drag
elements to the worksheet, but it does not have
to be in a traditional table format. As shown
below. (This a little random, but demonstrates
the point)
Elements (nodes) from the XML map can only be
mapped once in an Excel Workbook, but they can
be mapped to different worksheets. Once you have
assembled these mappings, you can press the
refresh data button on the List Toolbar (as
below).
You then will end up with data as shown.
As with any table, the data ‘connection’ is
stored, so changing the source and refreshing,
will be reflected in the imported data in excel.
