Creating an End-to-End XML Solution with Word 2003 and Excel 2003

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary: Frank Rice and Paul Cornell walk you through a simple end-to-end solution that demonstrates how XML has made it simple to move data from one Microsoft Office application to another. (19 printed pages)

Frank C. Rice, Microsoft Corporation

Paul Cornell, Microsoft Corporation

April 12, 2003

Download the odc_repurpose.exe sample file.

Contents

  • Overview

  • Tailspin Toys, Inc.

  • Create a Data Flow Chart

  • The TToys XML Data Input File

  • The TToys Purchase Order Schema

  • Creating the Data Map in Excel

  • Importing XML into Excel

  • Exporting XML from Excel

  • Transforming the XML in Word

  • Summary

Overview

There is a wealth of articles and books written on how to take advantage of eXtensible Markup Language (XML) and its associated family of technologies such as XML Schema Document (XSD) schemas and eXtensible Stylesheet Language Transformation (XSLT) files. Some of these articles do a great job of demonstrating the XML-related features of one application or another. Many of these articles, however, give you just a peek at one part of a complete solution. For example, how do you apply a schema or transform XML data from one format to another?

In this article, we'll examine a simple end-to-end solution that uses XSD schemas and an XSLT transformation file to move XML data from one Microsoft Office application to another. Specifically, for this article the goal is to move information from a Microsoft Office Excel 2003 worksheet format to a Microsoft Office Word 2003 document format. Along the way, we'll look at the data and structure of the XSD, XSL, and XML documents used as well as see some of the options available and choices made at each stage of the project.

Note

The information in this article is based on Microsoft Office 2003. As always, information can change. We have made every effort to focus on things that we think will hold true when the product ships.

Tailspin Toys, Inc.

To set the scenario for this article, Tailspin Toys (TToys) is a fictional manufacturer of expensive handmade balsa wood toy pogo sticks. TToys does most of its business with one balsa wood distributor because of special bulk pricing that it has been able to negotiate with them.

Although TToys uses Excel 2003 to create its purchase orders (PO), TToys' balsa wood distributor requires that purchase orders be submitted in the Word 2003 document format. This has led to a lot of wasted time on TToys' part as it has had to cut and paste data, both manually and programmatically, into Word documents just so the balsa wood manufacturer will accept their balsa wood purchase orders. TToys has argued on several occasions with the distributor trying to get them to also use Excel, but so far the distributor has not relented, preferring to use Word to process sales orders for the last several years. The distributor is willing to compromise with TToys and upgrade to Word 2003 but the distributor is still not convinced that it needs to fully switch over to Excel. Although TToys is frustrated about this arrangement, it thinks the move to XML technologies in Word 2003 and Excel 2003 will foster data exchange between the two parties, will reduce the ordering cycle, and will hopefully convince the distributor to make the move over to Excel.

TToys has decided to task its IT department to come up with a solution that automates as much of the process as possible within a relatively short timeframe. You, as part of the Application Development team, have been tasked to create the solution.

Create a Data Flow Chart

To get started, the first thing you do is create a flow diagram to get a better idea of how data moves thought the process, to understand the shape of the data at different points, to identify the pieces that exist, and what you need to create for the solution.

Figure 1. Data flow through the project

Once the flow chart has been created, you decide the logical next place to go is to examine the data that comes in (at the top of the flow chart).

The TToys XML Data Input File

