Eionet
Conversion and Quality Assessment Service
View stylesheet
| XML schema |
http://www.eionet.europa.eu/schemas/ippc-wi/dir20081elvbat_schema.xsd
|
|---|---|
| Output type | SQL |
| Description | MySQL conversion |
| XSL file | dir20081elvbat_sql.xsl (Last modified: 08 Dec 2009 13:34 ) |
<?xml version="1.0" encoding="utf-8"?>
<!-- $Id: dir20081elvbat_sql.xsl 246 2009-08-03 16:35:36Z jeanyr $ -->
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html" encoding="utf-8" />
<xsl:param name="xml_folder_uri" />
<xsl:variable name="directiveid">20081elvbat</xsl:variable>
<xsl:variable name="directiveidfull" select="concat('Quest', $directiveid, '-')" />
<xsl:variable name="labels" select="document(concat($xml_folder_uri,concat('dir', $directiveid, '_xsl_labels.xml')))/labels/itemset[@xml:lang='en']" />
<xsl:template match="text()" />
<!--
Create tables and top-level response row
-->
<xsl:template match="Questionnaire">
<html>
<body>
<pre>
-- Warning: MySQL oriented SQL
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Table structure for table `response`
--
CREATE TABLE IF NOT EXISTS `response` (
`response_id` int(11) NOT NULL auto_increment,
`directive_id` varchar(50) collate utf8_unicode_ci NOT NULL,
`member_state` text collate utf8_unicode_ci NOT NULL,
`response_lang` varchar(50) collate utf8_unicode_ci NOT NULL,
`date_imported` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`response_id`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
CREATE TABLE IF NOT EXISTS `installation` (
`installation_id` int(11) NOT NULL auto_increment,
`response_id` int(11) NOT NULL,
`status` varchar(50) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`installation_id`),
INDEX (`response_id`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
--
-- Table structure for table `answer`
--
CREATE TABLE IF NOT EXISTS `answer` (
`answer_id` int(11) NOT NULL auto_increment,
`installation_id` int(11) NOT NULL,
`question_id` varchar(255) collate utf8_unicode_ci NOT NULL,
`answer` longtext collate utf8_unicode_ci NOT NULL,
PRIMARY KEY `id` (`answer_id`),
INDEX (`installation_id`),
INDEX (`question_id`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
--
-- Table structure for table `question`
--
CREATE TABLE IF NOT EXISTS `question` (
`question_id` varchar(255) collate utf8_unicode_ci NOT NULL,
`response_id` int(11) NOT NULL,
`question` longtext collate utf8_unicode_ci NOT NULL,
`question_lang` varchar(50) collate utf8_unicode_ci NOT NULL,
`date_imported` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY `question_id` (`question_id`,`question_lang`),
INDEX (`response_id`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- Response row
INSERT INTO `response` (`directive_id` , `member_state`, `response_lang`) VALUES ('<xsl:value-of select="$directiveid" />', 'Enter Member State name here', '<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="@xml:lang" />
</xsl:call-template>');
SET @response_id = LAST_INSERT_ID();
<xsl:apply-templates />
</pre>
</body>
</html>
</xsl:template>
<!--
Add Installations rows - for each one, also insert answers
-->
<xsl:template match="installation-combustion-row">
<xsl:for-each select="*">
<!--
is there a better way to test for a change in parent than testing
for the first field in the dataset? current method is risky as field may not be sent?
-->
<xsl:if test="local-name() = 'Quest20081elvbat-q-test-checkbox-page1'">
INSERT INTO `installation` (`response_id`, `status`)
VALUES(@response_id,
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="../@status" />
</xsl:call-template>'
);
SET @installation_id = LAST_INSERT_ID();
</xsl:if>
<xsl:choose>
<xsl:when test="contains(local-name(), '-table')">
<xsl:call-template name="display_table" />
</xsl:when>
<xsl:otherwise>
<xsl:call-template name="display_standard" />
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</xsl:template>
<!-- Called by the match all and deals with standard question types -->
<xsl:template name="display_standard">
--
-- Data for answer/question: <xsl:value-of select="local-name()" />
--
INSERT INTO `answer` (`installation_id` , `question_id` , `answer`)
VALUES (
@installation_id,
<xsl:variable name="questionid" select="substring-after(local-name(),$directiveidfull)" />
<!-- question id -->
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="local-name()" />
</xsl:call-template>',
<!-- question response -->
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="text()" />
</xsl:call-template>'
);
INSERT IGNORE INTO `question` (`question_id` , `response_id` , `question`, `question_lang`)
VALUES (
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="local-name()" />
</xsl:call-template>',
@response_id,
<xsl:choose>
<xsl:when test="$labels/item[@id=$questionid] != ''">
<!-- question text -->
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="$labels/item[@id=$questionid]" />
</xsl:call-template>',
</xsl:when>
<xsl:otherwise>
<!-- use question id as question text when there is no question text -->
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="local-name()" />
</xsl:call-template>',
</xsl:otherwise>
</xsl:choose>
<!-- use question id as question text when there is no question text -->
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="$labels/@xml:lang" />
</xsl:call-template>'
);
</xsl:template>
<!-- Called by the match all and deals with questions within tables/rows -->
<xsl:template name="display_table">
<xsl:variable name="tablename" select="substring-before(local-name(), '-table')" />
--
-- Table structure for table: answer_<xsl:value-of select="$tablename" />
--
CREATE TABLE IF NOT EXISTS `answer_<xsl:value-of select="$tablename" />` (
`answer_id` int(11) NOT NULL auto_increment,
`installation_id` int(11) NOT NULL,
`question_id` varchar(255) collate utf8_unicode_ci NOT NULL,
`row_id` varchar(255) collate utf8_unicode_ci NOT NULL,
`answer` longtext collate utf8_unicode_ci NOT NULL,
PRIMARY KEY `id` (`answer_id`),
INDEX (`installation_id`),
INDEX (`question_id`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
--
-- Data for table answer/question: <xsl:value-of select="$tablename" />
--
<xsl:for-each select="*">
<xsl:variable name="rowid" select="concat(local-name(), position())" />
<xsl:for-each select="*">
INSERT INTO `answer_<xsl:value-of select="$tablename" />` (`installation_id` , `question_id` , `row_id` , `answer`)
VALUES (
@installation_id,
<xsl:variable name="questionidfull" select="concat($directiveidfull, local-name())" />
<!-- question id -->
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="$questionidfull" />
</xsl:call-template>',
<!-- row id -->
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="$rowid" />
</xsl:call-template>',
<!-- question response -->
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="text()" />
</xsl:call-template>'
);
INSERT IGNORE INTO `question` (`question_id` , `response_id` , `question`, `question_lang`)
VALUES (
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="$questionidfull" />
</xsl:call-template>',
@response_id,
<xsl:variable name="questionid" select="local-name()" />
<xsl:choose>
<xsl:when test="$labels/item[@id=$questionid] != ''">
<!-- question text -->
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="$labels/item[@id=$questionid]" />
</xsl:call-template>',
</xsl:when>
<xsl:otherwise>
<!-- use question id as question text when there is no question text -->
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="local-name()" />
</xsl:call-template>',
</xsl:otherwise>
</xsl:choose>
<!-- use question id as question text when there is no question text -->
'<xsl:call-template name="display_sql_field">
<xsl:with-param name="field" select="$labels/@xml:lang" />
</xsl:call-template>'
);
</xsl:for-each>
</xsl:for-each>
</xsl:template>
<!-- Returns the question label or if no question label found it will return the question id -->
<xsl:template name="display_label_or_id">
<!-- Remove directive id -->
<xsl:variable name="questionid" select="local-name()" />
<!-- Get question label or id -->
<xsl:choose>
<xsl:when test="$labels/item[@id=$questionid] != ''">
<xsl:value-of select="$labels/item[@id=$questionid]" />
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="local-name()" />
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<!-- FUNCTIONS -->
<!-- Handles quote escaping for SQL
Code adapted from: http://www.stylusstudio.com/xsllist/200103/post60790.html
To handle SQL single quote escaping
-->
<xsl:template name="display_sql_field">
<xsl:param name="field" />
<xsl:variable name="apostrophe">
<xsl:text>'</xsl:text>
</xsl:variable>
<xsl:choose>
<xsl:when test="contains( $field, $apostrophe )">
<!-- Field contains a quote. We must enclose this field in quotes,
and we must escape each of the quotes in the field value.
-->
<xsl:call-template name="escape_quotes">
<xsl:with-param name="string" select="$field" />
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<!-- No need to enclose this field in quotes. -->
<xsl:value-of select="$field" />
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template name="escape_quotes">
<xsl:param name="string" />
<xsl:variable name="apostrophe">
<xsl:text>'</xsl:text>
</xsl:variable>
<xsl:value-of select="substring-before( $string, $apostrophe )" />
<xsl:text>\'</xsl:text>
<xsl:variable name="substring_after_first_quote" select="substring-after( $string, $apostrophe )" />
<xsl:choose>
<xsl:when test="not( contains( $substring_after_first_quote, $apostrophe ) )">
<xsl:value-of select="$substring_after_first_quote" />
</xsl:when>
<xsl:otherwise>
<!-- The substring after the first quote contains a quote.
So, we call ourself recursively to escape the quotes
in the substring after the first quote.
-->
<xsl:call-template name="escape_quotes">
<xsl:with-param name="string" select="$substring_after_first_quote" />
</xsl:call-template>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>