0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×

XML Pipeline Transformation

XML Pipeline Transformation

Summary:

XML Pipeline is a readymade logic in the form of transform which helps to extract part of XML data dynamically.

  • XML Pipeline transform supports only XML (nested structure) input dataset.
  • XML Pipeline transform converts Nested structure into Flat structure.
  • No multiple inputs are allowed with XML pipeline.

Prerequisite:

  • Input to the XML Pipeline transform must be a XML source, either DTD (Document Type Definition) or XSD (XML Schema Definition).

Limitation:

  • XML pipeline allows only level (part) of extraction, i.e. from one level, only a set of columns of one node are allowed to extract.

 NOTE:

  • Multiple XML Pipeline transforms are used to extract data from multiple nodes present in same level.
  • XSD or DTD file structure should be defined and taken as source in the job and XML file should be provided in Source editor properties under ‘File name’ property to make a complete sense of XML source.
  • Output Schema maintenance of XML Pipeline transform is under user control. But there is no flexibility to add new fields in the output schema.

 Example Scenario:

 A sample XSD file is show in Figure 1(a) which is the structure of input XML source and is defined as input to XML pipeline transform.

XML Pipeline Transformation in BODS
Figure 1(a): Sample XML Source Structure(XSD)

Figure 1(b) shows a sample XML file which contains the data of input XML source. It is to be selected in the Source editor properties.

XML Pipeline Transformation in BODS
Figure 1(b): Sample XML Source Data

A sample target is shown in Figure 2 which is a flat structure obtained using XML pipeline transform. 

ORDERID

ORDERPERSON

NAME

ADDRESS

COUNTRY

TITLE

PRICE

889923

John Smith

Ola Nordmann

Langgt 23

Norway

Empire Burlesque

10.90

Figure 2: Sample Target Data

XML Pipeline Transformation in SAP BODS
Figure 3: XML Pipeline Transformation Object Hierarchy
  • From figure 4 it is clear that two XML Pipeline transforms are used to extract the data from input XML-XSD dataset.
  • Item and Ship-to (Delivery) are two nodes present at the same level in the given source.
  •  Two XML Pipelines are used to extract fields from these two nodes.
  • A query transform is used to ‘join’ these two nodes to get a single flat output structure.
XML Pipeline Transformation in BODS
Figure 4: XML Pipeline Transformation Job ETL flow
XML Pipeline Transformation in BODS 3
Figure 5: XML Pipeline Transformation Defining rules for Item details
XML Pipeline Transformation in SAP BODS 2
Figure 6: XML Pipeline Transformation Defining rules for Ship-to details
  • XML pipeline allows only level of extraction. Hence two XML Pipelines are used to extract data since ‘Item details’ and ‘Ship-to details’ nodes are in same level as in figures 4, 5, & 6.
XML Pipeline Transformation in BODS 4
Figure 7: Query Transformation Mapping rules

Attachments:

Source File:

  • Use the below files as source for working out on the transformation

Click Here to Download Source File 1

Click Here to Download Source File 2

.atl File:

  • Import the below .atl file in the Data Services Designer to find the Job for XML Pipeline transformation.

Click Here to Download ATL File.

That’s it.

Enjoy 🙂

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×