Read Multiple Nested loop in database

[Rajender]

Hi Community

I have a multiple nested loop xml. Read XML and load data into table. Below is required XML. 

I have enterprise version 7.1 of Talend Integration tool.  I went through several post on community. 

I tried to build mapping based on following link. Getting error while merging  <BLine> and <Acatch> into a single loop using NestedContext -> simpleLoop feature as suggested i below link. Both <BLine> and <Acatch> loop are not visible in final output. Below is error Received. 

My target is to load Clinecd, BLine, Actach, Line, Route and B-King data into separate tables.  

I am newbie to Talend.

Please suggest  on map or any other approach .  Thanks in Advance.

https://community.talend.com/t5/Design-and-Development/How-to-process-xml-message-with-multiple-loop...

<Message>
<B-king>(1-n)
<Sourceid>11111</Sourceid>
<TargetID/>
<BoNumber>0017650</BoNumber>
<BStatus>1</BStatus>
<CAStatus>0</CAStatus>
<BLine>(0-n)
<Sourceid>22222</Sourceid>
<TargetID/>
<ModelType>Cars</ModelType>
<ModelName>i10</ModelName>
<PrimaryCode>3</PrimaryCode>
<SecondaryCode>9</SecondaryCode>
<Quantity>1</Quantity>
<Clinecd>
<blineId> abc</blineId>
<blineno>cde</blineno>
</Clinecd>
<Note/>
</BLine>
<BLine>
<Sourceid>22223</Sourceid>
<TargetID/>
<ModelType>Cars</ModelType>
<ModelName>i10</ModelName>
<PrimaryCode>3</PrimaryCode>
<SecondaryCode>9</SecondaryCode>
<Quantity>1</Quantity>
<Clinecd>(0-n)
<blineId> abc1</blineId>
<blineno>cde1</blineno>
</Clinecd>
<Note/>
</BLine>
<Actach>(0-n)
<Sourceid>32222</Sourceid>
<TargetID/>
<ModelType>Cars</ModelType>
<ModelName>i10</ModelName>
<PrimaryCode>3</PrimaryCode>
<SecondaryCode>9</SecondaryCode>
<Quantity>1</Quantity>
<Clinecd>
<blineId> bc</blineId>
<blineno>de</blineno>
</Clinecd>
<Note/>
</Actach>
<Actach>
<Sourceid>32223</Sourceid>
<TargetID/>
<ModelType>Cars</ModelType>
<ModelName>i10</ModelName>
<PrimaryCode>3</PrimaryCode>
<SecondaryCode>9</SecondaryCode>
<Quantity>1</Quantity>
<Clinecd>(0-n)
<blineId>bc1</blineId>
<blineno>de1</blineno>
</Clinecd>
<Note/>
</Actach>
<Route>(1-n)
<SourceID>765991</SourceID>
<TargetID/>
<SequenceNumber>1</SequenceNumber>
<CLine>(0-n)
<CLineID>33211</CLineID>
<LineID/>
<Status>02</Status>
</CLine>
<CLine>
<CLineID>645332</CLineID>
<LineID/>
<Status>02</Status>
</CLine>
</Route>
<Line>(0-n)
<SourceID>003</SourceID>
<TargetID/>
<Statu>99</Statu>
<ID/>
<L>12.3</L>
<W>2.5</W>
<H>0.83</H>
<Wt>8000</Wt>
<Remark/>
<Charge>
<base>12</base>
<ratio>.2</ratio>
</Charge>
<CLink>
<LineID>645332</LineID>
<Quantity>1</Quantity>
</CLink>
</Line>
</B-king>
</Message>

[Rajender]

Hi Community 

I have used tXMLMap component to resolve problem. But as BLine, Acatch and Line are 0 to unlimited node. Because of which I am looking B-King Data. 

What i Mean is B-King has 5 records for which only one BLine record. For B-King Configure source loop is set B-King/BLine/Clinecd/BlineId.  In my final output, I have only one records not 5 records.

Please help.

[Anonymous]

Hi Rajender,

     The loop will have to be at the same level if you want to extract multiple records from an XML.

     Could you please share the current XMLMap screen shot, current output and desired output so that we will get an idea about the flow you are trying to do?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Rajender]

Hello @nthampi 

thanks for your time.

Loops are at multilevel to extract information.  Issue was with data type BYTE is giving error as data is character N. To resolve it I have changed source datatype string from byte.

Is there any other way to handle data type conversion.

Regards

Rajender Prasad

[Anonymous]

Hi Rajender,

   The byte data type description is as shown below.

"byte: The byte data type is an 8-bit signed two's complement integer. It has a minimum value of -128 and a maximum value of 127 (inclusive). The byte data type can be useful for saving memory in large arrays, where the memory savings actually matters. They can also be used in place of int where their limits help to clarify your code; the fact that a variable's range is limited can serve as a form of documentation."

