<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 writes the book list to a SQL database -->

    <xsl:variable name="count" select="0" saxon:assignable="yes"/>

    <!-- 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:template match="CUSTOMERORDERS">

        <xsl:message>customer_insertupdate.xsl : Connecting to <xsl:value-of select="$database"/>...</xsl:message>

        <xsl:message>customer_insertupdate.xsl : Insert/Update  records....</xsl:message>
        <xsl:apply-templates select="CUSTOMERORDER" mode="UPDATE"/>    
        <xsl:message>customer_insertupdate.xsl : Inserted/Updated  records....</xsl:message>    

        <!-- This stylesheet closes the connection. -->

        <sql:close connection="$connection"/>

    </xsl:template>

    <xsl:template match="CUSTOMERORDER" mode="UPDATE">
     
        <!-- Updating / Inserting in CUSTOMER table. -->
        <xsl:for-each select="CUSTOMER">        
        
            <xsl:variable name="customerid" select="CUSTOMERID"/>
            <xsl:variable name="customer-table">
                <sql:query  connection="$connection" table="customer" where="CUSTOMERID='{$customerid}'" column="*" row-tag="CUSTOMERORDER" column-tag="col"/> 
            </xsl:variable>
            <xsl:if test="count($customer-table//CUSTOMERORDER) > 0">
                <sql:update table="customer" connection="$connection"  where="CUSTOMERID='{$customerid}'">
                    <sql:column name="CUSTOMERLASTNAME" select="CUSTOMERLASTNAME"/>
                    <sql:column name="CUSTOMERFIRSTNAME" select="CUSTOMERFIRSTNAME"/>
                    <sql:column name="CUSTOMEREMAIL" select="CUSTOMEREMAIL"/>
                </sql:update>
                <sql:update table="address" connection="$connection"  where="CUSTOMERID='{$customerid}'">
                    <sql:column name="STREET" select="BILLINGADDRESS/STREET"/>
                    <sql:column name="CITY" select="BILLINGADDRESS/CITY"/>
                    <sql:column name="STATE" select="BILLINGADDRESS/STATE"/>
                    <sql:column name="COUNTRY" select="BILLINGADDRESS/COUNTRY"/>
                    <sql:column name="ZIPCODE" select="BILLINGADDRESS/ZIPCODE"/>
                </sql:update>
            </xsl:if>
            <xsl:if test="count($customer-table//CUSTOMERORDER) = 0">
                <sql:insert table="customer" connection="$connection">
                    <sql:column name="CUSTOMERID" select="CUSTOMERID"/>
                    <sql:column name="CUSTOMERLASTNAME" select="CUSTOMERLASTNAME"/>
                    <sql:column name="CUSTOMERFIRSTNAME" select="CUSTOMERFIRSTNAME"/>
                    <sql:column name="CUSTOMEREMAIL" select="CUSTOMEREMAIL"/>
                </sql:insert>
                <sql:insert table="address" connection="$connection">
                    <sql:column name="CUSTOMERID" select="CUSTOMERID"/>
                    <sql:column name="STREET" select="BILLINGADDRESS/STREET"/>
                    <sql:column name="CITY" select="BILLINGADDRESS/CITY"/>
                    <sql:column name="STATE" select="BILLINGADDRESS/STATE"/>
                    <sql:column name="COUNTRY" select="BILLINGADDRESS/COUNTRY"/>
                    <sql:column name="ZIPCODE" select="BILLINGADDRESS/ZIPCODE"/>
                </sql:insert>
            </xsl:if>
        </xsl:for-each>
        
        <xsl:for-each select="CUSTORDER">
        
            <!-- Updating / Inserting in CUSTORDER table. -->
            <xsl:variable name="orderid" select="ORDERID"/>
            <xsl:variable name="order-table">
                <sql:query  connection="$connection" table="custorder" where="ORDERID='{$orderid}'" column="*" row-tag="CUSTORDER" column-tag="col"/> 
            </xsl:variable>
            <xsl:if test="count($order-table//CUSTORDER) > 0">
                <sql:update table="custorder" connection="$connection"  where="ORDERID='{$orderid}'">
                    <sql:column name="ORDERDATE" select="ORDERDATE"/>
                </sql:update>
                <sql:update table="address" connection="$connection"  where="ORDERID='{$orderid}'">
                    <sql:column name="STREET" select="SHIPPINGADDRESS/STREET"/>
                    <sql:column name="CITY" select="SHIPPINGADDRESS/CITY"/>
                    <sql:column name="STATE" select="SHIPPINGADDRESS/STATE"/>
                    <sql:column name="COUNTRY" select="SHIPPINGADDRESS/COUNTRY"/>
                    <sql:column name="ZIPCODE" select="SHIPPINGADDRESS/ZIPCODE"/>
                </sql:update>
            </xsl:if>
            <xsl:if test="count($order-table//CUSTORDER) = 0">
                <sql:insert table="custorder" connection="$connection">
                    <sql:column name="CUSTOMERID" select="./../CUSTOMER/CUSTOMERID"/>
                    <sql:column name="ORDERID" select="ORDERID"/>
                    <sql:column name="ORDERDATE" select="ORDERDATE"/>
                </sql:insert>
                <sql:insert table="address" connection="$connection">
                    <sql:column name="ORDERID" select="ORDERID"/>
                    <sql:column name="STREET" select="SHIPPINGADDRESS/STREET"/>
                    <sql:column name="CITY" select="SHIPPINGADDRESS/CITY"/>
                    <sql:column name="STATE" select="SHIPPINGADDRESS/STATE"/>
                    <sql:column name="COUNTRY" select="SHIPPINGADDRESS/COUNTRY"/>
                    <sql:column name="ZIPCODE" select="SHIPPINGADDRESS/ZIPCODE"/>
                </sql:insert>
            </xsl:if>
        
            <!-- Updating / Inserting in ORDERITEM table. -->
            <xsl:for-each select="ORDERITEM"> 
                <xsl:variable name="itemid" select="ITEMID"/>
                <xsl:variable name="orderitem-table">
                    <sql:query  connection="$connection" table="orderitem" where="ITEMID='{$itemid}'" column="*" row-tag="ORDERITEM" column-tag="col"/> 
                </xsl:variable>
                <xsl:if test="count($orderitem-table//ORDERITEM) > 0">
                    <sql:update table="orderitem" connection="$connection"  where="ITEMID='{$itemid}'">
                        <sql:column name="ITEMNUMBER" select="NUMBER"/>
                        <sql:column name="INSTRUCTIONS" select="INSTRUCTIONS"/>
                    </sql:update>
                </xsl:if>
                <xsl:if test="count($orderitem-table//ORDERITEM) = 0">
                    <sql:insert table="orderitem" connection="$connection">
                        <sql:column name="ORDERID" select="./../ORDERID"/>
                        <sql:column name="ITEMID" select="ITEMID"/>
                        <sql:column name="ITEMNUMBER" select="NUMBER"/>
                        <sql:column name="INSTRUCTIONS" select="INSTRUCTIONS"/>
                    </sql:insert>
                </xsl:if>
            </xsl:for-each>
        </xsl:for-each>
    </xsl:template>    
    
</xsl:stylesheet>