Opening an Existing XML Document | Foundation XML for Flash

Excel 2003 can open an existing XML document with the File Open command. If you are opening a SpreadsheetML document, Excel will display the contents immediately. Excel will ask how to open other types of XML documents. Figure 6-1 shows the Open XML dialog box that youll see.

Figure 6-1: Excel asks how an XML document should be opened.

Opening as a list

When you open an XML document in Excel, you can open it as a list, as a read-only workbook, or by using the XML Source pane. If you choose the first option, As an XML list , Excel will check to see whether the document includes an XML schema reference. If Excel cant find a reference, it will create a schema, based on the data structure within the XML document. Figure 6-2 shows the message that Excel displays.

Figure 6-2: Excel will create a schema if one cant be found.

Excel imports the data from the XML document and adds it to the worksheet. The data displays in cells , and the first row of the data block contains the tag names . The row heading also includes automatic filter drop-down boxes so that you can view specific data.

Figure 6-3 shows the file address.xml open in Excel. You can see that the drop-down lists in the headings give you options for sorting and filtering the data by the selected column.

Figure 6-3: An XML document opened in Excel

You might remember that in the address.xml file, the id is an attribute of the <contact> element. Excel displays this attribute in a column next to other elements from the document. Excel doesnt distinguish between the two types of data and adds the content in whichever order it appears in the XML document.

You can right-click anywhere within the data area to display the list- related functions. Figure 6-4 shows the List context menu.

Figure 6-4: Right-click List options within Excel.

In Figure 6-4, Im adding a total by choosing the Total Row option. Figure 6-5 shows the result.

Figure 6-5: Displaying totals

Excel adds a SUBTOTAL function to the right-hand side column as well as a new row for totals. This function summarizes the data in the list by using an Average , Count , Sum , or another aggregate expression. I cant modify the formula within the formula bar, but I can choose a different type of summary function from the drop-down list. If I want to add a function for another column, I can click in the corresponding Total cell and make my selection.

Its useful to know that you can add calculated columns to the list. Just make sure that you dont leave any blank columns between the data and the new calculations. You can also refer to fields within the list in other worksheets or workbooks.

When you add data from an external XML document to a list, Excel maintains the link to the document. If the external document changes, Excel wont update the list automatically. Youll need to do this yourself by right-clicking within the list and choosing XML Refresh XML Data . You can see how to do this in Figure 6-6. Make sure you save the external document before you refresh the content in Excel.

Figure 6-6: Refreshing the XML data in the Excel list

You can also edit the list in Excel and export the updated content. Youll see this a little later in the chapter.

Dealing with errors

If you try to import a document into Excel that is not well-formed, youll see an error message. Figure 6-7 shows the error that occurs when you try to import this type of document as a list. Youll also see error messages if you try to import a document that isnt well- formed as a read-only workbook or using the XML Source task pane.

Figure 6-7: Excel cannot open a document that isnt well formed.

You can click the Details button to get more information about the error. Excel will display the XML Error dialog box, as shown in Figure 6-8.

Figure 6-8: The XML Error dialog box

This error resulted from trying to open the file addressErrror.xml in Excel. You can find the file with your resources if youd like to try and open it yourself. The message tells me that theres a missing end tag.

Opening as a read-only workbook

The second choice in the Open XML dialog box is As a read-only workbook . Figure 6-9 shows this choice after using File Open with an XML document.

Figure 6-9: Opening an XML document as a read-only workbook

If you choose the second option, youll see an entirely different view of the XML data, as shown in Figure 6-10. Excel displays the structure of the XML document in the headings using XPath statements. We discussed XPath in Chapter 3.

Figure 6-10: A read-only view of XML data

Once you have the XML content in this format, you can process it as you would with any other Excel worksheet data. You can add formulas, change the structure of rows and columns, and add formatting to cells.

When you open a document as a read-only workbook, you cant export it back to the original XML document. The data itself isnt read-only, and you can still edit the content and save it to another file. You can see the two different views of XML data in the following exercise.

Exercise 1: Opening an existing XML document in Excel

In exercise 1, well open an existing XML document within Excel in two different ways: first as a list and then as a read-only workbook. Well use the file address.xml .

Open Excel 2003.
Choose File Open and select address.xml from your resource files.
When prompted, choose the As an XML list option.
Click OK when Excel shows the schema message.
Experiment with the drop-down filters in the heading row.
Right-click within the list and add totals.

Close the document without saving and reopen it, this time as a read-only workbook.
Compare the document structure with the previous list view.
Close the document without saving.

Opening with the XML Source pane

The last option you have when opening an XML file is Use the XML Source task pane , shown in Figure 6-11. When you select this option without an attached schema, Excel will show the same schema prompt that you saw earlier.

