XML Transformation in Informatica

Introduction

In this document we will see how to process the XML data in Informatica Mapping. Before that we should know what is transformation in Informatica.

Informatica Transformations are repository objects which can read, modify or pass data to the defined target structures like tables, files, or any other targets required. A Transformation is basically used to represent a set of rules, which define the data flow and how the data is loaded into the targets.

What are Informatica Transformations

In Informatica, Transformations help to transform the source data according to the requirements of target system and it ensures the quality of the data being loaded into target.

Transformations are as classified as Active or Passive, Connected or Unconnected

Active Transformation:

An Active transformation can change the number of rows that pass through the transformation, change the transaction boundary, can change the row type.

Informatica Designer does not allow us to connect multiple active transformations or an active and passive transformation to the same downstream transformation or transformation input group because the Integration Service may not be able to concatenate the rows passed by active transformations except Sequence Generator Transformation(SGT). Because it generates unique numeric values since Integration Service does not encounter any problem while concatenating the rows.

Passive Transformation:

A passive transformation does not change the number of rows that pass through it, maintains the transaction boundary, and maintains the row type.

Informatica Designer allows you to connect multiple transformations to the same downstream transformation or transformation input group only if all transformations in the upstream branches are passive.

Connected Transformation:

Transformations which are connected to the other transformations or directly to target table in the mapping are called connected transformations.

Unconnected Transformation:

An unconnected transformation is not connected to other transformations in the mapping. It is called within another transformation, and returns a value to that transformation.

XML Transformation:

Informatica Power Center has powerful in-built functionality to process XML data. We can create an XML definition in Power Center from an XML file, DTD file, XML schema, flat file definition, or relational table definition.

We have three types of XML transformations and they are listed below:

XML Source Qualifier Transformation:

It is an active transformation as well as connected transformation. This is used only with an XML source definition. It represents the data elements that the Informatica Server reads when it executes a session with XML sources. XML Source Qualifier has one input or output port for every column in the source. If we remove an XML source definition from a mapping, the Designer also removes the corresponding XML Source Qualifier transformation.

Example:

Create Source Definition for XML Source Qualifier in Informatica

Once we connected to Informatica designer with our credentials, please navigate to Source Analyzer to define our XML data as a source. Let’s assume the XML file is at our Local File System. Import XML definition window will be called when we do as shown below

Import XML definition window:

Please select the Xml Contract.xml file from your local file system and click Open button.

This will open a XML wizard(Step 1). Click Next button

Click Finish button

In this example we are using the XML file with Entity relation so, we are selecting first option. If we are using a XML file Hierarchies then will select the second option.

In Entity relationship the Designer selects a root and creates separate views for complex types and multiple-occurring elements. It defines relationships and inheritance between complex types. It defines relationships between views with keys.

From the below screenshot we can observe that, we can see our newly created XML Source defenition in Informatica

We can use this Source definition in mapping window as we use generally. But this time it will show as XML Source Qualifier instead of Source Qualifier. See the below diagram.

We can use required fields from this Source Qualifier and load it to our target tables based on the business requirement. In our example the XML source Qualifier has many views for the XML Contract. See the below screenshot for XML Views. Double click on the Source definition.

If we require to edit any of the XML properties or column then will do the change in this view and will validate it. Please navigate to XML Views-> Validate XML Definition as shown below

In case if we want to edit the XSD file structure which was used earlier that will be taken from the following way in XML view itself. Click on View -> XML Metadata

Click on no namespace to get the XSD file it will open the file in installed XSD Editor like Microsoft Visual Studio

XML Parser Transformation:

It is an active transformation as well as connected transformation. This is used to extract XML inside a pipeline and then pass this to the target. The XML is extracted from the source systems such as files or databases. The XML Parser transformation reads XML data from a single input port and writes data to one or more output ports.

Example:

If the source definition is flat file or relational table which contains one column as XML data (Clob datatype) then XML parser will be used to retrieve the data.

Please navigate Source-> Import from Database

XDATA column contains the XML value. Only one XML data column can be parsed to XML Parser Transformation. Because one input port only allowed for XML Parser Transformation. But we can pass thru the other columns like REQ_ID, TRANSMISSION_ID columns to XML Parser Transformation.

Take the source definition into Mapping Designer

Now we need to create the XML parser Transformation by using the XSD file of the XDATA column from Source definition. Please navigate Transformation -> Create in Mapping designer

Select XML Parser from the Transformation type drop down list and click on create

It will open the Import XML definition window for the XSD file import. Click on Open

After importing the XSD file click on Next

We need to select they XSD structure type as we followed in XML Source Qualifier. Click on Finish in the below step.

Click on Done to complete the XML Parser Transformation creation

 

Once we are done with XML Parser Transformation creation we need to link the XDATA column from Source definition to Data Input in XML Parser Transformation.

In case if the normal fields from Source definition also needed for the target then that fields can be taken as $Pass Thru columns in XML Parser Transformation.

In the below example REQ_ID and TRANSMISSION ID are taken as Pass Thru columns in XML Parser Transformation.

We will take the required fields from XML Parser to downstream transformations.

If the data from multiple views need to be take it out to the target then we need to use Joiner transformation with the Sorted Input option.

Finally, the target table will be linked as given in the below diagram.

Note:

In case if we wish to edit the XSD structure in exiting mapping then we need to take the copy of the mapping and the copy one will be in checked in state. In the copy one we can able to re pull the new XSD structure by using synchronize xml definition(Right click XML Parser Transformation). Once done with all the changes in the copy one will rename the original mapping with _backup and the copy one should get renamed to original. Before doing Check out we need to make all the changes in copy mapping. Once done the corresponding session also should be checked out to point the correct mapping in which the changes have been made.