XML

This Example Demonstrates how to transform Complex XML into a simple, more readable format

The problem with XML

Here is an XML example, it is very well structured therefore there not need to perform additional transformations.

<Table>
<Record>
<ID>1</ID>
<Company>James Bond Production</Company>
<Amount>13</Amount>
</Record>
<Record>
<ID>2</ID>
<Company>Green Cloud</Company>
<Amount>14</Amount>
</Record>
</Table>

However there is no guarantie that next file will have exactly the same structure, for example this XML has a different tags order.

<Table>
<Record>
<Company>James Bond Production</Company>
<ID>1</ID>
<Amount>13</Amount>
</Record>
<Record>
<ID>2</ID>
<Company>Green Cloud</Company>
<Amount>14</Amount>
</Record>
</Table>

This XML has additional YEAR tag

<Table>
<Record>
<ID>1</ID>
<Company>James Bond Production</Company>
<Year>1956</Year>
<Amount>13</Amount>
</Record>
<Record>
<ID>2</ID>
<Company>Green Cloud</Company>
<Amount>14</Amount>
</Record>
</Table>

This kind of of problems can be easily addressed by using XSLT:

<?xml version=“1.0” encoding=“UTF-8”?>
<xsl:stylesheet version=“1.0” xmlns:xsl=“http://www.w3.org/1999/XSL/Transform”>
<xsl:output method=“xml” indent=“yes” version=“1.0”/>
<xsl:template match=“Table”>
<Table>
<xsl:for-each select=“Record”>
<Record><ID><xsl:value-of select=“ID”/></ID>
<Company><xsl:value-of select=“Company”/></Company>
<Amount><xsl:value-of select=“Amount”/></Amount>
</Record>
</xsl:for-each>
</Table>
</xsl:template>
</xsl:stylesheet>

 Note: XSLT is a way of transforming XML into a different format

Steps to follow

• Download and install Advanced ETL Processor [Link]
• Download and Unzip example [Link]
• Create a new transformation and open the .ats file

• Double click on the reader and select source XML file

• Repeat the process for the data writer (Point it at the result.csv file)

• Run the transformation by pressing the green arrow.

Press magnifying glass button to amend the XSLT transformation 

Please contact us if you need help with transforming the data

This Example Demonstrates how to convert XML attributes into XML elements

Here is an XML example, all the data we need is stored as XML attribute so we need to convert into simpler XML format

 <Table>
<Record>
<CompanyData ID="1" Company="James Bond Production" Amount="13"></CompanyData>
</Record>
<Record>
<CompanyData ID="2" Company="Green Cloud" Amount="146"></CompanyData>
</Record>
</Table>

Here is XSLTtransformation:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes" version="1.0"/>
<xsl:template match="Table">
<Table>
<xsl:for-each select="Record">
<Record> <ID><xsl:value-of select="CompanyData/@ID"/></ID>
<Company><xsl:value-of select="CompanyData/@Company"/></Company>
<Amount><xsl:value-of select="CompanyData/@Amount"/></Amount>
</Record>
</xsl:for-each>
</Table>
</xsl:template>
</xsl:stylesheet>

 Note: XSLT (Extensible Stylesheet Language Transformations) is a language for transforming XML documents into other XML documents or formats

Steps to follow

• Download and install Advanced ETL Processor [Link]
• Download and Unzip example [Link]
• Create a new transformation and open the .ats file

• Double click on the reader and select source XML file

• Repeat the process for the data writer (Point it at the result.csv file)

• Run the transformation by pressing the green arrow.

Press magnifying glass button to amend the XSLT transformation 

Please contact us if you need help with transforming the data

This Example Demonstrates how to convert multiple XML files into CSV files

 Here is an XML source example

<Table>
<Record>
<ID>1</ID>
<Company>James Bond Production</Company>
<Amount>13</Amount>
</Record>
<Record>
<ID>2</ID>
<Company>Green Cloud</Company>
<Amount>14</Amount>
</Record>
</Table>

Here is XSLT used transformation:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="UTF-8"/>
<xsl:template match="Table">
<xsl:text>ID,Company,Amount</xsl:text>
<xsl:text>&#xA;</xsl:text>
<xsl:for-each select="Record">
<xsl:value-of select="ID"/>
<xsl:text>,</xsl:text>
<xsl:value-of select="Company"/>
<xsl:text>,</xsl:text>
<xsl:value-of select="Amount"/>
<xsl:text>&#xA;</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

 Notes: We kept XSLT simple sot to is easy to understand and modify if necessary. 

Steps to follow

• Download and install Advanced ETL Processor [Link]
• Download and Unzip example [Link]
• Create a new transformation and open the .ats file

• Double click on the reader and select source XML Directory

• Repeat the process for the data writer
• Press the green arrow to run the transformation

 How it works

1. Data Reader scans the source directory and loads files into memory one by one
2. The transformer converts file in memory into CSV using XSLT
3. Data Writer writes results into the target directory

• To amend the  transformation double click on it

The transformation uses XML XSLT Transformation Function to convert XML to CSV and it also changes file name extension 

• To change XML XSLT Transformation Function properties double click on it.

•  Amend XSLT Transformation if necessary

•  Press transform to see the result

Please contact us if you need help with transforming the data

This Example Demonstrates how to convert an XML file into a Delimited file

In the previous example, we demonstrated how to transform multiple XML files into CSV. Using a transformer gives the users maximum flexibility, however not all users require such functionality. For them, we offer XSLT Transformation Package Action.

Steps to follow

• Download and install Advanced ETL Processor [Link]
• Download and Unzip example [Link]
• Create a new package and open the .wfp file

