Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(10 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Efficiently extract data from an XML source in PowerCenter
Content

This article demonstrates how to efficiently extract data from an XML source in PowerCenter.

The following example shows two different ways to import an XML source definition and uses that source definition in a mapping.

Requirements

A mapping is needed that extracts data from the XML source file purchasesample.xml and populate all the columns in the four target tables.

Sources

The following XML sources will be used:

  • purchasesample.xsd (XML schema)
  • purchasesample.xml (XML instance document)

Targets

The following relation database tables will be the targets:

  • PURCHASE_ORDER
  • PURCHASE_ORDER _LINE
  • PURCHASE_ORDER_DELIVERY
  • PURCHASE_ORDER_RECEIPTS

Procedure

It is highly recommended to use an XML schema (XSD) for creating the XML source definition in PowerCenter. An XML schema document describes the metadata of every element and attribute present in the XML (such as datatype, length, pattern, etc.). If an XML schema is used, instead of an XML instance document or DTD file, to create the source definition, many extra steps are avoided when creating the XML source definition.
There are multiple ways in which an XML source definition can be created in the Source Analyzer. However the most efficient way is to use the Skip Create XML views option and design the XML views according to the way the target is defined. This option is very powerful, easy to use and reduces the complexity of the mapping design. This also greatly improves the performance of the session since many of the joins are done at the XML parser level.

Using Entity Relationships option

This method uses the Entity relationship option (the default option) when creating the XML source definition and builds the mapping from this source.

  1. Create the XML definition.

    1. Import the XML definition using the purchasesample.xsd schema file
    2. Select Entity Relationships .

  2. Connect the PURCHASE_ORDER target.

    1. Create a new mapping.
    2. Link the following columns from the X_PurchaseOrder view to the Purchase_Order target
      • purchase OrderNumber
      • planning Division
      • GlobalStatusCode
    3. Link the XPK_PurchaseOrder column (automatically generated by PowerCenter) from the X_PurchaseOrder view to the Transaction_ID column in the target.
      This is a primary key column that needs a unique sequence generated value for a Purchase Order.

  3. Connect the PURCHASE_ORDER_LINE target.
    Since XML views are active transformations, you need a Joiner transformation to join columns from different views of the XML source definition.

    1. Create the JNRTRANS Joiner transformation.
    2. Link the following columns from the X_PurchaseOrder view to the JNRTRANS transformation.
      • XPK_PurchaseOrder
      • purchase OrderNumber
      • planning Division
    3. Select the Sorted Input option in the JNRTRANS transformation.
    4. Link the following columns from the X_PurchaseOrderLineDetail view to the JNRTRANS transformation:
      • XPK_PurchaseOrderLineDetail
      • FK_PurchaseOrder
      • lineNumber
      • ProductIdentifier
      • GlobalStatusCode
    5. Create the following join condition in the JNRTRANS transformation:

      XPK_PurchaseOrder = FK_PurchaseOrder

    6. Link the columns in the JNRTRANS transformation to the target.

  4. Connect the target PURCHASE_ORDER_DELIVERY.

    1. Create the JNRTRANS1 Joiner transformation .
    2. Link the following columns from JNRTRANS to JNRTRANS1 :
      • XPK_PurchaseOrderLineDetail
      • purchase OrderNumber
      • planning Division
      • lineNumber
      • XPK_PurchaseOrder
    3. Select the Sorted Input option in JNRTRANS1 .
    4. Link the following columns from the X_PurchaseOrderDeliveryDetail view to JNRTRANS1 :
      • XPK_PurchaseOrderDeliveryDetail
      • FK_PurchaseOrderLineDetail
      • deliveryLineNumber
      • currentDeliveryDate
      • GlobalStatusCode
    5. Create the following join condition in JNRTRANS1:

      XPK_PurchaseOrderLineDetail = FK_PurchaseOrderLineDetail

    6. Link the columns in the JNRTRANS1 transformation to the target.

  5. Connect the PURCHASE_ORDER_RECEIPTS target.

    1. Create the JNRTRANS2 Joiner transformation.
    2. Link the following columns from JNRTRANS1 to JNRTRANS2 :
      • XPK_PurchaseOrderDeliveryDetail
      • purchase OrderNumber
      • planning Division
      • lineNumber
      • deliveryLineNumber
      • XPK_PurchaseOrder.
    3. Select Sorted Input in JNRTRANS2 .
    4. Link the following columns from the X_ReceiptDetail view to JNRTRANS1 :
      • FK_PurchaseOrderDeliveryDetail
      • receivedDate
      • receivedQuantity
    5. Create the following join condition in JNRTRANS2 :

      XPK_PurchaseOrderDeliveryDetail = FK_PurchaseOrderDeliveryDetail

    6. Link the columns in the JNRTRANS1 transformation to the target.

As you can see below, the resulting mapping is quite complex with multiple joiner transformation.

Using Skip Create XML Views option

This method uses the Skip Create XML Views option when creating the XML source definition and builds the mapping from this source.

  1. Create the XML definition.

    1. Import the XML definition using the purchasesample.xsd schema file.
    2. Select the Skip Create XML Views option.

  2. Create the X_PurchaseOrder XML View.

    1. In the XML editor, select XML Views >Create XML View to create a view
    2. Enter "X_PurchaseOrder" for the name of the XML view.
    3. In the Schema Navigator window, right-click on LeasePurchaseOrders and select Show XPath Navigator .
    4. In the XPath Navigator, drag and drop the following columns from PurchaseOrder to the X_PurchaseOrder view
      • purchase OrderNumber
      • planning Division
      • GlobalStatusCode

  3. Create the X_PurchaseOrderLineDetail XML View

    • Type Ctrl-N to create a view.
    • Enter "X_PurchaseOrderLineDetail" for the name of the XML view.
    • In the Schema Navigator window, right-click on LeasePurchaseOrders and select Show XPath Navigator.
    • In the XPath navigator, drag and drop the following columns from PurchaseOrderLineDetail to the X_PurchaseOrderLineDetail view
      • lineNumber
      • ProductIdentifier
      • GlobalStatusCode
    • In the XPath navigator, drag and drop the following columns from PurchaseOrder to the X_PurchaseOrderLineDetail view:
      • purchase OrderNumber
      • planning Division
    • Right click on X_PurchaseOrderLineDetail>Create Relationship>X_PurchaseOrder view.

  4. Create the XML View X_PurchaseOrderDeliveryDetail

    • Type Ctrl-N to create a view.
    • Enter "X_PurchaseOrderDeliveryDetail" for the name of the XML view.
    • In the Schema Navigator window, right-click on LeasePurchaseOrders and select Show XPath Navigator.
    • In the XPath navigator, drag and drop the following columns to the X_PurchaseOrderDeliveryDetail view
      • deliveryLineNumber, currentDeliveryDate and GlobalStatusCode from PurchaseOrderDeliveryDetail
      • lineNumber from PurchaseOrderLineDetail
      • purchase OrderNumber and planning Division from PurchaseOrder
    • Right click on X_PurchaseOrderDeliveryDetail>Create Relationship>X_PurchaseOrder view.

  5. Create the XML View X_ReceiptDetail.

    1. Type Ctrl-N to create a view.
    2. Enter "X_ReceiptDetail" for the name of the XML view.
    3. In the Schema Navigator window, right-click on LeasePurchaseOrders and select Show XPath Navigator .
    4. From the XPath navigator, drag and drop the following columns to the X_RecieptDetail view:
      • receivedDate and receivedQuantity from ReceiptDetail
      • deliveryLineNumber from PurchaseOrderDeliveryDetail
      • lineNumber from PurchaseOrderLineDetail
      • purchase OrderNumber and planningDivision from PurchaseOrder.
    5. Right click on X_ReceiptDetail>Create Relationship>X_PurchaseOrder view.

  6. Link the columns from the XML source to each target.

The resulting mapping is quite simple:

Summary

Always use the Skip Create XML Views option and design your own XML views, this will greatly simplify the complexity of the mapping design.

Other Options

View Row

If you need only one row for every purchase order in Purchase_Order_Line target table containing only the first occurrence of the Line Number instead of multiple rows for every Line Number.

  1. Open the XML Editor.

  2. Select the X_PurchaseOrderLineDetail view.

  3. Right click on the PurchaseOrderNumber column.

  4. Select Set as View Row .

By default, PurchaseOrderLineDetail is set as View Row and you will get multiple rows from this XML View as the lineNumber changes.

Pivoting

If you need only one row for every purchase order in Purchase_Order_Line target table containing the first three occurrences of the line number instead of multiple rows for every Line Number.

  1. Open the XML editor.

  2. Select the X_PurchaseOrderLineDetail view.

  3. Right click on the LineNumber column.

  4. Select Delete this column .

  5. Right click on the PurchaseOrderNumber column.

  6. Select Set as View Row.

  7. In the XPath Navigator, switch to Advanced Mode .

  8. Drag the column LineNumber to the view 3 times to create 3 columns.

     



More Information


Reference


Attachments


Applies To
Product: PowerCenter
Problem Type:
User Type: Developer
Project Phase:
Product Version: PowerCenter
Database: XML
Operating System:
Other Software:

Last Modified Date: 7/28/2010 2:26 AM ID: 17880
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)