Importing XML data by using a

This example uses the worksheet shown in Figure 4-4. This worksheet uses data in column B to generate a loan amortization schedule. Assume that a back-end system generates XML files and that each file contains data for a customer. An example of such a file is shown below:

<?xml version="1.0"?> <Customer>

<Name>Joe Smith</Name> <AcctNo>32374-94</AcctNo> <LoanAmt>$325,983</LoanAmt> <IntRate>6.25%</IntRate> <Term>30</Term> </Customer>

Figure 4-4: This worksheet uses imported XML data.

This file has five data elements: Name, AcctNo, LoanAmt, IntRate, and Term. Two of the fields (Prepared and Number of Pmt Periods) are calculated with formulas and are not considered data elements.

The trick here is to be able to import files, such as this, and have the data sent to the appropriate cells in the worksheet.

The first step is to add a Map to the workbook. Make sure that XML Source is displayed in the task pane (choose Data ^ XML ^ XML Source).

To add the Map, follow these steps:

1. Click the XML Maps button at the bottom of the task pane. The XML Maps dialog box appears.

2. Click Add to display the XML Source dialog box.

3. Select one of the customer XML files. The exact file doesn't matter. This will be used only to infer the schema.

4. Click OK to dismiss the XML Maps dialog box. The task pane displays the data elements from the file (as shown in Figure 4-5).

Figure 4-5: The task pane shows the XML data elements.

The next step is to map the data elements to the appropriate worksheet cells.

1. In the task pane, click the Name element and drag it to cell B3.

2. Drag the AcctNo element to cell B5.

3. Drag the LoanAmt element to cell B6.

4. Drag the IntRate element to cell B7.

5. Drag the Term element to cell B8.

Finally, you can import an XML file. Choose Data ^ XML ^ Import and then select a customer XML file. You'll find that the data is fed into the appropriate cells. To calculate another amortization schedule, just import another XML file.

Was this article helpful?

0 0

Post a comment