Creating XML Schemas

---

At the heart of XML-based data interchange is the schema, which is a document that defines the structure of a set of XML files. You can create a custom schema in an XML editor, write the XML code yourself in a simple text editor such as Notepad, or you can have Excel do it for you when you save a workbook as an XML document. The following listing, which was created using Notepad, shows the schema used in the examples throughout this chapter:

<?xml version="1.0" encoding="utf-8" ?>
<schema xmlns="http://www.w3.org/2001/XMLSchema">
 <element name="Root">
  <complexType>
   <sequence>
      <element name="Supplier" maxOccurs="unbounded">
          <complexType>
            <sequence>
              <element name="SupplierID" type="positiveInteger"/>
              <element name="CompanyName" type="string"/>
              <element name="ContactName" type="string"/>
              <element name="ContactTitle" type="string"/>
              <element name="MailingAddress">
                <complexType>
                  <sequence>
                    <element name="Address" type="string"/>
                    <element name="City" type="string"/>
                    <element name="Region" type="string"/>
                    <element name="PostalCode" type="string"/>
                    <element name="Country" type="string"/>
                  </sequence>
                </complexType>
              </element>
             <element name="Phone" type="string"/>
             <element name="Fax" type="string"/>
            </sequence>
          </complexType>
       </element>
      </sequence>
   </complexType>
 </element>
</schema> 

Don't let the long set of closing tags at the bottom of the list fool you-the structure of the data objects depicted in the schema isn't that complex. Figure 26-4 depicts the schema's structure graphically instead of textually.

Figure 26-4: XML schemas are detailed, but not overly complicated.

Note 

You can display the XML Source task pane, which displays any schemas assigned to a workbook, by clicking Data, XML, XML Source.

The first two lines of the listing are housekeeping details that tell the XML interpreter which version of XML is used, which character encoding standard the data conforms to, and a reference to the schema standards document online at the World Wide Web Consortium's Web site.

<?xml version="1.0" encoding="utf-8" ?>
<schema xmlns="http://www.w3.org/2001/XMLSchema">

Inside Out
Watch Your Language

The utf-8 encoding scheme is used to represent relatively simple character sets such as that used in American English. American English employs few accents (and then only on borrowed words), which means computers can represent the entire character set (including numbers, capital letters, punctuation, and certain special characters such as spaces, tabs, and so on) using only eight bits of data. For other languages that have differing basic characters or use accents, you need to use another eight bits to represent all the possible characters. The encoding scheme for most of those languages is utf-16.

The next several lines of code identify the basic element in the schema (often named Root) and indicate what structure the data will take.

<element name="Root">
  <complexType>
   <sequence>
      <element name="Supplier" maxOccurs="unbounded">
       …
      </element>
   </sequence>
  </complexType>
</element>

The <complexType> tag in this XML schema code indicates that the Root element is complex, meaning it's allowed to contain multiple subelements. If you refer back to Figure 26-4, you can see that the Root element does indeed have subelements, the first of which is Supplier. The <sequence> tag indicates that the subelements will always appear in the same order, which is to be expected when you generate an XML data file from a spreadsheet or database table.

There is something new in the Supplier element declaration: the maxOccurs="unbounded' parameter setting. This parameter tells the XML interpreter that there can be more than one Supplier element in any XML data file that follows this schema. If you leave this parameter out, the interpreter knows to expect a single occurrence of the element, which is handy if you want to add information regarding the document author, the department, or the project code at the beginning of the data file.

You should think of XML elements as being similar to objects with properties. For example, the following abbreviated code sample shows some of the properties associated with a supplier:

              <element name="SupplierID" type="positiveInteger"/>
              <element name="MailingAddress">
                <complexType>
                  <sequence>
                    <element name="Address" type="string"/>
                    <element name="City" type="string"/>
                    <element name="Region" type="string"/>
                    <element name="PostalCode" type="string"/>
                    <element name="Country" type="string"/>
                  </sequence>
                </complexType>
              </element>

You should remember that any simple element (that is, an element with a single value associated with it) is enclosed within a single tag that ends with />. In addition to the name of the element, the element tag also contains a type parameter, which indicates the data type the element must contain. The two types used in the sample are positiveInteger and string, which are self-explanatory.

To save your schema as an XML schema file, follow these steps in Notepad:

1. Click File, Save.

2. Click the Save As Type down arrow and click All Files.

3. Type the name of the file in the File Name box. For example, if the name of the file you want to save as an XML schema file is MySuppliers, you would type MySuppliers.xsd.

   For more information on XML data types, visit the World Wide Web Consortium's reference page at http://www.w3.org/ and type xml data types into the search box.

---