View stylesheet

XML schemas http://dd.eionet.europa.eu/schemas/eu-ets-article21-1.1/Article21Questionnaire.xsd
Output type SQL
Description SQL inserts for MS Access
XSL file article21-sql-1.1.xsl (Last modified: 22 Apr 2015 14:59 )
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:xs="http://www.w3.org/2001/XMLSchema"
                xmlns="http://www.w3.org/1999/XSL/Transform" xmlns:local="local">

    <xsl:output method="text" />

    <xsl:param name="envelopeurl" select="''"/>
    <xsl:param name="releasetime" select="''"/>
    <xsl:param name="filename" />
    <xsl:param name="isreleased" select="'1'"/>
    <xsl:param name="countrycode" />
    <xsl:param name="commandline" select="'false'"/>

    <!--
    <xsl:param name="envelopeurl" select="'http://cdrtest.eionet.europa.eu/ee/eu/coluyk36g/envuylpda'"/>
    <xsl:param name="releasetime" select="substring-before(string(current-dateTime()), '.')"/>
    <xsl:param name="envelopeurl" select="''"/>
    <xsl:param name="releasetime" select="''"/>
    -->

    <xsl:variable name="schema" select="document('http://dd.eionet.europa.eu/schemas/eu-ets-article21-1.1/Article21Questionnaire.xsd')/xs:schema"/>
    <xsl:variable name="codelistTypes" select="$schema//xs:simpleType[count(xs:restriction/xs:enumeration)>0]/@name"/>

    <xsl:variable name="envelopeXml" select="document(concat($envelopeurl, '/xml'))"/>

    <xsl:variable name="reportid" select="concat($envelopeurl, '/', $filename , '#', $releasetime)"/>

    <xsl:template match="Article21Questionnaire">
        <xsl:variable name="xmlRoot" select="."/>
        <xsl:variable name="country" >
            <xsl:choose>
                <xsl:when test="string-length($countrycode) > 0"><xsl:value-of select="$countrycode"/></xsl:when>
                <xsl:otherwise><xsl:value-of select="upper-case($envelopeXml/envelope/countrycode)"/></xsl:otherwise>
            </xsl:choose>
        </xsl:variable>
