If an XML document contains an XML schema, then when importing data from such a document, Excel can use the information from the schema linked to the XML document and store it in XML maps of the corresponding workbook into which the data from the original document is imported.
When the original document does not contain a schema, Excel attempts to create an XML map on its own — based on an analysis of the data contained in the source document.
Importing Data from an XML File Without an XML Schema
To import data from an XML file:
- Go to the File tab on the ribbon and choose Open.
- In the Open File dialog box, click the drop-down list for All Excel Files and choose XML Files (*.xml). After this, only files of this type will be displayed in the file list area.
- Select the required file in the file list area and click Open.
- In the Open XML dialog box that appears, select the XML Table option and click OK.
- If the file being opened does not contain a data schema, a warning will appear stating that Excel will create a schema based on the XML file.
- Click OK — the data from the XML file will be imported onto an Excel worksheet.
- To view the XML map created by Excel, go to the Developer tab on the ribbon and in the XML group click Source: in the task pane on the right side, the XML Source pane will open with the created XML map.
Creating an XML Map and Importing Data from an XML File
Suppose we already have an XML schema. To create an XML map in MS Excel, perform the following steps:
- Open an Excel file (or create a new one) into which you need to import data from the XML document.
- Go to the Developer tab on the ribbon and in the XML group click Source: in the task pane on the right side, the XML Source pane will open.
- Click the XML Maps button in the lower-right corner of the XML Source pane — the XML Maps dialog box will open.
- In the XML Maps dialog box, click Add to open the Select XML Source dialog box.
- Select the file to create the map from and click Open.
- If the selected source contains multiple root nodes, a dialog box for selecting a root node for the XML map will appear.
- Choose the appropriate option from the list and click OK. A line with the parameters of the added map will appear in the XML Maps dialog box.
- Highlight the name of the added schema in the XML Maps dialog box and click OK. The map will be added to the workbook and displayed in the XML Source pane in Excel’s working area.
To use the added XML map:
- Drag the required elements from the XML Source pane onto the worksheet to specify which fields to display on the worksheet.
- Go to the Developer tab on the ribbon, in the XML group click Import. Then, after specifying the required file, the data will be imported into the Excel worksheet.
