Eionet
Conversion and Quality Assessment Service
View stylesheet
| XML schema |
http://dd.eionet.europa.eu/schemas/eu-ets-article21/Article21Questionnaire.xsd
|
|---|---|
| Output type | SQL |
| Description | SQL inserts for MS Access |
| XSL file | article21-sql.xsl (Last modified: 19 Jun 2014 12:29 ) |
<?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="'http://cdrtest.eionet.europa.eu/ee/eu/coluyk36g/envuylpda'"/>
<xsl:param name="releasetime" select="substring-before(string(current-dateTime()), '.')"/>
<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/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: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'">
<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:variable name="strValue">
<xsl:choose>
<xsl:when test="count($value) > 0">
<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:value-of select='replace(replace($outputStr, "& ''$", ""), "'' &", "")'/>
</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='"'"'/>
<xsl:with-param name="by" select='"''"'/>
</xsl:call-template>
</xsl:with-param>
<xsl:with-param name="replace" select='"
"'/>
<xsl:with-param name="by" select='"' & Chr(10) & Chr(13) & '"'/>
</xsl:call-template>
</xsl:variable>
<xsl:value-of select="$outputStr"/>
<!--
<xsl:variable name="outputStrFixed">
<xsl:choose>
<xsl:when
test='contains($outputStr, "Chr(13) & '") and normalize-space(substring-after($outputStr, "Chr(13) & '")) = ""'>
<xsl:value-of select="substring($outputStr, 0, string-length($outputStr) - 4)"/>
</xsl:when>
<xsl:otherwise>
<xsl:choose>
<xsl:when
test='contains($outputStr, "' & Chr(10)") and normalize-space(substring-before($outputStr, "' & Chr(10)")) = ""'>
<xsl:value-of select="substring($outputStr, 4)"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$outputStr"/>
</xsl:otherwise>
</xsl:choose>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
-->
</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) < 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>