The file TailspinPO_DataIn.xml contains the XML data that is used to populate a purchase order in TToys' Excel application:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<purchaseOrder xmlns="http://schemas.BalsaWoodDist.com/purchaseOrder">
<header>
   <OrderNumber>1234</OrderNumber>
   <OrderedBy>Paul Cornell</OrderedBy>
   <OrderedWith>Frank Rice</OrderedWith>
   <OrderDate>2003-01-15</OrderDate>
   <PaymentTerms>Net 30</PaymentTerms>
   <customerReferenceNumber>2345</customerReferenceNumber>
   <shipTo>
      <Name>Widgets, Inc.</Name>
      <Attn>Nancy Davolio</Attn>
      <Address>123 Main St.</Address>
      <City>Snoqualmie</City>
      <State>WA</State>
      <ZipCode>01999</ZipCode> 
      <Phone>555 555-1212</Phone>
      <Fax>555 555-1213</Fax>
      <eMail>paulcornell@widgetsinc.com</eMail>
      <shipVia>UPS</shipVia>
   </shipTo>
   <billTo>
      <sameAsShipTo>No</sameAsShipTo>
      <Name>Widgets, Inc.</Name>
      <Attn>Billing Dept.</Attn>
      <Address>123 Main St.</Address>
      <City>Snoqualmie</City>
      <State>WA</State>
      <ZipCode>01999</ZipCode>
      <Phone>555 555-1212</Phone>
      <Fax>555 555-1213</Fax>
      <eMail>billing@widgetsinc.com</eMail>
   </billTo>
</header>
<details>
   <item>
      <quantity>1</quantity>
      <description>Standard Balsa Wood Pack</description>
      <priceEach>19.99</priceEach>
      <taxEach>1.89</taxEach>
   </item>
   <item>
      <quantity>10</quantity>
      <description>Deluxe Balsa Wood Pack</description>
      <priceEach>29.99</priceEach>
      <taxEach>2.89</taxEach>
   </item>
<item>
<quantity>5</quantity>
<description>Premium Balsa Wood Pack</description>
<priceEach>39.99</priceEach>
<taxEach>3.89</taxEach>
</item>
</details>
</purchaseOrder>

As with all valid XML documents, the first part of the document consists of the namespace declarations and processing instructions, also known as the document prolog. Next, the document body, which contains the elements and data that make up the substance of the document. The first set of data and element tags (<OrderNumber>, <OrderedBy>, <OrderWith>, and so on) contain the order number, information on who created the order, the date of the order, and so forth. Then come the two sets of data encapsulated by the <shipTo> and <billTo> tags. Notice that each of these islands of data occur just once in the file, as you might expect. For example, typically there is one "ship to" address, one person who created the order, one billed entity, and so forth. This type of non-repeating data is common in many applications of this type for such uses as time sheets, invoices and, the purchase order detailed here. Continuing on, you come to the <details>/<item> tags. Unlike the previous data, these elements do repeat, also just as you would expect, for each item in an order. You will see more on repeating and non-repeating elements later in this article.

The TToys Purchase Order Schema

Now that you know what the data that goes into a TToys PO looks like, the next step is to ensure that the data in the TToys purchase order application adheres to a specified structure. You can do this with an XSD document since the purpose of XSD is to define the elements, attributes, and data types for the XML document. You also know that in Excel 2003, you can use an XSD file to create a structure called a data map. A data map is a logical collection of similar elements that are linked to the underlying XML data through an XPath link. To simplify that last sentence, think of an XSD file as a stencil that's used to paint signs. Data, in the form of paint, can only pass through the stencil where there are openings. Likewise, the only data that is allowed into the application is that which is defined in the XSD file.

Note

XPath provides a way to locate and retrieve XML elements and data. XPath treats XML data like a tree structure consisting of branches and nodes. More information on XPath can be found in the series of articles starting with Introduction to the XPath Tree Model. For additional information on XSD, see the article, XSD Schema Reference. And last, but not least, more information about XSLT can be found in the article, XSLT Developer's Guide.

