cancel
Showing results for 
Search instead for 
Did you mean: 

XSLT mapping help

Former Member
0 Kudos

Hi

I have an input payload xml as in the attachment   1example.txt. I am very new to xslt mapping.

I am trying to convert using xslt code this payload into a header - detail relationship. The key field is reference.

So the output should be as below

please can somebody give me the xslt code to convert attached xml into below parent child relation.

<?xml version="1.0" encoding="utf-8"?>

<ns:MT_FIDCC2_FILE_TO_PI xmlns:ns="XXXXXXXXXXXXXXXXXXXXXxxx">

<DT_FILEHDR_00>

  <Trailer>00</Trailer>

  <Interface>OTVI</Interface>

  <FileId>20130215122238</FileId>

</DT_FILEHDR_00>

<DT_HEADER_01>                                                       - invoice 1

  <Trailer>01</Trailer>

  <Interface>OTVI</Interface>

  <FileId>20130215122238</FileId>

  <Companycode>TBA</Companycode>

  <DocDate>20130124</DocDate>

  <Reference>PIP10000200/101</Reference>

  <DocHdr>PIP10000200/101</DocHdr>

  <Currency>GBP</Currency>

<DT_VNDRLINE_02>                                             - child 1 with same reference

  <Reference>PIP10000200/101</Reference>

  <Trailer>02</Trailer>

  <Interface>OTVI</Interface>

  <FileId>20130215122238</FileId>

  <Vendor>TBC</Vendor>

  <BaseDate>20130215</BaseDate>

  <PayTerms>Z001</PayTerms>

  <DocAmt>18.90</DocAmt>

  <ItemText>PIP10000200/101</ItemText>

</DT_VNDRLINE_02>

<DT_VNDRBNK_03>                                             - child 1 with same reference

  <Reference>PIP10000200/101</Reference>

  <Trailer>03</Trailer>

  <Interface>OTVI</Interface>

  <FileId>20130215122238</FileId>

  <Name1>Carl Tuesday</Name1>

  <Name2></Name2>

  <Name3></Name3>

  <Language>E</Language>

  <PostalCode>LE10 3EY</PostalCode>

  <City>Leeds</City>

  <CountryKey>GB</CountryKey>

  <Street>16 Long street name truncates here!</Street>

  <BnkCountryKey>GB</BnkCountryKey>

  <Bankno>200178</Bankno>

  <Bankacc>13445566</Bankacc>

  <BankCntrl></BankCntrl>

  <BankRef></BankRef>

</DT_VNDRBNK_03>

<DT_GLLINE_04>                                                       - child 1 with same reference

  <Reference>PIP10000200/101</Reference>

  <Trailer>04</Trailer>

  <Interface>OTVI</Interface>

  <FileId>20130215122238</FileId>

  <GLAcc>0000000000</GLAcc>

  <AmtDocCur>18.90</AmtDocCur>

  <VatDocCur>0.00</VatDocCur>

  <TaxCode>V0</TaxCode>

  <CostCntr></CostCntr>

  <WBS>CODE1</WBS>

  <ItemText></ItemText>

</DT_GLLINE_04>

</DT_HEADER_01>

Accepted Solutions (1)

Accepted Solutions (1)

smavachee
Active Contributor
0 Kudos

If you have Altova or any similar tool, go ahead with it. You can follow these simple steps..

> Store XSD’s of both Source and Target structures on local machine.

> Create a new Mapping File

> Import XSD’s

> Complete required mapping with use of available functions

> Output would be required XSLT code, save as .xsl 

> Zip .xsl file and import the same in ESR under Imported Archives.

If you want to go ahead with practicing on xslt mapping, refer this document by Amit and Anshul..

XSLT Mapping

Hope it helps.!

Regards,

Sunil


Former Member
0 Kudos

Hi Girish,

Please try this xsl:

  <?xml version="1.0" encoding="ISO-8859-1" ?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ns="XXXXXXXXXXXXXXXXXXXXXXx">

  <xsl:output method="xml" />

