<xsl:stylesheet xmlns:sql="java:/net.sf.saxon.sql.SQLElementFactory" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0" xmlns:saxon="http://saxon.sf.net/" extension-element-prefixes="saxon sql">

    <!-- insert your database details here, or supply them in parameters -->
    <xsl:param name="driver" select="'oracle.jdbc.driver.OracleDriver'"/>
    <xsl:param name="database" select="'jdbc:oracle:thin:@127.0.0.1:1521:orcl'"/> 
    <xsl:param name="user">scott</xsl:param>
    <xsl:param name="password">tiger</xsl:param>

    <!--xsl:param name="driver" select="'com.pointbase.jdbc.jdbcUniversalDriver'"/>
    <xsl:param name="database" select="'jdbc:pointbase:server://127.0.0.1:9092/sample'"/> 
    <xsl:param name="user">PBPUBLIC</xsl:param>
    <xsl:param name="password">PBPUBLIC</xsl:param-->


    <!-- This stylesheet creates a global connection variable. -->

    <xsl:variable name="connection" as="java:java.sql.Connection" xmlns:java="http://saxon.sf.net/java-type">
	<sql:connect driver="{$driver}" database="{$database}" user="{$user}" password="{$password}" xsl:extension-element-prefixes="sql"/>
    </xsl:variable>

    <xsl:output method="xml" indent="yes"/>

    <xsl:template match="CUSTOMERORDERS">

        <xsl:message>customer_query.xsl : Connecting to <xsl:value-of select="$database"/>...</xsl:message>

        <xsl:message>customer_query.xsl : query  records....</xsl:message>
        <xsl:apply-templates select="CUSTOMERORDER" mode="Query"/>    

        <!-- This stylesheet closes the connection. -->

        <sql:close connection="$connection"/>

    </xsl:template>

    <xsl:template match="CUSTOMERORDER" mode="Query">

        <xsl:variable name="orderid" select="CUSTORDER/ORDERID"/>

	<xsl:variable name="orderitem-table">
		<sql:query  connection="$connection" table="ORDERITEM" where="ORDERID='{$orderid}'" column="*" row-tag="ORDERITEM" column-tag="col"/> 
	</xsl:variable>
	
	<xsl:message>There are now <xsl:value-of select="count($orderitem-table//ORDERITEM)"/> orderitems.</xsl:message>

	<ORDER>
		<xsl:copy-of select="$orderitem-table"/>
	</ORDER>
	
        <sql:close connection="$connection"/>

    </xsl:template>
    
</xsl:stylesheet>