Now let's look at the XSD Tailspin_purchase_order.xsd file:

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema 
targetNamespace="http://schemas.BalsaWoodDist.com/purchaseOrder" 
elementFormDefault="qualified" 
xmlns="http://schemas.BalsaWoodDist.com/purchaseOrder" 
xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:annotation>
        <xs:documentation>Defines a purchase order received by the 
        Balsa Wood Distributor organization with a "purchaseOrder" root 
        element.</xs:documentation>
    </xs:annotation>
    <xs:complexType name="itemType">
        <xs:sequence>
            <xs:element name="quantity" type="xs:integer" />
            <xs:element name="description" type="xs:string" />
            <xs:element name="priceEach" type="currencyType" />
            <xs:element name="taxEach" type="currencyType" />
        </xs:sequence>
        </xs:complexType>
            <xs:simpleType name="currencyType">
                <xs:restriction base="xs:decimal">
                    <xs:fractionDigits value="2" />
                </xs:restriction>
            </xs:simpleType>
        <xs:complexType name="billToType">
        <xs:sequence>
            <xs:element name="sameAsShipTo" type="YesNoType">
                <xs:annotation>
                    <xs:documentation>If Bill To is same as Ship To, 
                    enter "Yes" and leave the rest of these elements 
                    empty.</xs:documentation>
                </xs:annotation>
            </xs:element> 
            <xs:element name="Name" type="xs:string" />
            <xs:element name="Attn" type="xs:string" />
            <xs:element name="Address" type="xs:string" />
            <xs:element name="City" type="xs:string" />
            <xs:element name="State" type="stateType" />
            <xs:element name="ZipCode" type="xs:string" />
            <xs:element name="Phone" type="telephoneType" />
            <xs:element name="Fax" type="telephoneType" minOccurs="0" 
            maxOccurs="1" />
            <xs:element name="eMail" type="xs:string" minOccurs="0" 
            maxOccurs="1" />
        </xs:sequence>
    </xs:complexType>
         <xs:complexType name="shipToType">
             <xs:sequence>
                 <xs:element name="Name" type="xs:string" />
                 <xs:element name="Attn" type="xs:string" />
                 <xs:element name="Address" type="xs:string" />
                 <xs:element name="City" type="xs:string" />
                 <xs:element name="State" type="stateType" />
                 <xs:element name="ZipCode" type="xs:string" />
                 <xs:element name="Phone" type="telephoneType" />
                 <xs:element name="Fax" type="telephoneType" 
                 minOccurs="0" maxOccurs="1" />
                 <xs:element name="eMail" type="xs:string" 
                 minOccurs="0" maxOccurs="1" />
                 <xs:element name="shipVia" type="xs:string" />
             </xs:sequence>
         </xs:complexType>
         <xs:complexType name="headerType">
         <xs:annotation>
             <xs:documentation>Basic order information.</xs:documentation>
         </xs:annotation>
         <xs:sequence>
             <xs:element name="OrderNumber" type="xs:string" />
             <xs:element name="OrderedBy" type="xs:string">
                 <xs:annotation>
                     <xs:documentation>Who ordered from customer 
                     side?</xs:documentation>
                 </xs:annotation>
             </xs:element>
             <xs:element name="OrderedWith" type="xs:string">
                 <xs:annotation>
                     <xs:documentation>Who took the order from seller 
                     side?</xs:documentation>
                 </xs:annotation>
             </xs:element>
             <xs:element name="OrderDate" type="xs:date" />
             <xs:element name="PaymentTerms" type="xs:string">
                 <xs:annotation>
                     <xs:documentation>For example: "Net 30", 
                     "C.O.D."</xs:documentation>
                 </xs:annotation>
             </xs:element>
             <xs:element name="customerReferenceNumber" 
             type="xs:string">
                 <xs:annotation>
                     <xs:documentation>If not C.O.D., what is 
                     customer's order number?</xs:documentation>
                 </xs:annotation>
             </xs:element>
             <xs:element name="shipTo" type="shipToType" />
             <xs:element name="billTo" type="billToType" />
         </xs:sequence>
    </xs:complexType>
    <xs:complexType name="detailsType">
        <xs:sequence>
            <xs:element name="item" type="itemType" minOccurs="1" 
            maxOccurs="unbounded" />
        </xs:sequence>
    </xs:complexType>
    <xs:simpleType name="YesNoType">
        <xs:restriction base="xs:string">
            <xs:enumeration value="Yes" />
            <xs:enumeration value="No" />
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="stateType">
        <xs:restriction base="xs:string">
            <xs:enumeration value="AL" />
            <xs:enumeration value="AK" />
            <xs:enumeration value="AZ" />
            <xs:enumeration value="AR" />
            <xs:enumeration value="CA" />
            <xs:enumeration value="CO" />
            <xs:enumeration value="CT" />
            <xs:enumeration value="DE" />
            <xs:enumeration value="DC" />
            <xs:enumeration value="FL" />
            <xs:enumeration value="GA" />
            <xs:enumeration value="HI" />
            <xs:enumeration value="ID" />
            <xs:enumeration value="IL" />
            <xs:enumeration value="IN" />
            <xs:enumeration value="IA" />
            <xs:enumeration value="KS" />
            <xs:enumeration value="KY" />
            <xs:enumeration value="LA" />
            <xs:enumeration value="ME" />
            <xs:enumeration value="MD" />
            <xs:enumeration value="MA" />
            <xs:enumeration value="MI" />
            <xs:enumeration value="MN" />
            <xs:enumeration value="MS" />
            <xs:enumeration value="MO" />
            <xs:enumeration value="MT" />
            <xs:enumeration value="NE" />
            <xs:enumeration value="NV" />
            <xs:enumeration value="NH" />
            <xs:enumeration value="NJ" />
            <xs:enumeration value="NM" />
            <xs:enumeration value="NY" />
            <xs:enumeration value="NC" />
            <xs:enumeration value="ND" />
            <xs:enumeration value="OH" />
            <xs:enumeration value="OK" />
            <xs:enumeration value="OR" />
            <xs:enumeration value="PA" />
            <xs:enumeration value="RI" />
            <xs:enumeration value="SC" />
            <xs:enumeration value="SD" />
            <xs:enumeration value="TN" />
            <xs:enumeration value="TX" />
            <xs:enumeration value="UT" />
            <xs:enumeration value="VT" />
            <xs:enumeration value="VA" />
            <xs:enumeration value="WA" />
            <xs:enumeration value="WV" />
            <xs:enumeration value="WI" />
            <xs:enumeration value="WY" />
        </xs:restriction>
    </xs:simpleType>
    <xs:complexType name="purchaseOrderType">
        <xs:sequence>
            <xs:element name="header" type="headerType" />
            <xs:element name="details" type="detailsType" />
        </xs:sequence>
    </xs:complexType>
    <xs:simpleType name="zipCodeType">
        <xs:restriction base="xs:string">
            <xs:pattern value="[0-9]{5}(-[0-9]{4})?" />
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="telephoneType">
        <xs:annotation>
            <xs:documentation>Accepts United States phone number 
            patterns only.</xs:documentation>
        </xs:annotation>
    <xs:restriction base="xs:string">
        <xs:pattern value="([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9]
        [0-9][0-9][0-9]" />
    </xs:restriction>
