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 14: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>&#39;</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>&#39;</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>