How to iterate XSL and get nodes

Question

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!

Answer 1

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>(&#10;</xsl:text>
            <!-- MATNR -->
            <xsl:text>'</xsl:text>
            <xsl:value-of select="following-sibling::MATNR[1]" />
            <xsl:text>',&#10;</xsl:text>
            <!-- AUFNR -->
            <xsl:text>'</xsl:text>
            <xsl:value-of select="$AUFNR" />
            <xsl:text>',&#10;</xsl:text>
            <!-- BMENGE/BDMNG -->
            <xsl:text>'</xsl:text>
            <xsl:value-of select="$BMENGE div ." />
            <xsl:text>',&#10;</xsl:text>
            <!-- VORNR -->
            <xsl:text>'</xsl:text>
            <xsl:value-of select="../../VORNR" />
            <xsl:text>',&#10;</xsl:text>
            <!-- BDMNG -->
            <xsl:text>'</xsl:text>
            <xsl:value-of select="." />
            <xsl:text>',&#10;</xsl:text>
            <!-- constant -->
            <xsl:text>1,&#10;GETDATE()&#10;)</xsl:text>
            <xsl:if test="position()!=last()">,</xsl:if>
            <xsl:text>&#10;</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()
)