Skip to Content

Transform tables to hiarchic / nested xml

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    Posted on Nov 19, 2012 at 09:24 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 07, 2012 at 08:10 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 07, 2012 at 08:28 PM

    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:

    http://scn.sap.com/people/claus.wallacher/blog/2006/04/17/replication-of-nodes-using-the-graphical-mapping-tool

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 07, 2012 at 09:55 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.