<xsl:template match="/">

<ns:MT_FIDCC2_FILE_TO_PI xmlns:ns="XXXXXXXXXXXXXXXXXXXXXXx">
<xsl:copy-of select="//DT_FILEHDR_00"/>

<xsl:apply-templates select="//DT_HEADER_01">

</xsl:apply-templates>

</ns:MT_FIDCC2_FILE_TO_PI>


</xsl:template>

<xsl:template match="DT_HEADER_01">
<DT_HEADER_01>
<xsl:apply-templates/>
<xsl:variable name="ref" >
<xsl:value-of select="./Reference"/>
</xsl:variable>
<xsl:copy-of select="/ns:MT_FIDCC2_FILE_TO_PI/DT_VNDRLINE_02[Reference=$ref]"/>
<xsl:copy-of select="/ns:MT_FIDCC2_FILE_TO_PI/DT_VNDRBNK_03[Reference=$ref]"/>
<xsl:copy-of select="/ns:MT_FIDCC2_FILE_TO_PI/DT_GLLINE_04[Reference=$ref]"/>

</DT_HEADER_01>

</xsl:template>

  <xsl:template match="@* | node()">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>

</xsl:stylesheet>

Regards,

Beena

Answers (3)

Answers (3)

Former Member
0 Kudos

Thanks Beena. Your XSLT works - can make few changes that I need.

Many thanks.

Former Member
0 Kudos

Sorry I could not reply earlier. Yes, I figured out how to do it and happy that you agree with it !!!

But I have one more requirement I am struggling with.

The file can have under every invoice header /DT_HEADER_01>, many gl lines <DT_GLLINE_04> that can be more than 949 for e.g. 1000. In this case I need to split this invoice into 2 parts - one containing all header information + first 949

<DT_GLLINE_04> lines for that invoice; and another invoice with exactly the same header nodes and the remaining 51 <DT_GLLINE_04> lines in it.

Please could you specify in your latest xslt how to achieve this.

Asking only because I am struggling currently to get my head round xslt commands since I am very new to it.


Former Member
0 Kudos

Hi Girish,

I tried to do some changes for your requirement, however its not complete yet:

<?xml version="1.0" encoding="ISO-8859-1" ?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ns="XXXXXXXXXXXXXXXXXXXXXXx">

  <xsl:output method="xml" />

<xsl:template match="/">

<ns:MT_FIDCC2_FILE_TO_PI xmlns:ns="XXXXXXXXXXXXXXXXXXXXXXx">
<xsl:copy-of select="//DT_FILEHDR_00"/>

<xsl:apply-templates select="//DT_HEADER_01">

</xsl:apply-templates>

</ns:MT_FIDCC2_FILE_TO_PI>


</xsl:template>

<xsl:template match="DT_HEADER_01">

<xsl:variable name="ref" >
<xsl:value-of select="./Reference"/>
</xsl:variable>

<xsl:variable name="count" select="count(//ns:MT_FIDCC2_FILE_TO_PI/DT_GLLINE_04[Reference=$ref] ) " />
<xsl:variable name="iteration" select= " floor($count div  900) + $count mod 900 + 1"/>

<xsl:call-template name="header">
<xsl:with-param name="ref1" select="$ref"/>
<xsl:with-param name="current" select="1" />
<xsl:with-param name="end" select="$iteration" />
</xsl:call-template>


</xsl:template>

<xsl:template name="header">
<xsl:param name="ref1"/>
<xsl:param name="current" />
<xsl:param name="end" />