Figure 6-11: Using the XML Source task pane when opening an XML document

Excel creates an XML map , which is similar to a schema. The map displays in the XML Source pane, and shows the names of elements and attributes from the XML document. You can use the map to control the layout of the worksheet. You can choose which elements should appear in your list and the order of the columns. Figure 6-12 shows the document map for the address.xml file.

Figure 6-12: The XML map for the address.xml file

To set up your list, drag the elements from the map onto the worksheet. Youll notice that the process creates the structure for the data, rather than adding the content. Figure 6-13 shows the structure of a worksheet after adding elements from an XML map.

Figure 6-13: The list structure created from XML map elements

To add the data, youll need to import it from the XML document. Right-click within the list structure and select the XML Import option. Make sure you click within the blue box. You can see how to do this in Figure 6-14.

Figure 6-14: Importing XML data into the Excel list

Excel will prompt you for the location of the XML document containing the data that you want to import. After youve selected the document, Excel will add the data to your list, as shown in Figure 6-15.

Figure 6-15: The imported data within Excel

Once the data is imported, you can work with it as described earlier in this section. You can sort the data, add totals, and enter new calculated columns. If the external XML document changes, you can update the contents in Excel by choosing XML Refresh XML Data .

Its worth bearing in mind that you can import data from an external document into your list at any time. Each time you import data, however, it will replace any existing content in the list.

Opening a document with a schema

When you open an XML document that references a schema, Excel displays the same three options for opening the document: As an XML list , As a read-only workbook , and Use the XML Source task pane . The only difference is that Excel wont prompt you about creating a schema for the document.

If Excel finds a schema, it will translate the contents into an XML map. You will be able to see the map in the XML Source pane. If the pane isnt shown, choose View Task Pane . Make sure that you select XML Source from the drop-down list at the top of the Task Pane, as shown in Figure 6-16.

Figure 6-16: Dispaying the XML Source pane

Opening a document with an attached style sheet

If you open a document that references a style sheet, Excel will ask you whether to apply the transformation before opening. The Import XML dialog box is shown in Figure 6-17.

Figure 6-17: Opening an XML document that references a style sheet

You can apply the transformation or just open the XML document. If the transformation creates an XML document, Excel will prompt you about how to open the file and you can select from the same options: as a list, as a read-only workbook, or by using the XML Source pane.

Dealing with nonrepeating content

Excel recognizes two types of elements within an XML document: repeating and nonrepeating. Repeating elements occur more than once; nonrepeating elements appear only once. Repeating elements display in a list, and nonrepeating elements display in a single cell.

Excel can work with XML documents that contain a mixture of both types of elements. However, if you open a document with both types as an XML list, Excel will repeat any nonrepeating elements. For example, code that follows contains a nonrepeating <owner> element with repeating <contact> elements. You can see the content saved in the resource file addressNonRepeating.xml .

 <?xml version="1.0" encoding="UTF-8"?> <phoneBook>   <owner>Sas Jacobs</owner>   <contact id="1">     <name>Sas Jacobs</name>     <address>123 Some Street, Some City, Some Country</address>     <phone>123 456</phone>   </contact>   <contact id="2">     <name>John Smith</name>     <address>4 Another Street, Another City, Another Country</address>   <phone>456 789</phone>   </contact> </phoneBook>

Figure 6-18 shows the effect of opening this document as a list in Excel. Excel repeats the <owner> element in each row.

Figure 6-18: Opening a nonrepeating element in a list

A better solution is to select the Use the XML Source task pane option when you open this type of document. You can then position the nonrepeating elements separately from the repeating elements in the worksheet. When you import the data, the nonrepeating elements will appear only once. Figure 6-19 shows how you might structure this type of XML spreadsheet.

Figure 6-19: Positioning nonrepeating items outside of the list

Using the XML Source pane allows you to position the elements appropriately within the worksheet and is a better option in this case.

Dealing with mixed content

So far, weve worked with simple XML documents that dont contain mixed content. Youll recall that mixed content occurs when an element contains both text and other elements as shown here:

 <eName>Text<childName>Child text</childName> in an element</eName>

When you try to open a document containing mixed content, Excel cant easily place the content into a grid structure, so it must modify the content. This means that youll lose some of the data from the original document.

Ive shown the file addressMixedElements.xml next. The <address> element contains text as well as a <suburb> element, which means it is a mixed element.

 <?xml version="1.0" encoding="UTF-8"?> <phoneBook>   <contact id="1">     <name>Sas Jacobs</name>  <address>123 Some Street,   <suburb>Some City</suburb>   , Some Country</address>  <phone>123 456</phone>   </contact>   <contact id="2">     <name>John Smith</name>  <address>4 Another Street,   <suburb>Another City</suburb>   , Another Country</address>  <phone>456 789</phone>   </contact> </phoneBook>

