on 02-27-2013 10:26 PM
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>
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..
Hope it helps.!
Regards,
Sunil
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Thanks Beena. Your XSLT works - can make few changes that I need.
Many thanks.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 < $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() > $temp and position() < $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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.