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).

XML file shown in Internet Explorer

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.



All comments about this page:

Comment by: satpal (16-10-2010 21:16:38) deeplink to this comment

we want to upload the excise dealer return in xml format but excise system (aces) remarks that your xml file structure is damaged.please guide us what should we do.




Comment by: Jan Karel Pieterse (17-10-2010 05:15:44) deeplink to this comment

I expect it means that the XML file is missing something. Either the structure of the xml file does not adhere to the rules that each tag needs an opening and a closing part or the order of the opening and closing tags isn't correct.
Also, not everything is allowed as values between tags, maybe some characters need to be escaped.

Comment by: vietthufet (10-6-2013 06:35:11) deeplink to this comment

this article really usefull for me. I'm starting work on XML file

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].