Loading data from XML can be a very complex task, but the complexity of this task depends on the people who design XML in the first place. In this article, we will provide you with some examples of loading data from XML files and transforming it. We will also talk about things to avoid and how to make the life of developers easier.

What is XML anyway?

Extensible Markup Language (XML) is a markup language that defines a set of rules for encoding documents in a format that is both human-readable and machine-readable. It is defined in the XML 1.0 Specification produced by the W3C, and several other related specifications, all gratis open standards.
The design goals of XML emphasize simplicity, generality, and usability over the Internet. It is a textual data format with strong support via Unicode for the languages of the world. Although the design of XML focuses on documents, it is widely used for the representation of arbitrary data structures, for example in web services.

Source: Wikipedia

XML is incredibly flexible, but it has some disadvantages as well.

For example:


The XML above has only one byte of information the rest of it is metadata. Using too much metadata requires more processor power, memory and increase network traffic(Which is great news for hardware vendors, but bad news for the people who have to pay for it)

The flexibility of XML can lead to unnecessary complexity and it can make it hard for developers to understand, therefore leading to mistakes and development time and cost increases.

In some of cases, it can be necessary to convert XML into a simplified format so it can be loaded into the database.
This XML can be loaded by most of the ETL tools.

Note: CustomerTable is a "Table tag" and CustomerRecord  is a "Record Tag"

<CustomerName>Peter Jones</CustomerName>
<CustomerName>Bill Watson</CustomerName>

This XML may need to be transformed in the format above so it can be loaded into the database later:

<CustomerRecord CustomerID="1" CustomerName="Peter Jones"/>
<CustomerRecord CustomerID="2" CustomerName="Bill Watson"/>

XML design tips
  1. Use XML when it is necessary
  2. Too much metadata is a bad thing
  3. Keep tags short
  4. Keep it simple and clean
  5. Check ETL documentation and design XML in such a way that it can be loaded without conversion
XML Transformation Examples
  1. How to Transform a Simple XML file
  2. How to Transform a Complex XML file
  3. How to Convert XML Attributes
  4. How to Convert XML into CSV using XSLT 
Direct link, no registration required.