XML Source Qualifier Transformation in Informatica PowerCenter

XML Source Qualifier Transformation in Informatica PowerCenter is an active transformation that represents the rows read from an XML source. When an XML source definition is added to a mapping, Informatica can create the corresponding XML Source Qualifier automatically.

The XML Source Qualifier converts hierarchical XML data into rows and columns that downstream transformations and relational targets can process. Its output structure depends on the XML source definition, the selected XML views, and the relationships defined while importing the source.

In this Informatica tutorial, we create a mapping that reads employee data from an XML file and loads it into an Oracle target table. The mapping demonstrates how to import an XML source, create the XML Source Qualifier, generate a target definition, configure the session, and run the workflow.

XML Source and Oracle Target Used in This Informatica Mapping

  • XML source file: emp.xml
  • Target: A relational table created from the XML Source Qualifier output ports and replicated in the Oracle database.
  • Mapping name: m_xml_emp

Before creating the mapping, confirm that the XML file is valid and that its elements and attributes represent the employee fields required by the target. If the XML file references an XSD or DTD, keep the required schema files accessible during source import.

How Informatica Represents Hierarchical XML Data

An XML document may contain parent elements, repeating child elements, nested groups, and attributes. Informatica imports this hierarchy as one or more XML views. Each view exposes fields as ports that can be connected to transformations or targets.

For a straightforward XML-to-relational load, a denormalized XML view is often convenient because related parent and child values can be presented together. However, denormalization may repeat parent values when a parent contains multiple child records. Review the resulting ports and expected row count before designing the target table.

How to Create an XML Source Qualifier Transformation in Informatica

Import emp.xml in Informatica Source Analyzer

  1. Open the Designer and select Source Analyzer from the Tools menu.
  2. Choose the option to import an XML definition.
  3. Browse to the directory containing emp.xml.
  4. Select the XML file and click Open.
  5. Confirm the import prompts and proceed to the XML definition wizard.
  6. Review the detected XML structure and retain the required elements and attributes.
  7. Click Next to configure the XML views.
  8. Select the required hierarchy relationships.
  9. Select a Denormalized XML View when the mapping must expose related XML fields in a flattened row structure.
  10. Click Finish and save the repository changes.

After the import, inspect the source definition carefully. Verify field names, datatypes, lengths, hierarchy relationships, and the ports included in each XML view. Incorrect metadata at this stage can produce rejected rows, truncated values, or an unexpected number of output records.

Create the m_xml_emp Mapping and XML Source Qualifier

  1. Open Mapping Designer.
  2. Create a mapping named m_xml_emp.
  3. Drag the imported XML source definition into the mapping workspace.
  4. Allow Informatica to create the XML Source Qualifier associated with the XML source.
  5. Open the XML Source Qualifier and confirm that the required XML output ports are available.
  6. Remove unused ports from the data flow or leave them unconnected when they are not required by the target.

The source definition describes the XML metadata, while the XML Source Qualifier represents the records that the Integration Service reads during the session. Connect downstream objects to the qualifier’s output ports rather than directly to the source definition.

Create a Relational Target from the XML Source Qualifier

  1. Right-click the XML Source Qualifier and select Create and Add Target.
  2. Review the generated target definition and assign a suitable target table name.
  3. Connect the required qualifier ports to the corresponding target ports.
  4. Verify that each source datatype is compatible with its target datatype.
  5. Check the precision and scale of numeric fields and the length of character fields.
  6. Save the mapping.
XML Source Qualifier Transformation

Creating a target definition in the mapping does not by itself create the physical table in Oracle. The corresponding table must also exist in the target database before the workflow can load rows into it.

Generate the Oracle Target Table SQL

  1. Open Target Designer.
  2. Drag the generated target definition into Target Designer if it is not already available there.
  3. Open the Targets menu and select Generate/Execute SQL.
  4. Choose the target definition for which the table creation statement must be generated.
  5. In the SQL generation window, select Generate SQL File.
XML Source Qualifier Transformation
  1. Click Edit SQL to inspect the generated statement.
  2. Review the table name, column names, datatypes, precision, and constraints before execution.
  3. Run the approved CREATE TABLE statement against the target Oracle database, or execute it through the available Informatica database connection.
  4. Confirm that the physical table has been created successfully.
  5. Save the target definition and mapping.

Generated SQL should be reviewed rather than executed without inspection. Repository metadata may not include every database-specific requirement, such as tablespaces, indexes, primary keys, naming conventions, or storage settings.

Configure the XML Source File in the Informatica Session

