topic Re: Complex hierarchical XML with looping structure to MS-SQL in Talend Studio

Complex hierarchical XML with looping structure to MS-SQL

Anonymous — Sat, 16 Nov 2024 04:31:24 GMT

I want to start by saying that I am new to Talend (2 weeks exposure). I come here with about 3 years of Informatica experience. I'm looking forward to learning the ins and outs of this tool.

The problem:

I am working with a fairly complex XML loop structure. Below is my start.

I am using Data Mapper to pull the necessary XML elements from a large XML source.Source File stream

Data Mapper Pulling from large source XMLI am using a FileOutputRaw just to verify that what is coming out of the data mapper is as expected.Confirm XML from Data Mapper

I believe the next step to tXMLMap is where I'm in need of the most help. If you take a look at the attached XML, you will see where the XML is looping. My task is to use an XML element with a qualifier to map to a column. As an example to get REP_PO_NUMBER, I need the XML field E1EDK02-BELNR where E1EDK02-QUALF = 001 which would have a value of "testSOLD". Below I have attached the definitions for "Target Column" with "XML Source" and the "Qualifier" (if any) with the expected results.

<?xml version="1.0" encoding="UTF-8"?>
<ZINVOIC2>
	<IDOC>
		<E1EDKA1>
			<PARVW>RS</PARVW>
			<NAME1>Corporation</NAME1>
		</E1EDKA1>
		<E1EDKA1>
			<PARVW>AG</PARVW>
			<PARTN>0000000320</PARTN>
			<NAME1> CORP</NAME1>
		</E1EDKA1>
		<E1EDKA1>
			<PARVW>AP</PARVW>
			<PARTN>0000002645</PARTN>
			<NAME1> CORP</NAME1>
			<NAME2>Person Lastname</NAME2>
		</E1EDKA1>
		<E1EDKA1>
			<PARVW>RE</PARVW>
			<PARTN>0000000403</PARTN>
			<NAME1>OUTSIDE ORGANIZATION</NAME1>
		</E1EDKA1>
		<E1EDKA1>
			<PARVW>RG</PARVW>
			<PARTN>0000000403</PARTN>
			<NAME1>OUTSIDE ORGANIZATION</NAME1>
		</E1EDKA1>
		<E1EDKA1>
			<PARVW>BK</PARVW>
			<NAME1>Internal Corporation</NAME1>
		</E1EDKA1>
		<E1EDK02>
			<QUALF>009</QUALF>
			<BELNR>0090000094</BELNR>
			<DATUM>20190801</DATUM>
		</E1EDK02>
		<E1EDK02>
			<QUALF>001</QUALF>
			<BELNR>testSOLD</BELNR>
			<DATUM>20190723</DATUM>
		</E1EDK02>
		<E1EDK02>
			<QUALF>002</QUALF>
			<BELNR>0008091672</BELNR>
			<DATUM>20190723</DATUM>
			<Z1EDK02>
				<AUART>ZQD</AUART>
			</Z1EDK02>
		</E1EDK02>
		<E1EDK02>
			<QUALF>012</QUALF>
			<BELNR>0080000056</BELNR>
			<DATUM>20190801</DATUM>
		</E1EDK02>
		<E1EDK02>
			<QUALF>017</QUALF>
			<BELNR>809167201</BELNR>
		</E1EDK02>
		<E1EDK02>
			<QUALF>087</QUALF>
			<BELNR>testSHIP</BELNR>
		</E1EDK02>
		<E1EDK14>
			<QUALF>008</QUALF>
			<ORGID>1000</ORGID>
		</E1EDK14>
		<E1EDK14>
			<QUALF>007</QUALF>
			<ORGID>35</ORGID>
		</E1EDK14>
		<E1EDK14>
			<QUALF>006</QUALF>
			<ORGID>00</ORGID>
		</E1EDK14>
		<E1EDK14>
			<QUALF>015</QUALF>
			<ORGID>ZF2</ORGID>
		</E1EDK14>
		<E1EDK14>
			<QUALF>003</QUALF>
			<ORGID>1000</ORGID>
		</E1EDK14>
		<E1EDK14>
			<QUALF>021</QUALF>
			<ORGID>M</ORGID>
		</E1EDK14>
		<E1EDK14>
			<QUALF>016</QUALF>
			<ORGID>1110</ORGID>
		</E1EDK14>
		<E1EDP01>
			<POSEX>000050</POSEX>
			<Z1EDP03>
				<MVGR1>AS370</MVGR1>
				<SPART>14</SPART>
				<UEPOS>ASDFS</UEPOS>
			</Z1EDP03>
			<E1EDPA1>
				<PARVW>WE</PARVW>
				<NAME1>SOMEKIND OF MANUFACTURING CORP</NAME1>
				<STRAS>1001 THIS STREET</STRAS>
				<STRS2>1002 THIS STREET</STRS2>
				<ORT01>THIS CITY</ORT01>
				<PSTLZ>00000</PSTLZ>
				<LAND1>US</LAND1>
				<REGIO>WI</REGIO>
				<Z1EDPA2>
					<STRS3>1003 THIS STREET</STRS3>
					<STRS4>1004 THIS STREET</STRS4>
				</Z1EDPA2>
			</E1EDPA1>
		</E1EDP01>
	</IDOC>
</ZINVOIC2>

Target Column, XML Source Field, XML Qualifier Field and value, Expected Outcome

