An End-to-End Scenario
This section deals with a more complex end-to-end scenario that puts together the schema creation capabilities of Visual Studio and the schema mapping capabilities of Excel. When you take a schema and map it into Excel using the XML Source task pane, you enable the exporting and importing of XML data in the spreadsheet. We are going to create an Excel spreadsheet that can be used to record a customer's book order. The spreadsheet will support the import and export of XML that conforms to our book order schema. The spreadsheet will look like Figure 21-8.
Figure 21-8. An Excel spreadsheet for processing a book order.
[View full size image]

Listing 21-5. XML File Generated from Book Order Spreadsheet
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns1:Order xmlns:ns1="http://tempuri.org/XMLSchema.xsd">
<ns1:CustomerName>Eric Carter</ns1:CustomerName>
<ns1:Date>2005-02-19</ns1:Date>
<ns1:Book>
<ns1:Title>Windows Forms Programming in C#</ns1:Title>
<ns1:ISBN>0-321-11620-8</ns1:ISBN>
<ns1:Publisher>Addison-Wesley</ns1:Publisher>
<ns1:Price>49.99</ns1:Price>
</ns1:Book>
<ns1:Book>
<ns1:Title>Effective C#</ns1:Title>
<ns1:ISBN>0-321-24566-0</ns1:ISBN>
<ns1:Publisher>Addison-Wesley</ns1:Publisher>
<ns1:Price>39.99</ns1:Price>
</ns1:Book>
<ns1:Book>
<ns1:Title>The C# Programming Language</ns1:Title>
<ns1:ISBN>0-321-15491-6</ns1:ISBN>
<ns1:Publisher>Addison-Wesley</ns1:Publisher>
<ns1:Price>29.99</ns1:Price>
</ns1:Book>
<ns1:Subtotal>119.97</ns1:Subtotal>
<ns1:Tax>10.7973</ns1:Tax>
<ns1:Total>130.7673</ns1:Total>
</ns1:Order>
Creating the Schema Using Visual Studio
To create this schema using Visual Studio, follow these steps:
1. | Start Visual Studio 2005. |
2. | Create a new XSD file by choosing File from the New menu of the File menu or by pressing Ctrl+N. |
3. | Choose XML Schema from the list of Visual Studio installed templates, as shown in Figure 21-4. Then click the Open button. |
4. | The Schema design view appears as shown in Figure 21-5. Drag an element object off of the toolbox onto the design surface. |
5. | Type Order and press the Enter key. |
6. | In the * row, type CustomerName and press the Enter key. |
7. | In the * row, type Date and press the Tab key, and then type date for the data type and press Enter. |
8. | In the * row, type Subtotal and press the Tab key, and then type float for the data type and press Enter. |
9. | In the * row, type Tax and press the Tab key, and then type float for the data type and press Enter. |
10. | In the * row, type Total and press the Tab key, and then type float for the data type and press Enter. |
11. | Now right-click the Order element box and choose New element from the Add menu. |
12. | Type Book and press the Enter key. |
13. | In the * row of the newly created Book element, type Title and press Enter. |
14. | In the * row of the newly created Book element, type ISBN and press Enter. |
15. | In the * row of the newly created Book element, type Publisher and press Enter. |
16. | In the * row of the newly created Book element, type Price and press the Tab key, and then type float for the data type and press Enter. |
17. | We now want to specify that multiple books can be included in an order. Click the Book row in the Order element box and show the Properties window by choosing Properties Window from the View menu. For the property maxOccurs, type unbounded . For the property minOccurs, type 1 . |
18. | Now save the schema using the Save As command from the File menu. In the Save File As dialog, drop down the Save as type combo box and pick XML Schema Files (*.xsd). For the filename, type BookOrder.xsd and save it to a convenient place such as the desktop. |
Figure 21-9 show what the final schema in Visual Studio should look like.
Figure 21-9. The book order schema in Visual Studio.

Listing 21-6. Book Order Schema XSD File
<?xml version="1.0" encoding="utf-8"?>
<xs:schema targetNamespace="http://tempuri.org/XMLSchema.xsd"
elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Order">
<xs:complexType>
<xs:sequence>
<xs:element name="CustomerName" type="xs:string" />
<xs:element name="Date" type="xs:date" />
<xs:element name="Book" maxOccurs="unbounded" minOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Title" type="xs:string" />
<xs:element name="ISBN" type="xs:string" />
<xs:element name="Publisher" type="xs:string" />
<xs:element name="Price" type="xs:float" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Subtotal" type="xs:float" />
<xs:element name="Tax" type="xs:float" />
<xs:element name="Total" type="xs:float" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Adding a Schema to the Excel Workbook
Now that we have created a schema, let's add it to an Excel workbook. Launch Excel and create a new empty workbook. Bring up the Excel XML Source task pane as described in the first section of this chapter. You should now see the XML Source task pane with no mappings as yet in the task pane. To add an XML map, click the XML Maps button in the XML Source task pane. Doing so brings up the dialog shown in Figure 21-10.
Figure 21-10. The XML Maps dialog.

Figure 21-11. The XML Source task pane with an XML map.

Mapping the Schema to the Excel Workbook
The XML Source task pane represents our book order schema in a tree view. The icon associated with Order indicates a required parent element. The icons associated with CustomerName, Date, Title, ISBN, Publisher, Price, Subtotal, Tax, and Total indicate required child elements. The icon associated with Book indicates a required repeating parent element. Excel also supports other schema constructs such as attributes and nonrequired elements and attributes. These constructs also have their own icons.Let's try a few different ways of mapping the schema into the workbook. The first approach we will take is to click the root ns1:Order node in the XML Source task pane and drag it to cell A1 in the workbook. Excel creates one list to contain all the data, as shown in Figure 21-12.
Figure 21-12. The list created when ns1:Order is dragged to cell A1.
[View full size image]

Figure 21-13. Mapped elements are bolded in the XML Source task pane.

Figure 21-14. Mapping cannot be exported because of denormalized data.

Figure 21-15. Result of importing the XML in Listing 21-5.
[View full size image]

Figure 21-16. Preparing the spreadsheet for mapping.

Finally, let's map the repeating elements to a list:
- Drag the Book element to cell B6. Because Book is a repeating element in a sequence with 1 to unbounded elements, this will create a list containing the elements Title, ISBN, Publisher, and Price as column headers in the list.
- The column headers created by Excel have the format ns1:Title rather than Title. You can edit these columns in the spreadsheet without breaking the XML mapping.
- Right-click the List object that was created and from the pop-up menu choose Totals Row from the List menu.
- Click the lower-right cell of the List object in the total row (Cell E8). A drop-down menu appears next to the cell. Pick Sum from the drop-down menu.
- Click cell C10. In the formula bar, type the formula =E8 . This causes the total created in the total row to be saved in the Subtotal element as well.
- Click cell C11. In the formula bar, type the formula =C10*.09 to calculate a 9 percent sales tax.
- Click cell C12. In the formula bar, type the formula =SUM(C10:C11) . This sums together the cost of the books plus the sales tax.
- Let's also do some formatting. Click the cells C10 through C12 and click the $ button to format these cells as currency. Also click the column header for the Price column in the list and format this column as currency because it is the column where book prices will go.