Today after discussion with my PM , got a question in mind - how its working exactly. Though "Web Services Consumer" tranformation is not getting created at my end because of license issue, I had number of things to check for XML Tranformation
To start with I have taken XML Source
To create a XML source, you need to have a XML file. I have taken it from -- http://www.w3schools.com/xml/xml_tree.asp
<?xml version="1.0" encoding="ISO-8859-1"?>
<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>
<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>
Saved it at Local drive and imported XML Tranformation. While importing it, we get below options
1.You will come across such screen at the time of importing a XML definition. Keep every thing as default.
Sometimes last option "How would you like to generate names for the XML Columns" will be default as first option, select it as second option and check all the mentioned check boxes.
After clicking on OK, you will get a screen asking "How would you like the XML Views to be generated?" as specified below.
Choose any option of Entitiy Relationship and Hierarchy Relationship; structure will be same in this case. Structure may differ for rest of the option or the XML is of more complex nature.
Once you import the tranformation successfully, it will be like this.
Here, structure got pivoted. Note is the main node, that became the primary key of a xml tranformation, rest to, from, heading and body became port of the tranformation i.e. column of a table. So, main outer tag became the row and inner tags became column ultimately.
We will be connecting this XML Source to Target as CSV file.
Below is the structure of a mapping
This structure is pretty simple, only caution is at the time of connection definition at Session Level. You need to define the XML and Target file path cautiously - please see below
As in the image, we have given value of 2 parameters
Source File Directory = D:\InformaticaFiles\
Source File Name = myXML.xml
By same way you need to specify directory and name for target file.
Once you run the map, target file will have one row inserted as below.
Below is the out-put of target file. Note, while generating the target, create dummy csv file with 5 columns specified with 30 as a each column size.
=================================================================
Now we added 2 more pipelines - so there are not totally 3 pipelines. First will have Normalized XML, second will have De-Normalized and the last one is created with option "Do not generate XML View". We will see regarding how to generate XML Source using "Do not generate XML View option", but if you notice at all 3 source definition, first 2 has one extra port - XPK_X_Note - which is also a primary key. This extra port is missing at 3rd definition which is created with "Do not generate XML View option". We will discuss on how to create XML Source using Do not generate xml view option later.
When we define same target file for all 3 targets, only 1 record get inserted - might be every pipeline will update the same record. So, we defined 3 separate files for each target, first 2 files have unique value for <node> got inserted, third file is missing that particular column.
First port - offcouse for first 2 sources - will generate a unique number for every run. In this case - second pipeline was not exists for first few runs. So, second XML Source count is slightly lacking behind the first source. What we have to find out is, if we dis-connect from repository, still that value persist?
Hmm, dis-connected from repository and after re-connecting it ran the workflow. Concluded values are persistent, might be getting saved at database against workflow run. Not sure how to re-set these values.
One more experiment I did was, I changed the XML Source file now as below
<?xml version="1.0"?>
<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
<to>staksale</to>
<from>magogate</from>
<heading>Reminder</heading>
<body>Meet tomorrow morning at 10</body>
<to>Tove</to>
<from>asjaiwa</from>
<heading>magogate</heading>
<body>Meet tomorrow morning at 10</body>
</note>
Now there are totally 3 records inside <node>, but when we run the workflow, it saved only first record of Jani.
This means that, our XML Source is defined to read only below structure
<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>
If you want to insert number of records, you need to pass that many xml files separately. That's the reason why people are using XML Generator tranformation. So that you just pass your input values to it, and it will generate the XML dynamically and forward it ahead.
Now we changed the XML files as below
<?xml version="1.0"?>
<note>
<to>staksale</to>
<from>magogate</from>
<heading>Reminder</heading>
<body>Meet tomorrow morning at 10</body>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
<to>Tove</to>
<from>asjaiwa</from>
<heading>magogate</heading>
<body>Meet tomorrow morning at 10</body>
</note>
This time, mapping picked up first record which is of staksale. and you could see it got inserted into source files.
if you want to insert all 3 records, please define 3 separate files for every records, and give it as an input.
No comments:
Post a Comment