So if the value of the incoming data is "Y" or "N", you need to do a condition check in tMap before inserting them to target column. For example, if the value is "N", you can insert 0 and if the value is "Y", then insert "1".

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Rajender]

As I am using tXMLMap to extract output. I am not getting expected output. I observe that Input xml file have some missing tags which is define with occurrence 0-1 or 0-n.

Is it possible to get multiple output from Talend Mapper. 

Following is example XML and expected output. 

Please help. Thanks in Advance.

<Message>
<B-king>(1-n)
<Sourceid>11111</Sourceid>
<TargetID/>
<BoNumber>0017650</BoNumber>
<BStatus>1</BStatus>
<CAStatus>0</CAStatus>
<BLine>(0-n)
<Sourceid>22222</Sourceid>
<TargetID/>
<ModelType>Cars</ModelType>
<ModelName>i10</ModelName>
<PrimaryCode>3</PrimaryCode>
<SecondaryCode>9</SecondaryCode>
<Quantity>1</Quantity>
<Clinecd>
<blineId> abc</blineId>
<blineno>cde</blineno>
</Clinecd>
<Note/>
</BLine>
<BLine>
<Sourceid>22223</Sourceid>
<TargetID/>
<ModelType>Cars</ModelType>
<ModelName>i10</ModelName>
<PrimaryCode>3</PrimaryCode>
<SecondaryCode>9</SecondaryCode>
<Quantity>1</Quantity>
<Clinecd>(0-n)
<blineId> abc1</blineId>
<blineno>cde1</blineno>
</Clinecd>
<Note/>
</BLine>
<Actach>(0-n)
<Sourceid>32222</Sourceid>
<TargetID/>
<ModelType>Cars</ModelType>
<ModelName>i10</ModelName>
<PrimaryCode>3</PrimaryCode>
<SecondaryCode>9</SecondaryCode>
<Quantity>1</Quantity>
<Clinecd>
<blineId> bc</blineId>
<blineno>de</blineno>
</Clinecd>
<Note/>
</Actach>
<Actach>
<Sourceid>32223</Sourceid>
<TargetID/>
<ModelType>Cars</ModelType>
<ModelName>i10</ModelName>
<PrimaryCode>3</PrimaryCode>
<SecondaryCode>9</SecondaryCode>
<Quantity>1</Quantity>
<Clinecd>(0-n)
<blineId>bc1</blineId>
<blineno>de1</blineno>
</Clinecd>
<Note/>
</Actach>
<Route>(1-n)
<SourceID>765991</SourceID>
<TargetID/>
<SequenceNumber>1</SequenceNumber>
<CLine>(0-n)
<CLineID>33211</CLineID>
<Status>02</Status>
</CLine>
<CLine>
<CLineID>645332</CLineID>
<LineID/>
<Status>02</Status>
</CLine>
</Route>
<Line>(0-n)
<SourceID>003</SourceID>
<TargetID/>
<Statu>99</Statu>
<CLink>
<LineID>645332</LineID>
<Quantity>1</Quantity>
</CLink>
</Line>
</B-king>
</Message>

B-king
Sourceuid|Targetid|B-kingSourceID|BoNumber|BStatus|CAStatus
11111||0017650|1|0

BLine and Actach
SourceID|Targetid|B-kingSourceID|ModelType|ModelName|PrimaryCode|SecondaryCode|Quantity|TYPE
22223||11111|Cars|i10|3|9|1|1|BLine
32222||11111|Cars|i10|3|9|1|1|Actach

Clinecd
BlineId|BLineSourceID|Bilneno
abc|22222|cde|BLine
abc1|22223|cde1|BLine
bc|32222|de|Actach
bc1|32223|de1|Actach

Route
SourceID|TargetID|B-kingSourceID|SequenceNumber
765991||11111|1

cline
ClineID|RouteID|status
3321|765991|02

Line
SourceID|TargetID|B-kingSourceID|Statu|ID|L|
003|11111||99

CLink
LineID|Quantity|LineID
645332|1|003

[Anonymous]

Hi,

    If a tag is missing, it will allocate Null value to the output section.

    So you need to add a Null check condition as additional step and map them to target values to be passed.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Rajender]

Hi @nthampi

Thanks for your Time.

Null check condition, Did you mean in expression something like " isnull(input)? default: input ".

Due to missing tags, Is it possible that tXMLMap does not map tags properly. Its observe value of data mismatch.

Regards

Rajender Prasad

[Anonymous]

Hi,

   You are right. If the data is missing, you need to fill them with default data.

    Could you please try and let us know the results?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Rajender · Accepted solution]

Hi @nthampi 

I have used thmap --> txmlmap. thmap is used to provide all the missing tags then TXMLMap to load all schema into file.

Please suggest is it right approach to load multiple schema XML.

One more thing, thmap has a datatype character with value 01 but final output of tXMLMap datatype character is 0 only.

Is tXMLMap character datatype accept/return only 1 character.

Thanks in Advance.

Regards

Rajender Prasad