<xsl:if test="$current &lt; $end">
<DT_HEADER_01_INTER>
<xsl:apply-templates/>
<xsl:copy-of select="/ns:MT_FIDCC2_FILE_TO_PI/DT_VNDRLINE_02[Reference=$ref1]"/>
<xsl:copy-of select="/ns:MT_FIDCC2_FILE_TO_PI/DT_VNDRBNK_03[Reference=$ref1]"/>
<xsl:variable name="temp" select="($current -1) * 2" />
<xsl:copy-of select="/ns:MT_FIDCC2_FILE_TO_PI/DT_GLLINE_04[Reference=$ref1 and position() &gt; $temp and position() &lt; $temp + ]" />  ---> need to add logic here to select 1 to 900 records in first iterationan and 900 to 1800 in second and so on

can play with current iteration value and 900 to build this

</DT_HEADER_01_INTER>
<xsl:call-template name="header">
<xsl:with-param name="ref1" select="$ref1"/>
<xsl:with-param name="current" select="$current + 1" />
<xsl:with-param name="end" select="$end" />
</xsl:call-template>


</xsl:if>

</xsl:template>


  <xsl:template match="@* | node()">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>


</xsl:stylesheet>

The above xslt would create 2 header nodes for DT_Header_01 but still need to add logic for gtline as mentioned earlier.

I have used procedural logic here, I am not sure if its best way to do it. I will try to search but by the time hope this helps if you need urgently.

Also while searching for this came across key and generate-id functions which can be used to convert flat xml file to nested xml. You may check that too.

By the way is there any specific reason for using xslt mapping?

Regards,

Beena

Former Member
0 Kudos

Hi Beena

The XSLT code you gave is working. Thanks.

I have another question : I plan to put this mapping step as the first step in my operation mapping. I want to send the output of this xslt mapping to the next mapping - which will be a graphical mapping to create the idocs.

Is this possible and how do send the output of first mapping step to second mapping step.

Please note that my operation mapping has one service interface, 2 mapping programs - first one the XSLT mapping, and second one graphical mapping.

Former Member
0 Kudos

Hi Girish,

That is possible. You just have to add another row in operation mapping and add graphical mapping there. The output of xslt mapping will become input for graphical mapping.

Regards,

Beena

Former Member
0 Kudos

Hi Beena

One more help - in your xslt, while copying the node to the target can I change its name?

So can I change DT_HEADER_01 to DT_HEADER_01_INTER.

Appreciate if you let me know how to do it.

Former Member
0 Kudos

Hi Girish,

copy-of will not work in that case. May be apply-templates will work.

Regards,

Beena

Former Member
0 Kudos

Hi Girish,

Sorry for earlier reply , I misunderstood your requirement.

Yes, it would work, simply change DT_HEADER_01 to  DT_HEADER_01_INTER.

<?xml version="1.0" encoding="ISO-8859-1" ?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ns="XXXXXXXXXXXXXXXXXXXXXXx">

  <xsl:output method="xml" />

<xsl:template match="/">

<ns:MT_FIDCC2_FILE_TO_PI xmlns:ns="XXXXXXXXXXXXXXXXXXXXXXx">
<xsl:copy-of select="//DT_FILEHDR_00"/>

<xsl:apply-templates select="//DT_HEADER_01">

</xsl:apply-templates>

</ns:MT_FIDCC2_FILE_TO_PI>


</xsl:template>

<xsl:template match="DT_HEADER_01">
<DT_HEADER_01_INTER>
<xsl:apply-templates/>
<xsl:variable name="ref" >
<xsl:value-of select="./Reference"/>
</xsl:variable>
<xsl:copy-of select="/ns:MT_FIDCC2_FILE_TO_PI/DT_VNDRLINE_02[Reference=$ref]"/>
<xsl:copy-of select="/ns:MT_FIDCC2_FILE_TO_PI/DT_VNDRBNK_03[Reference=$ref]"/>
<xsl:copy-of select="/ns:MT_FIDCC2_FILE_TO_PI/DT_GLLINE_04[Reference=$ref]"/>

</DT_HEADER_01_INTER>

</xsl:template>

  <xsl:template match="@* | node()">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>


</xsl:stylesheet>

Regards,

Beena