• Modify "XSLT Transformation" object properties and run the package by pressing the green arrow.

Please contact us if you need help with transforming the data

Great news Advanced ETL Processor support XSLT transformations now

A lot of XML files have very complex structure and XSLT allows to transform the data into more readable format:

<table>
<record>
<field1>value1<field1>
<field2>value2<field1>
<record>
<record>
<field1>value1<field1>
<field2>value2<field1>
<record>
<table>

XML can be transformed before loading the data or if the user wants to process/validate XML which is stored in the database field two new data transformation functions can be used

We would like to thank our customers for providing useful feedback

Loading data from XML can be a very complex task, but the complexity of this task depends on 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:

<CustomerOrderMessage>
<OrderNumber>1</OrderNumber>
</CustomerOrderMessage>



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 lead to mistakes and development time and cost increases.

In some of the 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"

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

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

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

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 so 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 

Here is an XML example

To load it all we need to do is to set the data source type to XML and select appropriate XML tags for "Table Tag"and "Record Tag"

Data reader settings :

Note: "Ignore Tags" is checked

There is no data in the grid because we have to transform XML into a more readable format first, using XSLT.

About XSLT:

XSLT (Extensible Stylesheet Language Transformations) is a language for transforming XML documents into other XML documents, or other objects such as HTML for web pages, plain text or into XSL Formatting Objects which can then be converted to PDF, PostScript, and PNG.

Typically, input documents are XML files, but anything from which the processor can build an XQuery and XPath Data Model can be used, for example, relational database tables, or geographical information systems.

Source:Wikipedia

XSLT:

Note: To get to this dialogue: open data reader properties, click XML file, check "Transform XML" and click Magnifying glass button

Here is the result of the XSLT transformation:

XSLT 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 

Generate XML data using any data source

Several New benefits of using our ETL Tools

Support for XML

In the latest version we've introduced 8 new transformation functions:

That makes it possible to perform following ETL Transformations:

What is XML

XML (Extensible Markup Language) is a set of rules for encoding documents electronically. It is defined in the XML 1.0 Specification produced by the W3C and several other related specifications; all are fee-free open standards

XML+ETL Challenges

• XML data can be stored in the file or as a field in the database.
• It can be created form any table or file or it can be loaded from any database or file

 Advanced ETL Processor Screenshot

We are constantly working on improving our ETL Software

A file system as a data source

In the latest version we've introduced file system as data-source so it is possible now:

A file system as a data-target

Perform following ETL Transformations

Advanced ETL Processor Screenshot

Here is a simple XML example

To load it all we need to do, is to set the data source type to XML and select appropriate XML tags for "Table Tag"and "Record Tag"

Data reader settings :

Note: "Ignore Tags" is checked

Here is our data in the grid:

More complex XML example:

Great news both Advanced ETL Processor and Visual Importer ETL support now XSLT transformations.

A lot of XML files have very complex structure and XSLT allows to transform the data into more readable format:

<table>
<record>
<field1>value1<field1>
<field2>value2<field1>
<record>
<record>
<field1>value1<field1>
<field2>value2<field1>
<record>
<table>

XML can be transformed before loading the data or if the user wants to process/validate XML which is stored in the database field two new data transformation functions can be used

We would like to thank our customers for providing useful feedback

This article provides examples of complex and simple data. We recommend using  Advanced ETL Processor for working with complex data structures and Visual Importer ETL with simple; If in doubt please contact us and we will help you to choose.

Simple data example: Excel file.

Data has a stable structure and there is only one value in the cell. Most database tables and delimited files can be considered simple data. except when a single field has multiple values or holds XML data.

Simple XML file:

XML tags are in the same order, the number of tags is always the same and there are no attributes, This XML does not require any additional transformation and can be easily loaded into the database.

Complex XML message:

The structure is not stable, the number of tags is different for a different customer record, plus it uses attributes. This XML may need some additional transformations.

Complex Text file:

Data is split into different rows, some empty rows should be ignored, some data must be taken from the header

SQL insert script:

Data can be easily extracted or generated using  Advanced ETL Processor

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

What is XML?

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 Problems

XML is a very inefficient way of storing the data 

For example:

<OrderMessage>
<OrderNumber>1</OrderNumber>
</OrderMessage>

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

XML is too flexible

This sounds like an advantage but the flexibility of XML can lead to unnecessary complexity and it can make it hard for developers to understand the XML structure, therefore lead to mistakes, increase in development time and cost.

In some of the cases, it is 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.

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

This XML has to be transformed in the format above so it can be loaded into a database:

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

Incosintent format

There is no guarantee that the next file you receive will have the same format as the previous one. It might have some elements missing, different element order or different encoding. Yes, this kind of problem can be easily addressed using XSLT transformations but again it requires more hardware resources.  

XML design tips:

• Use XML only when necessary
• Too much metadata is a bad thing
• Keep tags short
• Keep it simple and clean
• Design XML in such a way so it can be loaded without conversion

Learn how  to work with XML:

• How to Transform a Simple XML file
• How to Transform a Complex XML file
• How to Convert XML Attributes into Elements
• How to Convert XML into CSV
• How to Convert XML into Delimited File

Please contact us if you need help with transforming the data

While XML is a very flexible format but this flexibility brings problems, for example, one of the tags can be optional or tags order can be changed.

XSLT provides an easy way of transforming XML data into the standard format. XSLT can be applied during loading the data or in the latest version of Advanced ETL Processor and Visual Importer ETL using package action

Note: XSLT stands for EXtensible Stylesheet Language, and is a style sheet language for XML documents. XSLT stands for XSL Transformations.

Package Example

XSLT 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