<xsl:text>DELETE FROM tbl_report WHERE report_id=</xsl:text><xsl:call-template name="string"><xsl:with-param name="value" select="$reportid"/></xsl:call-template><xsl:text>;</xsl:text>
        <xsl:call-template name="statementSeparator"/>
        <xsl:text>INSERT INTO tbl_report (report_id, country, report_year, report_submissiondate, envelope_url, filename, envelope_isreleased, language, official_report) VALUES (</xsl:text>
        <xsl:call-template name="string"><xsl:with-param name="value" select="$reportid"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="string"><xsl:with-param name="value" select="$country"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="number"><xsl:with-param name="value" select="@year"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="datetime"><xsl:with-param name="value" select="$releasetime"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="string"><xsl:with-param name="value" select="$envelopeurl"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="string"><xsl:with-param name="value" select="$filename"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="string"><xsl:with-param name="value" select="$isreleased"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="string"><xsl:with-param name="value" select="@xml:lang"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="boolean"><xsl:with-param name="value" select="@officialReport"/></xsl:call-template><xsl:text></xsl:text>
        <xsl:text>);</xsl:text>
        <xsl:call-template name="statementSeparator"/>
        <xsl:text>UPDATE tbl_report SET most_recent_report = 1 WHERE country='</xsl:text>
        <xsl:value-of select="$country"/>
        <xsl:text>' and report_year=</xsl:text>
        <xsl:value-of select="@year"/>
        <xsl:text> and official_report=</xsl:text>
        <xsl:call-template name="boolean"><xsl:with-param name="value" select="@officialReport"/></xsl:call-template><xsl:text></xsl:text>
        <xsl:text> and report_submissiondate IN (SELECT TOP 1 report_submissiondate FROM tbl_report WHERE country='</xsl:text>
        <xsl:value-of select="$country"/>
        <xsl:text>' and report_year=</xsl:text>
        <xsl:value-of select="@year"/>
        <xsl:text> and official_report=</xsl:text>
        <xsl:call-template name="boolean"><xsl:with-param name="value" select="@officialReport"/></xsl:call-template><xsl:text></xsl:text>
        <xsl:text> order by report_submissiondate  DESC);</xsl:text>
        <xsl:call-template name="statementSeparator"/>
        <xsl:text>UPDATE tbl_report SET most_recent_report = 0 WHERE country='</xsl:text>
        <xsl:value-of select="$country"/>
        <xsl:text>' and report_year=</xsl:text>
        <xsl:value-of select="@year"/>
        <xsl:text> and official_report=</xsl:text>
        <xsl:call-template name="boolean"><xsl:with-param name="value" select="@officialReport"/></xsl:call-template><xsl:text></xsl:text>
        <xsl:text> and report_submissiondate NOT IN (SELECT TOP 1 report_submissiondate FROM tbl_report WHERE country='</xsl:text>
        <xsl:value-of select="$country"/>
        <xsl:text>' and report_year=</xsl:text>
        <xsl:value-of select="@year"/>
        <xsl:text> and official_report=</xsl:text>
        <xsl:call-template name="boolean"><xsl:with-param name="value" select="@officialReport"/></xsl:call-template><xsl:text></xsl:text>
        <xsl:text> order by report_submissiondate  DESC);</xsl:text>
        <xsl:call-template name="statementSeparator"/>

        <xsl:for-each select="$schema/xs:element[@name='Article21Questionnaire']/xs:complexType/xs:sequence/xs:element">
            <xsl:variable name="qName" select="@name"/>
            <xsl:for-each select="xs:complexType/xs:sequence/xs:element">
                <xsl:variable name="subQName" select="@name"/>
                <xsl:variable name="subQType" select="@type"/>
                <xsl:variable name="xmlSubQuestion" select="$xmlRoot/child::*[local-name()=$qName]/child::*[local-name()=$subQName]"/>
                <xsl:choose>
                    <xsl:when test="string-length($subQType) > 0 and count(child::*) = 0">
                        <!-- simple field in sub-question level -->
                        <xsl:call-template name="insertValue">
                            <xsl:with-param name="question" select="$qName"/>
                            <xsl:with-param name="subQuestion" select="$subQName"/>
                            <xsl:with-param name="fieldName" select="$subQName"/>
                            <xsl:with-param name="schemaDataType" select="$subQType"/>
                            <xsl:with-param name="fieldValue" select="$xmlSubQuestion"/>
                        </xsl:call-template>
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:for-each select="xs:complexType/xs:sequence/xs:element">
                            <xsl:variable name="subQFieldName" select="@name"/>
                            <xsl:variable name="subQFieldType" select="@type"/>
                            <xsl:variable name="xmlSubQuestionField" select="$xmlSubQuestion/child::*[local-name()=$subQFieldName]"/>
                            <xsl:choose>
                                <xsl:when test="string-length($subQFieldType) > 0 and count(child::*) = 0">
                                    <!-- simple field in sub-question field level eg. Q1 -->
                                    <xsl:call-template name="insertValue">
                                        <xsl:with-param name="question" select="$qName"/>
                                        <xsl:with-param name="subQuestion" select="$subQName"/>
                                        <xsl:with-param name="fieldName" select="$subQFieldName"/>
                                        <xsl:with-param name="schemaDataType" select="$subQFieldType"/>
                                        <xsl:with-param name="fieldValue" select="$xmlSubQuestionField"/>
                                    </xsl:call-template>
                                </xsl:when>
                                <xsl:otherwise>
                                    <xsl:variable name="rowDef" select="."/>
                                    <xsl:variable name="isOtherRow">
                                        <xsl:choose>
                                            <xsl:when test="starts-with($rowDef/@name, 'Other')">1</xsl:when>
                                            <xsl:otherwise>0</xsl:otherwise>
                                        </xsl:choose>
                                    </xsl:variable>
                                    <!-- repeating element - QuestionRow, FixedRow, ResultRow, OtherPleaseSpecify-->
                                    <xsl:for-each select="$xmlSubQuestionField">
                                        <xsl:variable name="pos" select="position()"/>
                                        <xsl:variable name="xmlRow" select="."/>
                                        <!-- ignore empty rows -->
                                        <xsl:if test="string-length(normalize-space($xmlRow)) > 0">
                                            <xsl:for-each select="$rowDef/xs:complexType/xs:sequence/xs:element">
                                                <xsl:variable name="rowFieldName" select="@name"/>
                                                <xsl:variable name="rowFieldType" select="@type"/>
                                                <xsl:variable name="xmlRowFieldValue"
                                                              select="$xmlRow/child::*[local-name()=$rowFieldName]"/>
                                                <xsl:call-template name="insertValue">
                                                    <xsl:with-param name="question" select="$qName"/>
                                                    <xsl:with-param name="subQuestion" select="$subQName"/>
                                                    <xsl:with-param name="fieldName" select="$rowFieldName"/>
                                                    <xsl:with-param name="schemaDataType" select="$rowFieldType"/>
                                                    <xsl:with-param name="fieldValue" select="$xmlRowFieldValue"/>
                                                    <xsl:with-param name="rowNum" select="$pos"/>
                                                    <xsl:with-param name="rowOther" select="$isOtherRow"/>
                                                </xsl:call-template>
                                            </xsl:for-each>
                                        </xsl:if>
                                    </xsl:for-each>
                                </xsl:otherwise>
                            </xsl:choose>
                        </xsl:for-each>
                    </xsl:otherwise>
                </xsl:choose>


            </xsl:for-each>
        </xsl:for-each>
        <xsl:text>
        </xsl:text>
    </xsl:template>

    <xsl:template name="insertValue">
        <xsl:param name="question"/>
        <xsl:param name="subQuestion"/>
        <xsl:param name="fieldName"/>
        <xsl:param name="rowNum" select="0"/>
        <xsl:param name="schemaDataType"/>
        <xsl:param name="fieldValue"/>
        <xsl:param name="rowOther" select="0"/>

        <xsl:variable name="datatype">
            <xsl:choose>
                <xsl:when test="starts-with($schemaDataType, 'Integer')">integer</xsl:when>
                <xsl:when test="starts-with($schemaDataType, 'Decimal')">decimal</xsl:when>
                <xsl:when test="starts-with($schemaDataType, 'Memo')">memo</xsl:when>
                <xsl:when test="starts-with($schemaDataType, 'YesNo')">yesno</xsl:when>
                <xsl:when test="count($schema//xs:simpleType[count(xs:restriction/xs:enumeration)>0 and @name=$schemaDataType]) >0 ">codelist</xsl:when>
                <xsl:otherwise>text</xsl:otherwise>
            </xsl:choose>
        </xsl:variable>
        <xsl:variable name="codeLabel">
            <xsl:choose>
                <xsl:when test="$datatype ='codelist' or $datatype='yesno'">
                    <xsl:value-of select="$schema/xs:simpleType[@name = $schemaDataType]//xs:enumeration[@value = $fieldValue]/xs:annotation/xs:documentation"/>
                </xsl:when>
                <xsl:otherwise></xsl:otherwise>
            </xsl:choose>
        </xsl:variable>
        <xsl:text>INSERT INTO tbl_report_value (report_id, question, sub_question, row_id, row_other, field_name, datatype, value_text, value_int, value_decimal, value_memo, codelist, code_label) VALUES (</xsl:text>
        <xsl:call-template name="string"><xsl:with-param name="value" select="$reportid"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="string"><xsl:with-param name="value" select="$question"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="string"><xsl:with-param name="value" select="$subQuestion"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="number"><xsl:with-param name="value" select="$rowNum"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="number"><xsl:with-param name="value" select="$rowOther"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="string"><xsl:with-param name="value" select="$fieldName"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="string"><xsl:with-param name="value" select="$datatype"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:call-template name="string"><xsl:with-param name="value" select="$fieldValue"/><xsl:with-param name="isNode" select="'true'"/></xsl:call-template><xsl:text>,</xsl:text>
        <xsl:choose>
            <xsl:when test="$datatype='integer'">
                <xsl:call-template name="integer"><xsl:with-param name="value" select="$fieldValue"/></xsl:call-template>
            </xsl:when>
            <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose><xsl:text>,</xsl:text>
        <xsl:choose>
            <xsl:when test="$datatype='decimal'">
                <xsl:call-template name="decimal"><xsl:with-param name="value" select="$fieldValue"/></xsl:call-template>
            </xsl:when>
            <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose><xsl:text>,</xsl:text>
        <xsl:choose>
            <xsl:when test="$datatype='memo' or string-length($fieldValue) > 255">
                <xsl:call-template name="string"><xsl:with-param name="value" select="$fieldValue"/><xsl:with-param
                        name="isMemo" select="'true'"/></xsl:call-template>
            </xsl:when>
            <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose><xsl:text>,</xsl:text>
        <xsl:choose>
            <xsl:when test="$datatype='codelist' or $datatype='yesno'">
                <xsl:text>'</xsl:text><xsl:value-of select="$schemaDataType"/><xsl:text>'</xsl:text>
            </xsl:when>
            <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose><xsl:text>,</xsl:text>
        <xsl:call-template name="string"><xsl:with-param name="value" select="$codeLabel"/></xsl:call-template>
        <xsl:text>);</xsl:text>
        <xsl:call-template name="statementSeparator"/>
    </xsl:template>

    <!-- COMMON templates -->

    <xsl:template name="string">
        <xsl:param name="value"/>
        <xsl:param name="isMemo" select="'false'"/>
        <xsl:param name="isNode" select="'false'"/>

        <xsl:variable name="strValue">
            <xsl:choose>
                <xsl:when test="$isNode='true' and count($value) > 1">
                    <xsl:for-each select="$value">
                        <xsl:value-of select="."/><xsl:if test="position() != last()">, </xsl:if>
                    </xsl:for-each>
                </xsl:when>
                <xsl:otherwise><xsl:value-of select="$value"/></xsl:otherwise>
            </xsl:choose>
        </xsl:variable>

        <xsl:variable name="value255">
            <xsl:choose>
                <xsl:when test="string-length($strValue) > 255 and $isMemo = 'false'"><xsl:value-of select="substring($strValue, 1, 255)"/></xsl:when>
                <xsl:otherwise><xsl:value-of select="$strValue"/></xsl:otherwise>
            </xsl:choose>
        </xsl:variable>

        <xsl:variable name="valueEscaped">
            <xsl:call-template name="globalReplace">
                <xsl:with-param name="text" select="$value255"/>
            </xsl:call-template>
        </xsl:variable>

        <xsl:variable name="outputStr">
            <xsl:choose>
                <xsl:when test="$value != ''">
                    <xsl:text>'</xsl:text>
                    <xsl:value-of select="$valueEscaped"/>
                    <xsl:text>'</xsl:text></xsl:when>
                <xsl:otherwise>NULL</xsl:otherwise>
            </xsl:choose>
        </xsl:variable>

        <!-- remove line break apostrophes from the start and end of strings. -->
        <xsl:call-template name="string-replace-all">
            <xsl:with-param name="text">
                <xsl:call-template name="string-replace-all">
                    <xsl:with-param name="text" select="$outputStr"/>
                    <xsl:with-param name="replace" select='"&amp; &apos;&apos;$"'/>
                    <xsl:with-param name="by" select="''"/>
                </xsl:call-template>
            </xsl:with-param>
            <xsl:with-param name="replace" select='"&apos;&apos; &amp;"'/>
            <xsl:with-param name="by" select="''"/>
        </xsl:call-template>
        <!--
        <xsl:value-of select='replace(replace($outputStr, "&amp; &apos;&apos;$", ""), "&apos;&apos; &amp;", "")'/>
        -->

    </xsl:template>

    <!-- Fixes quotes in database input strings -->
    <xsl:template name="globalReplace">
        <xsl:param name="text"/>
        <xsl:variable name="outputStr">
            <xsl:call-template name="string-replace-all">
                <xsl:with-param name="text">
                    <xsl:call-template name="string-replace-all">
                        <xsl:with-param name="text" select="$text"/>
                        <xsl:with-param name="replace" select='"&apos;"'/>
                        <xsl:with-param name="by" select='"&apos;&apos;"'/>
                    </xsl:call-template>
                </xsl:with-param>
                <xsl:with-param name="replace" select='"&#xA;"'/>
                <xsl:with-param name="by">
                    <xsl:choose>
                        <xsl:when test="$commandline='false'">&apos; &amp; Chr(10) &amp; Chr(13) &amp; &apos;</xsl:when>
                        <xsl:otherwise>&apos; + Char(13) + Char(10) + &apos;</xsl:otherwise>
                    </xsl:choose>
                </xsl:with-param>
            </xsl:call-template>
        </xsl:variable>
        <xsl:value-of select="$outputStr"/>
    </xsl:template>

    <xsl:template name="string-replace-all">
        <xsl:param name="text"/>
        <xsl:param name="replace"/>
        <xsl:param name="by"/>

        <xsl:choose>
            <xsl:when test='contains($text, $replace)'>
                <xsl:value-of select='concat(substring-before($text, $replace), $by)'/>
                <xsl:call-template name="string-replace-all">
                    <xsl:with-param name="text" select='substring-after($text, $replace)'/>
                    <xsl:with-param name="replace" select='$replace'/>
                    <xsl:with-param name="by" select='$by'/>
                </xsl:call-template>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="$text"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>

    <!-- format datetime for MS Access -->
    <xsl:template name="datetime">
        <xsl:param name="value"/>
        <xsl:choose>
            <xsl:when test="$value != ''">
                <xsl:text>'</xsl:text>
                <xsl:value-of select="translate($value, 'TZ', ' ')"/>
                <xsl:text>'</xsl:text></xsl:when>
            <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
    </xsl:template>

    <xsl:template name="number">
        <xsl:param name="value"/>
        <xsl:choose>
            <xsl:when test="number($value) = number($value)"><xsl:value-of select="$value"/></xsl:when>
            <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
    </xsl:template>

    <xsl:template name="integer">
        <xsl:param name="value"/>
        <xsl:choose>
            <xsl:when test="round($value) = number($value) and number($value) = number($value) and number($value) &lt; 2147483647"><xsl:value-of select="$value"/></xsl:when>
            <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
    </xsl:template>

    <xsl:template name="decimal">
        <xsl:param name="value"/>
        <xsl:choose>
            <xsl:when test="number($value) = number($value)"><xsl:value-of select="$value"/></xsl:when>
            <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
    </xsl:template>

    <xsl:variable name="true-values-array">
        <local:item>true</local:item>
        <local:item>sand</local:item>
        <local:item>vero</local:item>
        <local:item>wahr</local:item>
        <local:item>vrai</local:item>
        <local:item>yes</local:item>
    </xsl:variable>
    <xsl:param name="true-values" select="document('')/*/xsl:variable[@name='true-values-array']/*" />

    <xsl:variable name="false-values-array">
        <local:item>false</local:item>
        <local:item>falsk</local:item>
        <local:item>falskt</local:item>
        <local:item>falso</local:item>
        <local:item>falsch</local:item>
        <local:item>faux</local:item>
        <local:item>no</local:item>
    </xsl:variable>
    <xsl:param name="false-values" select="document('')/*/xsl:variable[@name='false-values-array']/*" />

    <xsl:template name="boolean">
        <xsl:param name="value"/>

        <xsl:variable name="lcletters">abcdefghijklmnopqrstuvwxyz</xsl:variable>
        <xsl:variable name="ucletters">ABCDEFGHIJKLMNOPQRSTUVWXYZ</xsl:variable>

        <xsl:choose>
            <xsl:when test="count($true-values[. = translate($value,$ucletters,$lcletters)]) > 0">TRUE</xsl:when>
            <xsl:when test="count($false-values[. = translate($value,$ucletters,$lcletters)]) > 0">FALSE</xsl:when>
            <xsl:when test="$value = 'true' or $value = 1">TRUE</xsl:when>
            <xsl:when test="$value = 'false' or $value = 0">FALSE</xsl:when>
            <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
    </xsl:template>

    <xsl:template name="statementSeparator">
        <xsl:if test="$commandline='false'">--</xsl:if>
        <xsl:text>
</xsl:text>
    </xsl:template>
    
</xsl:stylesheet>