XML

  • 0006 How to Transform a Complex XML file

    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

    XSLT ETL Transformation 

    • Double click on the reader and select source XML file

    XML Data Reader

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

    ETL Data Writer

    • Run the transformation by pressing the green arrow.

    Press magnifying glass button to amend the XSLT transformation 

    XSLT Editor

     

    Testing XSLT Transformation

     

    XSLT ETL Transformation Result

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0007 How to Convert XML Attributes into Elements

    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

    Convert XML attributes to XML tags 

    • Double click on the reader and select source XML file

    XML Data Reader

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

    XML Data writer

    • Run the transformation by pressing the green arrow.

    Press magnifying glass button to amend the XSLT transformation 

    XSLT Transformation

     

    XML Editor

     

    XML Result

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0008 How to Convert XML into CSV

    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

    Convert multiple XML files into CSV format 

    • Double click on the reader and select source XML Directory

    XML Data Reader

    • 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 

    ETL Data Transformer

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

    XML Example

    •  Amend XSLT Transformation if necessary

    XSLT Editor

    •  Press transform to see the result

    XSLT Transformation Result

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0010 How to Convert XML into Delimited File

    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

    Convert XML file into Delimited format 

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

    Convert XML file into Delimited format

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • Advanced ETL Processor performs XSLT transformations

    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

    XML Transfomation Functions

    XML Editor

    We would like to thank our customers for providing useful feedback

    Direct link, no registration required.
  • Advantages and disadvantages of working with XML

    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 
    Direct link, no registration required.
  • ETL and XSLT

    Here is an XML example

    XML With Attributes

    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 :
    XML with Attributes Datareader

    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.
    XML With Attributes Data

    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:

    XSLT Transformation

    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 Result

    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 
    Direct link, no registration required.
  • Generating XML

    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:

    • In-Place Replace
    • Encode XML Element
    • Decode XML Element
    • Encode XML Attribute
    • Decode XML Attribute
    • XSLT Transformation
    • Encode XML String
    • Decode XML String

    That makes it possible to perform following ETL Transformations:

    • Database table to XML File
    • The XML file to a database table
    • XML file to Flat file
    • Flat file to XML file
    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

    Generating XML Data

     

    Generating XML Data

     

    Direct link, no registration required.
  • Loading Images into Database

    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:

    • Load images into a database
    • Process Separate XML files
    • Process HL7 messages from the folder
    • Process EDI Messages
    • Load context of a directory into the database
    • Load users' photos
    A file system as a data-target

    Perform following ETL Transformations

    • Export data from blob fields
    • Save every record as a separate XML file
    • Export email messages
    • Generate EDI messages

    Advanced ETL Processor Screenshot

    Loading Files into the Database
    Direct link, no registration required.
  • Transforming XML Data

    Here is a simple XML example

    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 :
    Simple XML Data Reader Settings

    Note: "Ignore Tags" is checked

    Here is our data in the grid:
    Simple XML Data

    More complex XML example:
  • Using XSLT for ETL transformations

    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

    XML Transfomation Functions

    XML Data Source Options

    We would like to thank our customers for providing useful feedback

    Direct link, no registration required.
  • What is complex data

    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 excel fileSimple 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.

    Simple XML

    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 XML

    Complex Text file:

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

    Complex TEXT

    SQL insert script:

    Data can be easily extracted or generated using  Advanced ETL Processor

    Insert Statement

    Direct link, no registration required.
  • What is wrong with the XML

    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:

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • XSLT Transformation

    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 Package

     

    XSLT Transformation Package Action

    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 
    Direct link, no registration required.
 

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies