I am reaching you regarding a challenge I am facing issue while attempting to iterate over and XSLT transformation. I have the following XML structure and I am trying to get the values AUFNR, BDMNG, MATNR, MEINS, BDMNG, VORNR.
<IDOC BEGIN="1">
<E1AFKOL SEGMENT="1">
<AUFNR>200000345</AUFNR>
<BMENGE>16.000</BMENGE>
<E1AFFLL SEGMENT="1">
<APLZL>00000001</APLZL>
<FLGAT>0</FLGAT>
<PLNFL>0</PLNFL>
<E1AFVOL SEGMENT="1">
<VORNR>0011</VORNR>
<E1RESBL SEGMENT="1">
<BDMNG>16.000</BDMNG>
<MATNR>0228737A00</MATNR>
<MEINS>PCE</MEINS>
<BDMNG>17.000</BDMNG>
<MATNR>0228737A01</MATNR>
<MEINS>PCE</MEINS>
</E1RESBL>
</E1AFVOL>
<E1AFVOL SEGMENT="1">
<VORNR>0021</VORNR>
<E1RESBL SEGMENT="1">
<BDMNG>48.000</BDMNG>
<MATNR>070106293</MATNR>
<MEINS>PCE</MEINS>
</E1RESBL>
</E1AFVOL>
<E1AFVOL SEGMENT="1">
<VORNR>0026</VORNR>
</E1AFVOL>
<E1AFVOL SEGMENT="1">
<VORNR>0027</VORNR>
</E1AFVOL>
</E1AFFLL>
</E1AFKOL>
</IDOC>
</LOIPRO04>
Specially I am encountering difficulties with the iteration to get the VORNR value.
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="/">
<RESULT>
<BOM_COMPONENTS>
<xsl:for-each select="/LOIPRO04/IDOC/E1AFKOL/E1AFFLL/E1AFVOL">
<xsl:for-each select="/LOIPRO04/IDOC/E1AFKOL/E1AFFLL/E1AFVOL/E1RESBL">
('<xsl:value-of select="normalize-space(MATNR)" />',
'<xsl:value-of select="normalize-space(/LOIPRO04/IDOC/E1AFKOL/AUFNR)"/>',
'<xsl:value-of select="normalize-space(/LOIPRO04/IDOC/E1AFKOL/BMENGE div BDMNG)" />',
'<xsl:value-of select="normalize-space(/LOIPRO04/IDOC/E1AFKOL/E1AFFLL/E1AFVOL/VORNR)" />',
'<xsl:value-of select="normalize-space(BDMNG)" />',
1,
GETDATE()
)
<xsl:if test="position()!=last()">,</xsl:if>
</xsl:for-each>
</xsl:for-each>
</BOM_COMPONENTS>
</RESULT>
</xsl:template>
</xsl:stylesheet>
I want to archieve the following result, is arranged to be the input of a SQL insert
('0228737A00',
'200000345',
'1',
'0011',
'16.000',
1,
GETDATE()
)
,
('0228737A01',
'200000345',
'0.9411764705882353',
'0011',
'17.000',
1,
GETDATE()
)
,
('070106293',
'200000345',,
'0.3333333333333333',
'0021',
'48.000',
1,
GETDATE()
)
Could someone please help me with this? Many thanks in advance!
This is quite a puzzle, what with the cryptic element names and the lack of wrapping elements for the intended records.
After a lot of head scratching, I think I managed to get close to the wanted result with:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="UTF-8"/>
<xsl:template match="/LOIPRO04">
<xsl:for-each select="/LOIPRO04/IDOC/E1AFKOL">
<xsl:variable name="AUFNR" select="AUFNR"/>
<xsl:variable name="BMENGE" select="BMENGE"/>
<xsl:for-each select="E1AFFLL/E1AFVOL/E1RESBL/BDMNG">
<xsl:text>( </xsl:text>
<!-- MATNR -->
<xsl:text>'</xsl:text>
<xsl:value-of select="following-sibling::MATNR[1]" />
<xsl:text>', </xsl:text>
<!-- AUFNR -->
<xsl:text>'</xsl:text>
<xsl:value-of select="$AUFNR" />
<xsl:text>', </xsl:text>
<!-- BMENGE/BDMNG -->
<xsl:text>'</xsl:text>
<xsl:value-of select="$BMENGE div ." />
<xsl:text>', </xsl:text>
<!-- VORNR -->
<xsl:text>'</xsl:text>
<xsl:value-of select="../../VORNR" />
<xsl:text>', </xsl:text>
<!-- BDMNG -->
<xsl:text>'</xsl:text>
<xsl:value-of select="." />
<xsl:text>', </xsl:text>
<!-- constant -->
<xsl:text>1, GETDATE() )</xsl:text>
<xsl:if test="position()!=last()">,</xsl:if>
<xsl:text> </xsl:text>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
With the provided input, this gets me:
(
'0228737A00',
'200000345',
'1',
'0011',
'16.000',
1,
GETDATE()
),
(
'0228737A01',
'200000345',
'0.9411764705882353',
'0011',
'17.000',
1,
GETDATE()
),
(
'070106293',
'200000345',
'0.3333333333333333',
'0021',
'48.000',
1,
GETDATE()
)