</xs:simpleType>
<xs:element name="purchaseOrder" type="purchaseOrderType"></xs:element>
</xs:schema>

As you can see from this listing, the schema defines the structure of the XML, the data types of its elements, as well as providing other pertinent information. For example, looking at the <telephoneType> simple type at the bottom of the listing, the data type is a String value and the structure consists of a series of ten numbers between 0-9.

Note

When working with XSD documents, you will sometimes see elements designated as either simple or complex types. A simple type is just that; Strings, Integers, Char, and so forth. Complex types consist of a combination of simple types.

If you look below at the <item> element from the listing, you'll see that the minimum and maximum number of times the element can appear is defined. In this case, the element must appear at least once but can appear any number of times.

...
<xs:element name="item" type="itemType" minOccurs="1" 
MaxOccurs="unbounded" />
...

You can also use the schema document to define a list of enumerations as was done with the <stateType> in the listing.

Creating the Data Map in Excel

Excel 2003 also gives you choices when importing XML data into a worksheet. You can either use the schema referenced in the XML data file (assuming a reference exists), use the schema "preloaded" into the worksheet (again, if one exists), or have Excel "infer" a schema by examining the structure of the XML elements, and making a best guess.

Since we have a schema document, we'll go with the second option to preload the schema, create a data map, and then import the XML data.