As you can see, most of the outputs are expected to come from different iterations of the same element. (4 different iterations of E1EDK02, 3 different iterations of E1EDKA1, etc.)

Basic tXMLMap connections. I definitely am in need of assistance here.

Currently, my output only grabs the first value it finds for each element.

Output does not match expected.

I hope I've done a good enough job of explaining what I'm trying to do.

Please help.

Thank you,

-Dustin

Re: Complex hierarchical XML with looping structure to MS-SQL

Anonymous — Tue, 01 Oct 2019 02:38:34 GMT

Hi Dustin,

The reason for extracting only the first element is because your loop might be at different level. Could you please try to use another Hmap for extracting data based on different loop? You can also call one map from another map (by adding in the properties of first map).

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

Re: Complex hierarchical XML with looping structure to MS-SQL

Anonymous — Tue, 01 Oct 2019 14:38:21 GMT

Thank you for your response, Nikhil.

If you look at the tHMap (map_INVOIC), you can see that each level is capable of looping.

Like I said to start my initial post, I am very new to Talend. I am not sure how to go about what you suggest.

Re: Complex hierarchical XML with looping structure to MS-SQL

Anonymous — Tue, 01 Oct 2019 21:20:14 GMT

Is it possible to do all this work in the Data Mapper itself? I am struggling to find any relevant and helpful "How to" articles/videos.

Re: Complex hierarchical XML with looping structure to MS-SQL

Anonymous — Tue, 01 Oct 2019 22:14:10 GMT

@d_oshaughnessy

I would recommend you to go through the Talend Data Mapper Basics and Advanced courses which will help you to get better idea about different looping options. I am afraid I don't have enough time to create full solution for the problem. I would recommend you to add 5 sample input records and expected output for those sample records to the post.

I will try to look at it during my free time. May be some other active members may resolve the issue before I log back again 🙂

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

Re: Complex hierarchical XML with looping structure to MS-SQL

Anonymous — Wed, 02 Oct 2019 19:33:21 GMT

I found my own solution and it seemed pretty simple once I figured it out. I've seen several posts similar to mine that were unresolved. I'm hoping this can help others.

SOLUTION:

I first created my own output structure that mimic the looping of the source structure with split loops.Structure mimics source with split loops

While mapping the input to the output, I was able to use the same input element with a [SimpleLoop] and an [Equal] filter function mapped to a [Constant].

Rep: Constact AP

Bill_To: Constant RE

Sold_To: Constant AG

I followed this method for each element that I needed with qualifiers. This gave me exactly the output I was looking for.

<ZINVOIC2>
  <IDOC>
    <SOLD_TO>
      <SOLD_TO_NUMBER>0000000320</SOLD_TO_NUMBER>
      <SOLD_TO_NAME>CORP</SOLD_TO_NAME>
    </SOLD_TO>
    <REP>
      <REP_CONTACT>Aaron Gotham</REP_CONTACT>
    </REP>
    <BILL_TO>
      <BILL_TO_NUMBER>0000000403</BILL_TO_NUMBER>
      <BILL_TO_NAME>BILLING COMPANY INC</BILL_TO_NAME>
    </BILL_TO>
    <GOODS_ISSUE>
      <GOODS_ISSUE_DATE>20190801</GOODS_ISSUE_DATE>
    </GOODS_ISSUE>
    <REP_PO>
      <REP_PO_NUMBER>testSOLD</REP_PO_NUMBER>
    </REP_PO>
    <ORDER>
      <SALES_ORDER_NUMBER>0008091672</SALES_ORDER_NUMBER>
      <ORDER_TYPE>
        <ORDER_TYPE>ZQD</ORDER_TYPE>
      </ORDER_TYPE>
    </ORDER>
    <CUSTOMER_PO>
      <CUSTOMER_PO_NUMBER>testSHIP</CUSTOMER_PO_NUMBER>
    </CUSTOMER_PO>
    <SALES_ORG>
      <SALES_ORG>1000</SALES_ORG>
    </SALES_ORG>
    <INVOICE>
      <SALES_ORDER_LINE>000050</SALES_ORDER_LINE>
      <ITEM>
        <LONG_MODEL_PRODUCT_CLASS></LONG_MODEL_PRODUCT_CLASS>
        <LONG_MODEL_DIVISION>14</LONG_MODEL_DIVISION>
        <HIGHER_LEVEL_ITEM_LINE></HIGHER_LEVEL_ITEM_LINE>
      </ITEM>
      <SHIP_TO>
        <SHIP_TO_NAME>SOME MANUFACTURING CORP</SHIP_TO_NAME>
        <SHIP_TO_LINE2></SHIP_TO_LINE2>
        <SHIP_TO_LINE1>6900 THIS STREET</SHIP_TO_LINE1>
        <SHIP_TO_CITY>THIS CITY</SHIP_TO_CITY>
        <SHIP_TO_POST_CODE>99999</SHIP_TO_POST_CODE>
        <SHIP_TO_COUNTRY>US</SHIP_TO_COUNTRY>
        <SHIP_TO_REGION>WI</SHIP_TO_REGION>
        <Z1EDPA2></Z1EDPA2>
      </SHIP_TO>
    </INVOICE>
  </IDOC>
</ZINVOIC2>

I push this output to a tXMLMap to map the results to columns for my output.

I have not yet tested the efficiency of this and it is my first solution to the problem.

If others come up with a better solution, I'm all ears.

Thank you,

Dustin