Consuming XML Data Directly

Once you have a well-formed XML document, you can start using the data it contains. One of the simplest ways to use an XML document is to open it directly from Excel. To help demonstrate this, open EmployeeSales.xml, shown here. This XML document contains data revolving around the invoices filed by the employee in an organization:

<?xml version="1.0"?> <EmployeeSales> <Employee>

<Empid>2312</Empid> <FirstName>Mike</FirstName> <LastName>Alexander</LastName> <InvoiceNumber>100</InvoiceNumber> <InvoiceAmount>23 00</InvoiceAmount> </Employee>

<Employee>

<Empid>24601</Empid> <FirstName>Stephen</FirstName> <LastName>Bullen</LastName> <InvoiceNumber>200</InvoiceNumber> <InvoiceAmount>3211</InvoiceAmount> </Employee>

</EmployeeSales>

You can find the sample files used in the various walkthroughs for this chapter at www.wrox.com, in this chapter's XMLSampleFiles folder.

Start Excel, select File O Open, and then open the XML document titled EmployeeSales.xml. You will immediately see the Open XML dialog box, shown here in Figure 12-1.

Figure 12-1

Select As an XML table and click the OK button. This will activate the dialog box, shown here in Figure 12-2, where Excel tells you that it could not find a schema for your XML document so it will create one for you.

Microsoft Office Excel

si

-Z . The specified XML source does not refer to a

schema. Excel will create a

* J schema based on the XML source data.

1 1 In the future, do not show this message.

Figure 12-2

In XML terms, the word schema refers to an XML Schema Description (XSD). An XSD is a file typically associated with an XML file in order to provide rules for the document. XSD files dictate the layout and sequencing for the data in an XML document, as well as the data types and default values for each element and attribute.

The topic of XSD is a subject that is worthy of its own book. This chapter is, alas, focused on areas outside the scope of XSD, so XSD is not covered in detail here. Although there are plenty of books that cover this subject, a visit to www.w3shcools. com/schema will get you off on the right foot. This site will give you a solid (and free) start on learning more about XSD.

Because the EmployeeSales.xml file does not have an associated schema file (XSD), Excel will infer a schema from your XML document. This means Excel essentially creates an internal schema that will dictate the rules for the document.

Once you click the OK button, Excel will create a new workbook and populate it with your XML data table. At this point, you should see the same table shown in Figure 12-3.

1 fiWfflff^i

2 2312 Mike

3 2J-601 Stephen

FiistNaine 55 LsstfJairiH; Q InvoiceNimtber QllnvoiceAnioufit

Alexander Bullen

2300 3211

Figure 12-3

Excel automatically creates an XML list, mapping a range of cells to the elements in the source XML document. These cells are linked back to the XML document and can be refreshed with the latest data by right-clicking inside the XML list and selecting XML O Refresh XML Data.

You can also refresh using the Refresh Data button, found in the XML Group under the Developer tab of the Ribbon.

To test this out, save your Excel file and close it. Now edit the EmployeeSales.xml file to add a new employee record, as demonstrated here:

<?xml version="1.0"?> <EmployeeSales> <Employee>

<Empid>2312</Empid> <FirstName>Mike</FirstName> <LastName>Alexander</LastName> <InvoiceNumber>100</InvoiceNumber> <InvoiceAmount>23 00</InvoiceAmount> </Employee>

<Employee>

<Empid>24601</Empid> <FirstName>Stephen</FirstName> <LastName>Bullen</LastName> <InvoiceNumber>200</InvoiceNumber> <InvoiceAmount>3211</InvoiceAmount> </Employee>

<Employee>

<Empid>1132</Empid> <FirstName>Rob</FirstName> <LastName>Bovey</LastName> <InvoiceNumber>3 00</InvoiceNumber> <InvoiceAmount>4211</InvoiceAmount> </Employee>

</EmployeeSales>

Once you save your edits in the XML document, return to the Excel file and refresh the XML map. As you can see in Figure 12-4, the newly added employee will be included in the mapped range.

Figure 12-4

Figure 12-4

Take a moment to think about how this functionality could be useful to you as an Excel programmer. Once your XML data is mapped to a range of cells, it can be used just as other data in Excel. For instance, you can use XML data as variables in formulas, as feeds for charts, and as the source data for pivot tables. Imagine building an Excel-based reporting system where all data that feeds your pivot tables and charts links back to XML files on a network server. You can imagine that those XML files could be updated on a nightly basis, while your client's workbooks could be designed to automatically refresh on open. Later in this chapter, you will discover how you can leverage VBA to create XML documents and automate many of the actions you have taken here thus far.

Keep in mind that when Excel infers a schema for you, the source XML document is automatically rendered read-only. In this context, the term read-only means that you cannot make changes to the source XML document via the XML map created in Excel. This prevents your users from editing or adding data in the source XML document.

Was this article helpful?

0 0

Post a comment