How to merge XML tags to one, based on item number

OL Connect Professional DataMapper
1

Dear all

I’ve a XML file that the structure is separated in three parts: product description (TAG: IT_GEN), product count (TAG: IT_KOND) and product price (TAG: IT_PRICE). Please see example below. For a detailed table I need to have one table and all parts to one dynamic table → one dynamic table with product description, count and price.

Is this possible to do?
BR
Miro

<?xml version="1.0" encoding="utf-8"?>
<sf>
	<abap>
		<values>
			<IS_BIL_INVOICE>
				<IT_GEN>
					<LBBIL_IT_GEN>
						<BIL_NUMBER>0041123604</BIL_NUMBER>
						<ITM_NUMBER>000001</ITM_NUMBER>
						<SHORT_TEXT>Red</SHORT_TEXT>
					</LBBIL_IT_GEN>
					<LBBIL_IT_GEN>
						<BIL_NUMBER>0041123604</BIL_NUMBER>
						<ITM_NUMBER>000002</ITM_NUMBER>
						<SHORT_TEXT>Blue</SHORT_TEXT>
					</LBBIL_IT_GEN>
				</IT_GEN>
				<IT_PRICE>
					<LBBIL_IT_PRICE>
						<BIL_NUMBER>0041123604</BIL_NUMBER>
						<ITM_NUMBER>000001</ITM_NUMBER>
						<PRICE>710.0</PRICE>
					</LBBIL_IT_PRICE>
					<LBBIL_IT_PRICE>
						<BIL_NUMBER>0041123604</BIL_NUMBER>
						<ITM_NUMBER>000002</ITM_NUMBER>
						<PRICE>177.5</PRICE>
					</LBBIL_IT_PRICE>
				</IT_PRICE>
				<IT_KOND>
					<LBBIL_IT_KOND>
						<BIL_NUMBER>0041123604</BIL_NUMBER>
						<ITM_NUMBER>000001</ITM_NUMBER>
						<COUNT>70</COUNT>
					</LBBIL_IT_KOND>
					<LBBIL_IT_KOND>
						<BIL_NUMBER>0041123604</BIL_NUMBER>
						<ITM_NUMBER>000001</ITM_NUMBER>
						<COUNT>100</COUNT>
					</LBBIL_IT_KOND>
					<LBBIL_IT_KOND>
						<BIL_NUMBER>0041123604</BIL_NUMBER>
						<ITM_NUMBER>000002</ITM_NUMBER>
						<COUNT>219</COUNT>
					</LBBIL_IT_KOND>
					<LBBIL_IT_KOND>
						<BIL_NUMBER>0041123604</BIL_NUMBER>
						<ITM_NUMBER>000002</ITM_NUMBER>
						<COUNT>902</COUNT>
					</LBBIL_IT_KOND>
					<LBBIL_IT_KOND>
						<BIL_NUMBER>0041123604</BIL_NUMBER>
						<ITM_NUMBER>000002</ITM_NUMBER>
						<COUNT>80</COUNT>
					</LBBIL_IT_KOND>
				</IT_KOND>
			</IS_BIL_INVOICE>
		</values>
	</abap>
</sf>
2

The trick here is to loop on your LBBIL_IT_KOND elements. With each iteration, you extract the COUNT, ITM_NUMBER and BIL_NUMBER elements, but you also store the ITM_NUMBER value in a variable.

You can then use an XPATH statement to retrieve the corresponding LBBL_IT_PRICE/PRICE and LBBIL_IT_GEN/SHORT_TEXT values.

So for instance, if you have a record variable named ItemNumber that contains the ITM_NUMBER value, then for any LBBIL_IT_KOND element, you can extract its SHORT_TEXT value by using the following statement:

data.extract('../../IT_GEN/LBBIL_IT_GEN[ITM_NUMBER="'+sourceRecord.properties.ItemNumber+'"]/SHORT_TEXT');

Once expanded by the XML engine, this statement becomes:
data.extract('../../IT_GEN/LBBIL_IT_GEN[ITM_NUMBER="000001"]/SHORT_TEXT'); or
data.extract('../../IT_GEN/LBBIL_IT_GEN[ITM_NUMBER="000002"]/SHORT_TEXT');

Here is a sample data mapping configuration that demonstrates this technique: Details.OL-datamapper (3.9 KB)

3

Hi Phil

Wow, thank you very much for your answer. I will try to implement your solution and give your feedback.

4

Hi Phil

it works :grinning: . Thank you for your solution. BR Miro