You can create a data map with the following steps:

  1. Start Excel 2003 and then open a new workbook.

  2. On the Data menu, point to XML, and then click XML Source. The XML Source task pane is displayed.

  3. Click XML Maps.

  4. In the XML Maps dialog box, click Add.

  5. In the Select XML Source dialog box, locate the Tailspin_purchase_order.xsd file, and then click Open.

  6. An entry is displayed in the XML Map dialog box. Click OK.

  7. A tree view of the schema is displayed in the XML Source task pane.

    Figure 2. Tree view of the Tailspin_purchase_order schema

  8. Drag the OrderNumber element from the task pane onto cell B2.

  9. Next, drag Quantity onto cell B13. Notice that the element at cell B13 has a header while the element at cell B2 doesn't. As we'll see when we import data into the map, the element at B2 is non-repeating data while the element at B13 is repeating data. Repeating elements are automatically given headers while non-repeating elements aren't.

  10. Now, drag OrderedBy onto cell B3. Notice the Eight-ball icon that pops up. Clicking the drop-down arrow opens a menu that allows you to choose whether to add a heading and where. Also notice that the smart tag is clever enough to know that it can't add a header above the element and has grayed that option out. Click the option that places the heading to the left.

    Figure 3. Header placement option

  11. Drag the Description element onto cell C13. Notice how the element automatically becomes part of the adjoining column. This action is due to the Auto-merge feature of Excel.

  12. Click the arrow beside the Description element header. You are presented with options to sort and filter the data.

  13. Drag the remaining elements onto the cell locations indicated in the following figure. Note that you could also drag-and-drop a container folder such as the billTo or shipTo folders to the grid that would add all of the elements contained in the folder to the grid. However for this procedure, follow the assignments in the figure. Once you have created the data map, we'll look at populating the map.

    Figure 4. Data map in Excel

    Note

    Depending on whether smart tags are enabled in your Excel application, you may see colored triangles in some of the mapped cells in your worksheets. These represent smart tags that provide additional options for your application. In the figure above, these smart tags provide additional functionality for the <Address> tags.

Importing XML into Excel

Once you have created the data map, you are now ready to import the XML data into you map.

  1. Point to XML on the Data menu, and then click Import.

  2. In the Import XML dialog box, locate the TailspinPO_DataIn.xml file, and then click Import. Notice how the data populates the elements in the map.

    Figure 5. Mapped XML data from the TailspinPO_DataIn.xml file

Next, we'll look at moving the data over to the Balsa wood distributor's Word application.

Exporting XML from Excel

Now that you've created a map and populated it with XML data, you can then export the data and the structure of the map to an XML file. During export, Excel writes the data and elements for any valid mapped, non-empty cells in the spreadsheet. In addition, Excel validates every file exported against the schema that was used to create the map and provides a warning message when validation fails (assuming that list data validation errors option has been enabled).

Note

To enable data validation warnings, on the Tools menu, click Options. On the Error Checking tab, select List data validation error.

Note that the XML data file is created regardless of whether schema validation fails or not.

  1. Click anywhere in the XML map, point to XML on the Data menu, and then click Export.

  2. In the Export XML dialog box, type TailspinPO_DataOut, and then click Export. A dialog box is displayed notifying the user of the status of data validation against the schema and the XML file is created.

The following listing is the XML that was exported by Excel:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<?xml-stylesheet type="text/xsl" href="Transform2Balsa.xsl"?>
<ns1:purchaseOrder 
xmlns:ns1="http://schemas.BalsaWoodDist.com/purchaseOrder">
<ns1:header>
    <ns1:OrderNumber>1234</ns1:OrderNumber>
    <ns1:OrderedBy>Paul Cornell</ns1:OrderedBy>
    <ns1:OrderedWith>Frank Rice</ns1:OrderedWith>
    <ns1:OrderDate>2003-01-15</ns1:OrderDate>
    <ns1:shipTo>
        <ns1:Name>Widgets, Inc.</ns1:Name>
        <ns1:Attn>Nancy Davolio</ns1:Attn>
        <ns1:Address>123 Main St.</ns1:Address>
        <ns1:City>Snoqualmie</ns1:City>
        <ns1:State>WA</ns1:State>
        <ns1:ZipCode>01999</ns1:ZipCode>
        <ns1:Phone>555 555-1212</ns1:Phone>
        <ns1:Fax>555 555-1213</ns1:Fax>
    </ns1:shipTo>
    <ns1:billTo>
        <ns1:Name>Widgets, Inc.</ns1:Name>
        <ns1:Attn>Billing Dept.</ns1:Attn>
        <ns1:Address>123 Main St.</ns1:Address>
        <ns1:City>Snoqualmie</ns1:City>
        <ns1:State>WA</ns1:State>
        <ns1:ZipCode>01999</ns1:ZipCode>
        <ns1:Phone>555 555-1212</ns1:Phone>
        <ns1:Fax>555 555-1213</ns1:Fax>
    </ns1:billTo>
</ns1:header>
<ns1:details>
    <ns1:item>
        <ns1:quantity>1</ns1:quantity>
        <ns1:description>Standard Balsa Wood Pack</ns1:description>
        <ns1:priceEach>19.99</ns1:priceEach>
        <ns1:taxEach>1.89</ns1:taxEach>
    </ns1:item>
    <ns1:item>
        <ns1:quantity>10</ns1:quantity>
        <ns1:description>Deluxe Balsa Wood Pack</ns1:description>
        <ns1:priceEach>29.99</ns1:priceEach>
        <ns1:taxEach>2.89</ns1:taxEach>
    </ns1:item>
    <ns1:item>
        <ns1:quantity>5</ns1:quantity>
        <ns1:description>Premium Balsa Wood Pack</ns1:description>
        <ns1:priceEach>39.99</ns1:priceEach>
        <ns1:taxEach>3.89</ns1:taxEach>
    </ns1:item>
</ns1:details>
</ns1:purchaseOrder>

Transforming the XML in Word

The next activity on your agenda is to get the data that you exported into the Balsa Wood Distributor's Word application. To do that, you could have used an XSLT transformation file when you exported the data from Excel and then handed distributor the data on some mobile media such as a floppy disk or removable hard drive. Instead, the distributor's IT department says they are uncomfortable with accepting any data created externally, so you decide to show them how to use an XSL transformation file you created that transforms while importing data into Word:

  1. Start Word 2003.

  2. On the File menu, click Open. The Open dialog box is displayed.

  3. In the Files of type, click XML Files (*.xml) and navigate to the file TailspinPO_DataOut.xml created in the previous procedure.

  4. Click the drop-down arrow beside the Open button and click Open with Transform.

  5. In the Choose an XML Transform dialog box, navigate to the Transform2Balsa.xsl file, and then click OK.

The document opens in Word with the transformed XML displayed.

Figure 6. The transformed XML file in Word

