XML and Excel

Pages in this article

1. What is XML
2. Characteristics of XML
3. Structure of XML
4. XML Schemas
5. XML in Excel
6. XML Validation
7. Conclusion

Structure of an XML file

The XML code shown in figure 1 (shown again below) is relatively easy to read and understand (file test_en.xml in the download).

The first (blue) line indicates we're dealing with an XML file. This line should appear in each XML file. The file contains both information and pairs of opening and closing tags around the information. A set of opening and closing tags and the information in-between is called an element. The text below shows a complete element:

<name>Joe Jackson</name>

The XML example shown above thus contains the following named elements:

companies, company, companyname, employee, code, name, street, houseno, areacode, place, phone.

Tag names are case sensitive, "name" is not the same as "Name". A tag is characterized by text surrounded by less than and greater than symbols: <tag>. Each tag must have a closing tag: </tag>. All text between the closing and opening tags belongs to the tag.

If there is no content between the tags we can combine opening and closing tag into one self-closing tag: <tag/>. This is done when either there is no content, or when the content of a tag is given as an attribute of the tag, for example::

<name name="Jan Janssen"/>

is equivalent to

<name>Jan Jansen</name>

The use of attributes really doesn't improve readability and it is recommended to use paired opening and closing tags instead.

The second line of the XML file contains the starting tag of the root element: <companies>. The element "companies" is the so-called Root element of the XML file. Each XML file must contain exactly one root element. Compare an XML file with a tree: a tree has exactly one stem. If there is more than one stem, we have a bush, not a tree.

Next, line 3 indicates the start of the element "companies". Within this "companies" element, multiple "company" elements may be nested.
Line 4 shows the entire "companyname" element, after which lines 5 to 27 contain data of the three employees of company "Stanford and Son".

The power of XML is that in order to understand the data structure and intent of an XML file, all you have to do is have a look at the file. Also, you can imagine adding a new company to this file, along with a couple of employees is simple.