Create a session task for m_xml_emp and configure the XML source file properties. The exact property labels can vary by PowerCenter version and session configuration, but the session must identify both the source directory and the XML filename.

  • Source file type: Direct
  • Source directory: Installation Directory\server\infa_shared\SrcFiles
  • Source filename: emp.xml

Place emp.xml in the directory accessible to the Integration Service. The path is evaluated on the machine where the Integration Service runs, not necessarily on the workstation where PowerCenter Designer is installed.

Configure the relational target connection for Oracle and verify that the selected database user has permission to insert into the target table. Set the required target load options, commit interval, error handling, and session log settings.

Run and Validate the XML-to-Oracle Workflow

  1. Create a workflow and add the session task for the m_xml_emp mapping.
  2. Validate the workflow and resolve invalid connections or missing session properties.
  3. Start the workflow.
  4. Open Workflow Monitor and check whether the session completes successfully.
  5. Review the session log for the number of source rows read, target rows loaded, rejected rows, and transformation errors.
  6. Query the Oracle target table and compare the loaded values with the records in emp.xml.

A successful workflow status is not the only validation requirement. Confirm that repeating XML elements produced the expected number of rows, optional elements were handled correctly, and parent values were associated with the correct child records.

XML Source Qualifier Transformation Behavior

  • The transformation is active because one hierarchical XML input can produce multiple relational output rows.
  • It exposes the XML views and ports defined in the imported source metadata.
  • It handles the relationship between parent and child XML groups according to the imported hierarchy.
  • It can flatten selected XML structures when a denormalized view is used.
  • It supplies rows to downstream transformations and targets but does not create the physical target table automatically.

Common XML Source Qualifier Mapping Problems

XML File Is Not Found by the Integration Service

Confirm that the session source directory points to a path accessible from the Integration Service host. Also verify the filename, extension, operating-system path format, and file permissions.

XML Rows Do Not Match the Expected Record Count

Review repeating child elements and the selected hierarchy relationships. A denormalized XML view may create one row for each repeating child and repeat the associated parent values.

XML Values Are Truncated or Rejected

Compare XML Source Qualifier port lengths and datatypes with the target definition. Increase character lengths where required and check numeric precision, date formats, and null handling.

Oracle Target Table Is Missing

The target definition stored in the Informatica repository is metadata only. Generate and review the SQL, create the physical table in Oracle, and then run the session.

XML Structure Changed After Source Import

If elements, attributes, namespaces, or hierarchy relationships in the XML change, refresh or re-import the source definition. Then review affected ports, mappings, target connections, and session properties before running the workflow again.

XML Source Qualifier Transformation FAQs

Is XML Source Qualifier an active or passive transformation?

XML Source Qualifier is an active transformation. The number and structure of output rows can differ from the hierarchical XML input because repeating groups and parent-child relationships are converted into relational rows.

What is the difference between an XML source and an XML Source Qualifier?

The XML source definition stores the imported metadata, including XML views, fields, and hierarchy relationships. The XML Source Qualifier represents the rows read from that source during mapping execution and provides output ports to downstream objects.

Can Informatica create a relational target from an XML Source Qualifier?

Yes. Designer can create a target definition from the qualifier ports. However, the corresponding physical table must still be generated and created in the target database.

Why are parent XML values repeated in the target rows?

This commonly occurs when a denormalized view combines a parent with a repeating child group. Informatica produces a row for each child occurrence and includes the related parent values in each row.

XML Source Qualifier Mapping QA Checklist

  • Confirm that emp.xml is well-formed and matches the imported source structure.
  • Verify that the selected normalized or denormalized XML view matches the required target grain.
  • Check parent-child hierarchy relationships and repeating XML groups.
  • Confirm that all required XML Source Qualifier ports are connected to the correct target ports.
  • Compare source and target datatypes, lengths, precision, scale, and nullability.
  • Verify that the Oracle table exists and that the target connection has the required privileges.
  • Confirm that the Integration Service can access the configured source directory and filename.
  • Compare source rows read, target rows loaded, rejected rows, and the final Oracle record count.

Summary of the Informatica XML Source Qualifier Process

To load XML data into Oracle, import the XML definition in Source Analyzer, select the required hierarchy and XML view, add the source to m_xml_emp, connect the XML Source Qualifier ports to a relational target, create the physical Oracle table, and configure the session with the correct XML file path. After running the workflow, validate both the session statistics and the loaded target data.

The same process can be adapted to other XML files, provided that the imported XML metadata, hierarchy relationships, target design, and session file settings accurately represent the source document.