Create the XML Data File
In this task, you create an XML data file in a text editor that is used later in this exercise.
To create the XML data file
- Start the text editor such as Notepad.
- Paste or type the following XML into the editor.
<?xml version='1.0'?>
<BookInfo>
<Book>
<ISBN>989-0-487-04641-2</ISBN>
<Title>My World</Title>
<Author>Nancy Davolio</Author>
<Quantity>121</Quantity>
</Book>
<Book>
<ISBN>981-0-776-05541-0</ISBN>
<Title>Get Connected</Title>
<Author>Janet Leverling</Author>
<Quantity>435</Quantity>
</Book>
<Book>
<ISBN>999-1-543-02345-2</ISBN>
<Title>Honesty</Title>
<Author>Robert Fuller</Author>
<Quantity>315</Quantity>
</Book>
</BookInfo> - Save the document as C:\BookData.xml.
- Close the text editor.
Import the Schema and XML Data into Excel
In this task, you import the schema created in the previous section into Excel to create an XML map and then import an XML data file.To create the Xml Map and then import the XML data file into Excel
- On the Developer tab, in the XML group, click Source to open the XML Source task pane.
- On the task pane, click Xml Maps.
- In the Xml Maps dialog box, click Add, navigate to the file BookInfo.xsd, click Open, and then click OK.
- In the XML Source task pane, drag the BookInfo node to cell A1. This action creates a table in the worksheet as shown in Figure 1. You can format the table as necessary.
- Next, import the XML data file. On the Developer tab, click Import.
- In the Import XML dialog box, navigate to the XML data file and then click Import. The data is mapped into the formatted table as shown in Figure 2.