Complex hierarchical XML with looping structure to MS-SQL

Highlighted
Five Stars

Complex hierarchical XML with looping structure to MS-SQL

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. 
Talend_INVOIC_Job.PNG

 

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

 

INVOIC_DataMapper_tHMap.PNGData 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.INVOIC_Confirm_xml_to_xmlMap.PNGConfirm 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>

Definitions_Expected_Results.PNGTarget 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.)

 

 

INVOIC_tXMLMap1.PNGBasic tXMLMap connections. I definitely am in need of assistance here.

 

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

INVOIC_Log_Output.PNGOutput 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


Accepted Solutions
Five Stars

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

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.INVOIC_Mapper_Output_Structure.PNGStructure 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].

 

INVOIC_Rep.PNGRep: Constact AP

 

INVOIC_Bill_To.PNGBill_To: Constant RE

 

INVOIC_Sold_To.PNGSold_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


All Replies
Employee

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

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

 

Five Stars

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

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.

Five Stars

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

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.
Employee

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

@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

Five Stars

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

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.INVOIC_Mapper_Output_Structure.PNGStructure 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].

 

INVOIC_Rep.PNGRep: Constact AP

 

INVOIC_Bill_To.PNGBill_To: Constant RE

 

INVOIC_Sold_To.PNGSold_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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog