Run conversion
View stylesheet

XML schema	http://www.eionet.europa.eu/schemas/ippc-wi/dir200076_schema.xsd
Output type	SQL
Description	MySQL conversion
XSL file	dir200076_sql.xsl (Last modified: 10 Aug 2009 15:47 )
<?xml version="1.0" encoding="utf-8"?>
<!-- $Id: dir200076_html.xsl 216 2009-06-05 09:21:50Z sebf $ -->
<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">200076</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()"/>

    <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 ;

--
-- Table structure for table `answer`
--

CREATE TABLE IF NOT EXISTS `answer` (
  `answer_id` int(11) NOT NULL auto_increment,
  `response_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`)
) 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`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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>

    <!-- Match all and deal with the correct data type based on the name -->
    <xsl:template match="*">
        <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: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` (`response_id` , `question_id` , `answer`)
VALUES (
@response_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,
  `response_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`)
) 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"/>` (`response_id` , `question_id` , `row_id` , `answer`)
VALUES (
@response_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>

    <!-- 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>