Figure 6-20 shows what happens when I open this document as a list in Excel.

Figure 6-20: Opening an XML document with mixed content in Excel

The document map shows that the <address> element contains a <suburb> element. However, the list doesnt display the text within the <address> element. If I exported this list in XML format, Id lose the text from the original <address> element. Youll learn more about exporting content from Excel a little later in the chapter.

The simple conclusion is that you cant work with mixed content in Excel. If you do, youll lose some of the content when you export your data. For the purposes of Flash, its much better to avoid mixed elements if youre going to use Excel to manage your content.

Dealing with complicated structures in a list

Another limitation of Excel is that it cant deal with complicated structures in XML lists. In the XML document shown here, Ive stored <contact> elements in two different parents: the < friends > and <work> elements. You can see the content saved in the resource file addressFixed.xml .

 <?xml version="1.0" encoding="UTF-8"?> <phoneBook>   <friends>     <contact id="1">       <name>Sas Jacobs</name>       <address>123 Some Street, Some City, Some Country</address>       <phone>123 456</phone>     </contact>     <contact id="2">       <name>John Smith</name>       <address>4 Another Street, Another City, Country</address>       <phone>456 789</phone>     </contact>   </friends>   <work>     <contact id="3">       <name>Jo Bloggs</name>       <address>7 Different Street, Different City, UK</address>       <phone>789 123</phone>       </contact>       </work> </phoneBook>

Excel gets a little confused when I try to open this document as a list. Figure 6-21 shows how Excel interprets the data.

Figure 6-21: Excel has difficulty opening an XML document with a complex structure.

The <friends> and <work> elements from the XML map appear side by side. It would have made more sense to store them beneath each other with another column describing their category.

To achieve this outcome, I have to change the structure of the XML document. Ive added the category as an attribute, as you can see this in the following XML document. You can see the content saved in the resource file addressFixedAttributes.xml .

 <?xml version="1.0" encoding="UTF-8"?> <phoneBook>   <contact id="1" category="friends">     <name>Sas Jacobs</name>     <address>123 Some Street, Some City, Some Country</address>     <phone>123 456</phone>   </contact>   <contact id="2" category="friends">     <name>John Smith</name>     <address>4 Another Street, Another City, Country</address>     <phone>456 789</phone>   </contact>   <contact id="3" category="work">     <name>Jo Bloggs</name>     <address>7 Different Street, Different City, UK</address>     <phone>789 123</phone>   </contact> </phoneBook>

Figure 6-22 shows how Excel interprets this content.

Figure 6-22: The list arising from the amended XML document structure

You can see that I had to simplify the XML document structure so that Excel could display the content in a list.The element structure is an important consideration if youre going to use Excel 2003 to manage the content for your Flash application.

In the next section, youll complete an exercise that shows you how to open an XML document using the XML Source pane.

Exercise 2: Opening an existing XML document using the XML Source pane

In this exercise, well open the XML document addressNonRepeating.xml in Excel using the XML Source pane. The document contains both repeating and nonrepeating elements, and well use the XML Source pane to position the content appropriately. This approach provides more control over the structure of the worksheet compared with opening the XML document as a list.

Open Excel 2003 if necessary.
Choose File Open and select addressNonRepeating.xml from your resource files.
When prompted, choose the Use the XML Source task pane option and click OK . Click OK again so that Excel can create a schema from the document structure.
Enter the text Owner in cell A1. From the XML map, drag the owner element to cell B1.
Drag the id element to cell A3, the name element to cell B3, the address element to cell C3, and the phone element to cell D3, as shown in Figure 6-23. Notice that Excel automatically adds the column headings.

Figure 6-23: The completed Excel file from exercise 2
Right-click within the list structure and choose XML Import . Navigate to the file addressNonRepeating.xml and click Import . You may need to click OK as the list inserts rows within the worksheet.
Save the file as address.xls . You can see the completed file with your resources. Figure 6-23 shows the completed Excel spreadsheet.

Using the XML Source pane provides you with much more control over the placement and structure of XML elements within your worksheet.

The process for opening an existing XML document within Excel 2003 is as follows:

Choose File Open and locate the XML document.
Select whether to open the document as an XML list, as a read-only workbook, or by using the XML Source task pane.
If you open an XML document that references a style sheet, choose whether to apply the transformation before opening the document.

In the next section, Ill show you how to create an XML document from an existing Excel spreadsheet. Well generate a SpreadsheetML document and then create more structured content with a document map.