Let's briefly look at the transform that converted TToys' purchase order XML workbook into a visually-rich Word XML document version of the balsa wood distributor's purchase order.

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns:ns1="http://schemas.BalsaWoodDist.com/purchaseOrder" 
version="1.0">
<xsl:output method="html"/>
<xsl:template match="/">
<HTML>
<HEAD>
<TITLE>Tailspin Toys</TITLE>
</HEAD>
<BODY>
<H1 ALIGN="CENTER"><font SIZE="+5"><font COLOR="RED">Tailspin 
Toys</font></font></H1>
<H2><i>Invoice</i></H2>
<hr/>
<TABLE ALIGN="LEFT" WIDTH="25%">
    <TR>
        <TD><b>Order Date:</b> <xsl:value-of 
        select="//ns1:header/ns1:OrderDate"/></TD>
    </TR>
    <TR>
        <TD><b>Order Number:</b> <xsl:value-of 
        select="//ns1:header/ns1:OrderNumber"/></TD>
    </TR>
    <TR>
        <TD><b>Ordered By:</b> <xsl:value-of 
        select="//ns1:header/ns1:OrderedBy"/></TD>
    </TR>
    <TR>
        <TD><b>Ordered With:</b> <xsl:value-of 
        select="//ns1:header/ns1:OrderedWith"/></TD>
    </TR>
</TABLE>
<TABLE ALIGN="RIGHT" WIDTH="25%">
    <TR>
        <TD><b>Customer: </b><xsl:value-of 
        select="//ns1:header/ns1:shipTo/ns1:Name"/></TD>
    </TR>
    <TR>
        <TD><b>C/O: </b><xsl:value-of 
        select="//ns1:header/ns1:shipTo/ns1:Attn"/></TD>
    </TR>
    <TR>
        <TD><b>Address: </b><xsl:value-of 
        select="//ns1:header/ns1:shipTo/ns1:Address"/></TD>
    </TR>
    <TR>
        <TD><xsl:value-of select="//ns1:header/ns1:shipTo/ns1:City"/>, 
<xsl:value-of select="//ns1:header/ns1:shipTo/ns1:State"/> 
<xsl:value-of select="//ns1:header/ns1:shipTo/ns1:ZipCode"/></TD>
    </TR>
</TABLE>
<h1>.</h1>
<h1>.</h1>
<TABLE WIDTH="100%">
    <TR>
        <TH>Quantity</TH>
        <TH>Description</TH>
        <TH>Price</TH>
    <TH>Tax</TH> 
    </TR>
    <xsl:for-each select="//ns1:details/ns1:item">
    <TR>
        <TD ALIGN="Center"><xsl:value-of select="ns1:quantity"/></TD>
        <TD><xsl:value-of select="ns1:description"/></TD>
        <TD ALIGN="Center"><xsl:value-of select="ns1:priceEach"/></TD>
        <TD ALIGN="Center"><xsl:value-of select="ns1:taxEach"/></TD>
    </TR>
    </xsl:for-each>
</TABLE>
</BODY>
</HTML>
</xsl:template>
</xsl:stylesheet>

The dominant feature of this document (as with most XSLT documents) is that it consists of a sequence of template rules. These rules describe how a particular element or structure should be processed. The rules can be in any order and don't even have to match the order of the input document or the output document. The primary precept for the declarative XSLT language is to say what output should be produced when a particular pattern occurs as opposed to procedural languages where you what tasks need to be performed and in what order.

The document itself consists of HTML, instructions, and XSLT constructs. For example, the <xsl:value-of> instruction inserts the value of the selected element at the current point in the output. Likewise, the <xsl:for-each> construct is used to iterate though sets of repeating elements.

Summary

In this article, we looked at an end-to-end solution involving XML and XML-related files. We saw how XSD schemas can be used to ensure that only specific elements are used in a document and that they are of the correct data type. We also used an XSLT file to transform the data from one format to another. XML formatted file can provide a lot of flexibility when working with moving data from one system to another. I would highly encourage you to take advantage of this flexibility in your own applications.

About the Authors

Frank Rice is a programmer writer for the Microsoft Office Developer Center where he adds his teaspoon of content to the flood of developer documents. In previous incarnations, he was an Access application developer and a test engineer.

Paul Cornell manages documentation projects for Microsoft Developer Division User Education. Currently, Paul manages the documentation for "Visual Studio Tools for Office." You can find out more about these tools at http://msdn.microsoft.com/vstudio/techinfo/officetools/. Paul lives in the mountains of Washington State with his wife and two daughters.