- SAP Community
- Products and Technology
- Technology
- Technology Q&A
- Transform tables to hiarchic / nested xml
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Transform tables to hiarchic / nested xml
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 11-07-2012 7:37 PM
Hi all,
I was searching here and via Google about the issue and found some clues already. However, I would like to ask about any best practice here as I would suppose this must be a quite common task.
I have a source XML from an RFC that looks like this
<LT_DELIVERY_HEAD>
<item>
<VBELN>D1</VBELN>
<KUNNR>C1</KUNNR>
<ROUTE/>
</item>
<item>
<VBELN>D2</VBELN>
<KUNNR>C2</KUNNR>
<ROUTE/>
</item>
</LT_DELIVERY_HEAD>
<LT_HU_HEAD>
<item>
<EXIDV>E1</EXIDV>
<OBJKEY>D1</OBJKEY>
</item>
<item>
<EXIDV>E2</EXIDV>
<OBJKEY>D1</OBJKEY>
</item>
<item>
<EXIDV>E3</EXIDV>
<OBJKEY>D2</OBJKEY>
</item>
</LT_HU_HEAD>
Expected outpout would be
<Shipment>
<Delivery>
<DeliveryID>D1</DeliveryID>
<ShipToID>C1</ShipToID>
<HandlingUnit>
<HandlingUnitID>E1</HandlingUnitID>
<HandlingUnitID>E2</HandlingUnitID>
</HandlingUnit>
</Delivery>
<Delivery>
<DeliveryID>D2</DeliveryID>
<ShipToID>C2</ShipToID>
<HandlingUnit>
<HandlingUnitID>E3</HandlingUnitID>
</HandlingUnit>
</Delivery>
</Shipment>
I would like to know how to best (most easily) achieve this. Message Mapping with UDF? XSLT? Java Mapping? Or any other tricks?
Key here would be that HandlingUnitIDs E1 and E2 should reside under Delivery D1 and HandlingUnitID E3 under Delivery D2.
Thanks in advance for any input on this.
Cheers
Jens
- SAP Managed Tags:
- ABAP Development,
- SAP Process Integration
Accepted Solutions (1)
Accepted Solutions (1)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
I just wanted to let you know what I ended up with: I tried to do this without any XSLT, just by graphical mapping and playing with the context. This seemed to work with a 2 level (one header table, one child table) input. I, however didn't try this on a multi level structure (header table, child table, child of the child table)
So I solved this using XSLT.
Here's the XSLT to create the hierarchy out of a "flat", table-like input
Input:
<?xml version="1.0" encoding="UTF-8"?>
<MT_Test_Source>
<LT_DELIVERY_HEAD>
<item>
<VBELN>D1</VBELN>
<KUNNR>C1</KUNNR>
<ROUTE/>
</item>
<item>
<VBELN>D2</VBELN>
<KUNNR>C2</KUNNR>
<ROUTE/>
</item>
<item>
<VBELN>D0</VBELN>
<KUNNR>C5</KUNNR>
<ROUTE/>
</item>
</LT_DELIVERY_HEAD>
<LT_HU_HEAD>
<item>
<VENUM>H1</VENUM>
<EXIDV>E1</EXIDV>
<OBJKEY>D1</OBJKEY>
</item>
<item>
<VENUM>H2</VENUM>
<EXIDV>E2</EXIDV>
<OBJKEY>D1</OBJKEY>
</item>
<item>
<VENUM>H3</VENUM>
<EXIDV>E3</EXIDV>
<OBJKEY>D2</OBJKEY>
</item>
<item>
<VENUM>H4</VENUM>
<EXIDV>E4</EXIDV>
<OBJKEY>D0</OBJKEY>
</item>
<item>
<VENUM>H0</VENUM>
<EXIDV>E9</EXIDV>
<OBJKEY>D0</OBJKEY>
</item>
</LT_HU_HEAD>
<LT_HU_ITEM>
<item>
<VENUM>H0</VENUM>
<VEPOS>1</VEPOS>
<VEMNG>10</VEMNG>
<MATNR>39010000</MATNR>
</item>
<item>
<VENUM>H1</VENUM>
<VEPOS>1</VEPOS>
<VEMNG>3</VEMNG>
<MATNR>01800180</MATNR>
</item>
<item>
<VENUM>H1</VENUM>
<VEPOS>2</VEPOS>
<VEMNG>5</VEMNG>
<MATNR>10010000</MATNR>
</item>
</LT_HU_ITEM>
</MT_Test_Source>
Transform:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/MT_Test_Source/LT_HU_ITEM/item">
<Item>
<Position>
<xsl:value-of select="./VEPOS"/>
</Position>
<Material>
<xsl:value-of select="./MATNR"/>
</Material>
<Description>
<xsl:value-of select="./MAKTX"/>
</Description>
</Item>
</xsl:template>
<xsl:template match="/MT_Test_Source/LT_HU_HEAD/item">
<HandlingUnit>
<HandlingUnitID>
<xsl:value-of select="./VENUM"/>
</HandlingUnitID>
<xsl:variable name="HU" select="./VENUM"/>
<xsl:apply-templates select="/MT_Test_Source/LT_HU_ITEM/item[VENUM = $HU]"/>
</HandlingUnit>
</xsl:template>
<xsl:template match="/MT_Test_Source/LT_DELIVERY_HEAD/item">
<Delivery>
<DeliveryID>
<xsl:value-of select="./VBELN"/>
</DeliveryID>
<ShipToID>
<xsl:value-of select="./KUNNR"/>
</ShipToID>
<ShipToCity>
<xsl:value-of select="./CITY"/>
</ShipToCity>
<xsl:variable name="DELIVERY" select="./VBELN"/>
<xsl:apply-templates select="/MT_Test_Source/LT_HU_HEAD/item[OBJKEY = $DELIVERY]"/>
</Delivery>
</xsl:template>
<xsl:template match="/">
<Shipment>
<xsl:apply-templates select="/MT_Test_Source/LT_DELIVERY_HEAD/item"/>
</Shipment>
</xsl:template>
</xsl:stylesheet>
Output:
<?xml version="1.0"?>
<Shipment>
<Delivery>
<DeliveryID>D1</DeliveryID>
<ShipToID>C1</ShipToID>
<ShipToCity/>
<HandlingUnit>
<HandlingUnitID>H1</HandlingUnitID>
<Item>
<Position>1</Position>
<Material>01800180</Material>
<Description/>
</Item>
<Item>
<Position>2</Position>
<Material>10010000</Material>
<Description/>
</Item>
</HandlingUnit>
<HandlingUnit>
<HandlingUnitID>H2</HandlingUnitID>
</HandlingUnit>
</Delivery>
<Delivery>
<DeliveryID>D2</DeliveryID>
<ShipToID>C2</ShipToID>
<ShipToCity/>
<HandlingUnit>
<HandlingUnitID>H3</HandlingUnitID>
</HandlingUnit>
</Delivery>
<Delivery>
<DeliveryID>D0</DeliveryID>
<ShipToID>C5</ShipToID>
<ShipToCity/>
<HandlingUnit>
<HandlingUnitID>H4</HandlingUnitID>
</HandlingUnit>
<HandlingUnit>
<HandlingUnitID>H0</HandlingUnitID>
<Item>
<Position>1</Position>
<Material>39010000</Material>
<Description/>
</Item>
</HandlingUnit>
</Delivery>
</Shipment>
The important part is to create templates to match the respective tables, like so:
<xsl:template match="/MT_Test_Source/LT_DELIVERY_HEAD/item">
Then you pair this template to the childs table node that holds the foreign key like so:
<xsl:variable name="DELIVERY" select="./VBELN"/>
<xsl:apply-templates select="/MT_Test_Source/LT_HU_HEAD/item[OBJKEY = $DELIVERY]"/>
where $DELIVERY is a variable that holds the parent's primary key and OBJKEY holds the child's foreign key.
Almost same technique applies when flattening a hierarchical xml to a table-like structure:
Input
<?xml version="1.0"?>
<Shipment>
<Delivery>
<DeliveryID>D1</DeliveryID>
<ShipToID>C1</ShipToID>
<ShipToCity/>
<HandlingUnit>
<HandlingUnitID>H1</HandlingUnitID>
<Item>
<Position>1</Position>
<Material>01800180</Material>
<Description/>
</Item>
<Item>
<Position>2</Position>
<Material>10010000</Material>
<Description/>
</Item>
</HandlingUnit>
<HandlingUnit>
<HandlingUnitID>H2</HandlingUnitID>
</HandlingUnit>
</Delivery>
<Delivery>
<DeliveryID>D2</DeliveryID>
<ShipToID>C2</ShipToID>
<ShipToCity/>
<HandlingUnit>
<HandlingUnitID>H3</HandlingUnitID>
</HandlingUnit>
</Delivery>
<Delivery>
<DeliveryID>D0</DeliveryID>
<ShipToID>C5</ShipToID>
<ShipToCity/>
<HandlingUnit>
<HandlingUnitID>H4</HandlingUnitID>
</HandlingUnit>
<HandlingUnit>
<HandlingUnitID>H0</HandlingUnitID>
<Item>
<Position>1</Position>
<Material>39010000</Material>
<Description/>
</Item>
</HandlingUnit>
</Delivery>
</Shipment>
Transform
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/Shipment/Delivery/HandlingUnit/Item">
<item>
<VENUM>
<xsl:value-of select="../HandlingUnitID"/>
</VENUM>
<VEPOS>
<xsl:value-of select="./Position"/>
</VEPOS>
<MATNR>
<xsl:value-of select="./Material"/>
</MATNR>
<MAKTX>
<xsl:value-of select="./Description"/>
</MAKTX>
</item>
</xsl:template>
<xsl:template match="/Shipment/Delivery/HandlingUnit">
<item>
<VENUM>
<xsl:value-of select="./HandlingUnitID"/>
</VENUM>
</item>
</xsl:template>
<xsl:template match="/Shipment/Delivery">
<item>
<VBELN>
<xsl:value-of select="./DeliveryID"/>
</VBELN>
<KUNNR>
<xsl:value-of select="./ShipToID"/>
</KUNNR>
</item>
</xsl:template>
<xsl:template match="/Shipment">
<MT_Test_Source>
<LT_DELIVERY_HEAD>
<xsl:apply-templates select="/Shipment/Delivery"/>
</LT_DELIVERY_HEAD>
<LT_HU_HEAD>
<xsl:apply-templates select="/Shipment/Delivery/HandlingUnit"/>
</LT_HU_HEAD>
<LT_HU_ITEM>
<xsl:apply-templates select="/Shipment/Delivery/HandlingUnit/Item"/>
</LT_HU_ITEM>
</MT_Test_Source>
</xsl:template>
</xsl:stylesheet>
Output
<?xml version="1.0"?>
<MT_Test_Source>
<LT_DELIVERY_HEAD>
<item>
<VBELN>D1</VBELN>
<KUNNR>C1</KUNNR>
</item>
<item>
<VBELN>D2</VBELN>
<KUNNR>C2</KUNNR>
</item>
<item>
<VBELN>D0</VBELN>
<KUNNR>C5</KUNNR>
</item>
</LT_DELIVERY_HEAD>
<LT_HU_HEAD>
<item>
<VENUM>H1</VENUM>
</item>
<item>
<VENUM>H2</VENUM>
</item>
<item>
<VENUM>H3</VENUM>
</item>
<item>
<VENUM>H4</VENUM>
</item>
<item>
<VENUM>H0</VENUM>
</item>
</LT_HU_HEAD>
<LT_HU_ITEM>
<item>
<VENUM>H1</VENUM>
<VEPOS>1</VEPOS>
<MATNR>01800180</MATNR>
<MAKTX/>
</item>
<item>
<VENUM>H1</VENUM>
<VEPOS>2</VEPOS>
<MATNR>10010000</MATNR>
<MAKTX/>
</item>
<item>
<VENUM>H0</VENUM>
<VEPOS>1</VEPOS>
<MATNR>39010000</MATNR>
<MAKTX/>
</item>
</LT_HU_ITEM>
</MT_Test_Source>
Kind regards
Jens
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Answers (3)
Answers (3)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Thanks both for the valuable input. I must admint, I haven't tried Greg's solution as its getting late. I will however give it a shot tomorrow morning.
That said, the given example structure indeed was a bit simplified. In the real world scenario I have an Input XML as this:
<MT_INPUT>
<LT_DELIVERY_HEAD>
<item>
<VBELN>D1</VBELN>
<KUNNR>C1</KUNNR>
<ROUTE/>
</item>
<item>
<VBELN>D2</VBELN>
<KUNNR>C2</KUNNR>
<ROUTE/>
</item>
</LT_DELIVERY_HEAD>
<LT_HU_HEAD>
<item>
<VENUM>H1</VENUM>
<EXIDV>E1</EXIDV>
<OBJKEY>D1</OBJKEY>
</item>
<item>
<VENUM>H2</VENUM>
<EXIDV>E2</EXIDV>
<OBJKEY>D1</OBJKEY>
</item>
<item>
<VENUM>H3</VENUM>
<EXIDV>E1</EXIDV>
<OBJKEY>D2</OBJKEY>
</item>
</LT_HU_HEAD>
<LT_HU_ITEM>
<item>
<VENUM>H1</VENUM>
<VEPOS>1</VEPOS>
<MATNR>0815</MATNR>
</item>
<item>
<VENUM>H1</VENUM>
<VEPOS>2</VEPOS>
<MATNR>0816</MATNR>
</item>
<item>
<VENUM>H3</VENUM>
<VEPOS>1</VEPOS>
<MATNR>0817</MATNR>
</item>
</LT_HU_ITEM>
<LT_PARTY_ADR>
<item>
<PARTYNO>C1</PARTYNO>
<CITY>Berlin</CITY>
</item>
<item>
<PARTYNO>C2</PARTYNO>
<CITY>Paris</CITY>
</item>
</LT_PARTY_ADR>
<LT_MATERIAL>
<item>
<MATNR>0815</MATNR>
<MAKTX>Wheel</MAKTX>
</item>
<item>
<MATNR>0816</MATNR>
<MAKTX>Bumper</MAKTX>
</item>
<item>
<MATNR>0817</MATNR>
<MAKTX>Windshield</MAKTX>
</item>
</LT_MATERIAL>
</MT_INPUT>
Expected Output is this:
<Shipment>
<Delivery>
<DeliveryID>D1</DeliveryID>
<ShipToID>C1</ShipToID>
<ShipToCity>Berlin</ShipToCity>
<HandlingUnit>
<HandlingUnitID>E1</HandlingUnitID>
<Item>
<Position>1</Position>
<Material>0815</Material>
<Description>Wheel</Description>
</Item>
<Item>
<Position>2</Position>
<Material>0816</Material>
<Description>Bumper</Description>
</Item>
</HandlingUnit>
</Delivery>
<Delivery>
<DeliveryID>D2</DeliveryID>
<ShipToID>C2</ShipToID>
<ShipToCity>Paris</ShipToCity>
<HandlingUnit>
<HandlingUnitID>E2</HandlingUnitID>
<Item>
<Position>1</Position>
<Material>0817</Material>
<Description>Windshield</Description>
</Item>
</HandlingUnit>
</Delivery>
</Shipment>
Where there are the following relationsships between the tables in the input XML:
- OBJKEY "D1" matches to VBELN "D1" (LT_HU_HEAD/item/OBJKEY ==> n:1 ==> LT_DELIVERY_HEAD/item/VBELN)
- VENUM of HU-Items "H1" matches to VENUM of HU-Header "H1" (LT_HU_ITEM/item/VENUM ==> n:1 ==> LT_HU_HEAD/item/VENUM)
- KUNNR of Delivery "C1" matches to PARTYNO of Address Table "C1" (LT_DELIVERY_HEAD/item/KUNNR ==> n:1 ==> LT_PARTY_ADR/item/PARTYNO)
- MATNR of HU-Items "0815" matches to MATNR of Material Table "0815" (LT_HU_ITEM/item/MATNR ==> n:1 ==> LT_MATERIAL/item/MATNR)
Is it still possible to have this done with graphical mapping (with or even without UDF) or should I consider using XSLT? I, however, currently have only beginners skills in XSLT so I'm wondering if digging into this would really be necessary for the given task.
Also I didn't mention until now, that we need to transform this the other way around on the answer, meaning from hiarchical / tree to a flat table based XML as the RFC that is being called from a Webservice would expect those flat tables.
That being said, I will try the already suggested inputs tomorrow and will be back with results then.
Thanks so far.
Cheers!
Jens
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hello Jens,
Like Greg says you can probably accomplish this with normal context handling functions in graphic message mapping. But a UDF could certainly do it also, some people prefer just working in code.
Maybe this blog will help you with some context examples:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Jens,
It depends on what is "the easiest way" for you. Personally, I would use just graphical mapping, and even no UDFs are requred. So for DeliveryID and ShipToID mapping should be simple. You just need to sort the values by DeliveryID (using sort and sortByKey) because of how you will further map items. For HandlingUnitID, use the following mapping:
EXIDV -> sortByKey(OBJKEY) -> formatByExample( X ) -> HandlingUnitID, where
X = OBJKEY -> sort -> splitByValue(Value Change)
You might also need to play with the initial context, but the mapping above should give you the general idea and a jumpstart to the most difficult part .
Regards,
Greg
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- SAP UX Innovations in Public Cloud Products, Joule, AI, Entry Points, Apple Vision Pro and more in Technology Blogs by SAP
- SAP Fiori for Android 7.1 and iOS 10.0 – Introducing New Components and Mobile Card System in Technology Blogs by SAP
- Designing with Code (Part 2) - Crossing the Bridge in Technology Blogs by SAP
- Multi-Model powering Intelligent Data Apps (2/3) in Technology Blogs by SAP
- SAC Sunburst Widget - Hierarchical Data Visualization with SAC Data Binding and Linked Analysis in Technology Blogs by Members
| User | Count |
|---|---|
| 93 | |
| 11 | |
| 10 | |
| 9 | |
| 9 | |
| 7 | |
| 6 | |
| 5 | |
| 4 | |
| 4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.