How to count elements what in date range in xslt 1.0?-CodePudding

i'm stuck a little bit, maybe someone can give me a hand.

So i have an xml, and i want to count those song what are in the date range. I have tried translate the date or substring it, but it still do not counting it.

My XML:

<?xml version="1.0" encoding="UTF-8"?>
<albums>
    <ALBUM>
        <closingDate>
            <DATE date="2021-10-09"/>
        </closingDate>
        <songsinalbum>
            <SONGSINALBUM>
                <song>
                    <SONG>
                        <songStatus>
                            <ESP_SONGSTATUS name="Prehistoric"/>
                        </songStatus>
                        <SONGType>
                            <ESP_SONGTYPE type="metal"/>
                        </SONGType>
                        <Date>
                            <ESP_DATE date="2021-10-09"/>
                        </Date>
                    </SONG>
                    <SONG>
                        <songStatus>
                            <ESP_SONGSTATUS name="Prehistoric"/>
                        </songStatus>
                        <SONGType>
                            <ESP_SONGTYPE type="metal"/>
                        </SONGType>
                        <Date>
                            <ESP_DATE date="2022-01-09"/>
                        </Date>
                    </SONG>
                    <SONG>
                        <songStatus>
                            <ESP_SONGSTATUS name="Expired"/>
                        </songStatus>
                        <SONGType>
                            <ESP_SONGTYPE type="metal"/>
                        </SONGType>
                        <Date/>
                    </SONG>
                    <SONG>
                        <songStatus>
                            <ESP_SONGSTATUS name="Available"/>
                        </songStatus>
                        <SONGType>
                            <ESP_SONGTYPE type="metal"/>
                        </SONGType>
                        <Date>
                            <ESP_DATE date="2022-03-20"/>
                        </Date>
                    </SONG>
                </song>
            </SONGSINALBUM>
        </songsinalbum>
    </ALBUM>
</albums>

My XSLT:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
    <xsl:output method="xml" encoding="UTF-8" indent="yes"/>

    <xsl:template match="/">
        <Workbook>
            <Styles>
                <Style ss:ID="mydefault" ss:Name="mydefault">
                    <Font ss:FontName="Verdana" ss:Bold="0"/>
                    <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
                </Style>
            </Styles>
            <Worksheet ss:Name="Report">
                <xsl:call-template name="ws_table"/>
            </Worksheet>
        </Workbook>
    </xsl:template>

    <xsl:template name="ws_table">
        <Table>
            <xsl:call-template name="headerrow"/>
            <xsl:apply-templates select="albums/ALBUM" mode="datarow"/>
        </Table>
    </xsl:template>

    <xsl:template name="headerrow">
        <Row>
            <!-- Date -->
            <Cell>
                <Data ss:Type="String">Date</Data>
            </Cell>
            <!-- test counting -->
            <Cell>
                <Data ss:Type="String">test counting</Data>
            </Cell>
            <!-- real count -->
            <Cell>
                <Data ss:Type="String">real count</Data>
            </Cell>
        </Row>
    </xsl:template>

    <xsl:template match="ALBUM" mode="datarow">

        <xsl:variable name="InRange" select="translate(songsinalbum/SONGSINALBUM/song/SONG[songStatus/ESP_SONGSTATUS/@name='Prehistoric'][SONGType/ESP_SONGTYPE/@type='metal']/Date/ESP_DATE/@date,'-','') &lt;= translate(closingDate/DATE/@date,'-','')"/>
        
        <xsl:variable name="formateddate" select="number(concat
            (substring(closingDate/DATE/@date, 1, 4),
            substring(closingDate/DATE/@date, 6, 2),
            substring(closingDate/DATE/@date, 9, 2)))
            &lt;= 
            number(concat
            (substring(songsinalbum/SONGSINALBUM/song/SONG[songStatus/ESP_SONGSTATUS/@name='Prehistoric'][SONGType/ESP_SONGTYPE/@type='metal']/Date/ESP_DATE/@date, 1, 4),
            substring(songsinalbum/SONGSINALBUM/song/SONG[songStatus/ESP_SONGSTATUS/@name='Prehistoric'][SONGType/ESP_SONGTYPE/@type='metal']/Date/ESP_DATE/@date, 6, 2),
            substring(songsinalbum/SONGSINALBUM/song/SONG[songStatus/ESP_SONGSTATUS/@name='Prehistoric'][SONGType/ESP_SONGTYPE/@type='metal']/Date/ESP_DATE/@date, 9, 2)))" />
        

        <Row>
            <!-- Date -->
            <Cell>
                <Data ss:Type="String">
                    <xsl:value-of select="closingDate/DATE/@date"/>
                </Data>
            </Cell>
            
            <!-- test counting -->
            <Cell>
                <Data ss:Type="String">
                    <xsl:value-of select="count(songsinalbum/SONGSINALBUM/song/SONG[songStatus/ESP_SONGSTATUS/@name='Prehistoric'][SONGType/ESP_SONGTYPE/@type='metal'])"/>
                </Data>                   
            </Cell>
            <!-- songs what is   -->
            <Cell>
                <Data ss:Type="String">
                    <xsl:value-of select="count($formateddate)"/>
                </Data>                   
            </Cell>
            
        </Row>
    </xsl:template>
    
</xsl:stylesheet>

The ouput should be like this: enter image description here

And ofc. the closing date can be changed, so if it like "2022-01-09", then the "real count" value should be "2".

So what i do wrong?

Thank you, Regards

CodePudding user response：

Consider the following minimized example:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/albums">
    <results>
        <xsl:for-each select="ALBUM">
            <xsl:variable name="close-date" select="translate(closingDate/DATE/@date, '-', '')" />
            <xsl:variable name="songs" select="songsinalbum/SONGSINALBUM/song/SONG" />
            <xsl:variable name="songs-before" select="$songs[translate(Date/ESP_DATE/@date, '-', '') &lt;= $close-date]" />
            <songs>
                <xsl:value-of select="count($songs)" />
            </songs>
            <songs-before>
                <xsl:value-of select="count($songs-before)" />
            </songs-before>
        </xsl:for-each>
    </results>
</xsl:template>

</xsl:stylesheet>

When applied to your XML example input, the result will be:

<?xml version="1.0" encoding="UTF-8"?>
<results>
  <songs>4</songs>
  <songs-before>1</songs-before>
</results>

If you change the closing date to 2022-01-09 then the result will be:

<?xml version="1.0" encoding="UTF-8"?>
<results>
  <songs>4</songs>
  <songs-before>2</songs-before>
</results>