Method-1: Import XML Data
To import XML data:
1. Open an existing workbook that has an XML map or create a new workbook.
2. If necessary, customize the Ribbon to display the Developer tab.
3. If necessary, map the elements of an XML schema to the workbook.
4. On the Developer tab, in the XML group, click Import.
5. In the Import XML dialog box, navigate to and select the file to be imported.
6. Click Import.
Method-2: Import XML Data as External Data
To import XML data as an external data:
1. Open a new Excel workbook.
2. On the Data tab, in the Get External Data group, click From Other Sources and select From XML Data Import.
3. In the Select Data Source dialog box, navigate to the desired folder, select the desired XML file to be imported, and click Open.
4. In the Microsoft Office Excel information box, click OK to create a schema based on the XML data source.
5. In the Import Data dialog box, specify the location for the import.
a. Choose XML Table In Existing Worksheet and specify the location in the text box to place the import into an XML table in the active worksheet.
b. Choose Existing Worksheet and specify the location in the text box to place the import as flattened XML data.
Tips: – A flattened XML table consists of a two-dimensional table that contains columns and rows. The column headings will be the XML tags and the rows below the column headings will contain the appropriate data. In cases where XML data is flattened, Excel will not infer a schema, and the XML map will not be available in the Source task pane.
c. Or, choose New Worksheet to place the import in a new worksheet and place the data in the upper-left corner of the worksheet.
6. If necessary, in the Import Data dialog box, click Properties and set the import properties.
7. Click OK.
Method-3: Export XML Data
To export XML data:
1. Open the file that contains the data that you want to export.
2. On the Developer tab, in the XML group, click Export.
3. In the Export XML dialog box, navigate to the folder where you want to store the exported XML file.
4. In the File Name text box, type a name for the file to be exported.
5. Click Export.
Managing XML Workbooks
The data in an XML-based workbook can be edited, deleted cut, pasted, and sorted just like any other data in an Excel sheet.
When you want to import data into an XML-mapped worksheet, add new data to that worksheet, and then export all of the data, your XML map must appear as a contiguous list. This means that your mapped XML elements must appear as column headings in adjacent columns, and they cannot be separated from one another. To force XML elements to remain contiguous across adjacent cells, you can map them from left to right, across the column headings.