<section><title>SQL Examples</title>
<section>
<para>
The java run time needs to be able to find the database driver.  
The examples in this section are set up for the Oracle driver, 
oracle.jdbc.driver.OracleDriver.  To configure ServingXML to recognize 
this driver, copy the Oracle classes12.jar file (not distributed) to 
the <filename>lib/local</filename> directory, and then rebuild.  Of course, you can specify a different driver, 
making the appropriate changes in the sample resources script and copying the right
jar file to the <filename>lib/local</filename> directory.
</para>
</section>
<section><title>SQL Query to Flat File</title>
<section>
<title>Writing the results of a SQL query to a CSV file (employees-csv)</title>
<titleabbrev>employees csv</titleabbrev>
<indexterm><primary>flat file writer</primary><secondary>default</secondary></indexterm>
<indexterm><primary>SQL query</primary></indexterm>
<para>
This example shows how to prepare a resources script that will write the results of a SQL query to a
CSV file, using a default flat file writer.
</para>
<para>
The desired CSV file is shown below.
</para>

<figure id="employees.csv">
  <title>Output XML file employees.csv</title>
<programlisting>
<![CDATA[
EMPNO,NAME,JOB
7902,FORD,ANALYST
7788,SCOTT,ANALYST
7876,ADAMS,CLERK
7900,JAMES,CLERK
7934,MILLER,CLERK
7369,SMITH,CLERK
]]></programlisting>
</figure>
<para>
The following resources script does the transformation.
</para>
<figure id="resources_employee_csv.xml">
  <title>Resources script</title>
<programlisting><![CDATA[
<sx:resources xmlns:sx="http://www.servingxml.com/core">

  <sx:service id="employees-to-csv">
    <sx:recordStream>
      <sx:recordReader ref="employees"/>
      <sx:flatFileWriter/>
    </sx:recordStream>
  </sx:service>

  <sx:sqlReader id="employees">
    <sx:sqlConnectionPool ref="jdbcPool"/>

    <sx:parameter name="jobList">
      <sx:toString value="{$job}" separator=",">
        <sx:quoteSymbol character="'" escapeCharacter="'"/>
      </sx:toString>
    </sx:parameter>

    <sx:sqlQuery recordType = "employee">
        SELECT EMPNO, ENAME AS NAME,JOB FROM EMP WHERE 1=1
      <sx:choose>
        <sx:when test="$jobList">
            AND JOB IN ({$jobList})
        </sx:when>
      </sx:choose>
        ORDER BY JOB,ENAME
    </sx:sqlQuery>

  </sx:sqlReader>

  <sx:sqlConnectionPool id="jdbcPool"
                        driver="oracle.jdbc.driver.OracleDriver"
                        databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev"
                        user="{$user}"
                        password="{$password}"
                        minConnections="2"
                        testStatement="SELECT * FROM DUAL"/>

</sx:resources>
]]></programlisting>
</figure>
<para>
Executing the <code>employees</code> service with two <code>job</code> parameters, like this
<programlisting>
<![CDATA[
    servingxml  -r resources-employees_csv.xml -o output/employees.csv employees-to-csv
         user=scott password=spring job=ANALYST job=CLERK
]]>
</programlisting>
results in the following SQL statement being sent to the Oracle server:
<programlisting>
<![CDATA[
SELECT EMPNO, ENAME AS NAME,JOB 
  FROM EMP 
  WHERE 1=1 AND JOB IN ('ANALYST','CLERK') 
  ORDER BY JOB,ENAME
]]></programlisting>
</para>
<para>
The attribute names EMPNO, NAME and JOB become the headings of the CSV file, and the 
values in each row are written out below.
</para>
</section>
</section>
<section><title>SQL Query to XML</title>
<section>
<title>Mapping the results of a parameterized SQL query into XML (analysts)</title>
<para>
The example below illustrates how to prepare a resources script that will convert the results of 
a SQL query into an XML document.  
</para>
<para>
</para>
<para>
The desired output is shown below.
<figure id="employees.xml">
  <title>Output XML file employees.xml</title>
<programlisting>
<![CDATA[
<?xml version="1.0" encoding="utf-8"?>
<employees>
  <employee employee-no="7788">
    <name>SCOTT</name>
    <job>ANALYST</job>
  </employee>
  <employee employee-no="7902">
    <name>FORD</name>
    <job>ANALYST</job>
  </employee>
</employees>
]]></programlisting>
</figure>
</para>
<para>
The following resources script does the transformation.
</para>
<figure id="sql-resources.xml">
  <title>Resources script</title>
<programlisting><![CDATA[
<sx:resources xmlns:sx="http://www.servingxml.com/core">                   
                                                                   
  <sx:service id="employees">                         
    <sx:serialize>
      <sx:content ref="employees"/>
    </sx:serialize>
  </sx:service>

  <sx:recordContent id="employees">
    <sx:sqlReader>
      <sx:sqlConnectionPool ref="jdbcPool"/>
      
      <sx:parameter name="jobList">
        <sx:toString value="{$job}" separator=",">
          <sx:quoteSymbol character="'" escapeCharacter="'"/>
        </sx:toString>
      </sx:parameter>
      
      <sx:sqlQuery recordType = "employee">
        SELECT EMPNO, ENAME AS NAME,JOB FROM EMP WHERE JOB = '{$job}' 
      </sx:sqlQuery>
        
    </sx:sqlReader>
    
    <sx:recordMapping ref="employeesToXml"/>
    
  </sx:recordContent>
  
  <sx:recordMapping id="employeesToXml">
    <employees>
      <sx:groupBy fields="JOB">
        <sx:elementMap element="{JOB}">
          <sx:onRecord>
            <employee>
              <sx:fieldAttributeMap field="EMPNO" attribute="employee-no"/>
              <sx:fieldElementMap field="NAME" element="name"/>
            </employee>
          </sx:onRecord>
        </sx:elementMap>
      </sx:groupBy>
    </employees>
  </sx:recordMapping>

  <sx:sqlConnectionPool id="jdbcPool"
                        driver="oracle.jdbc.driver.OracleDriver"
                        databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev"
                        user="{$user}"
                        password="{$password}"
                        minConnections="2"
                        testStatement="SELECT * FROM DUAL"/>
                         
  </sx:resources>
]]></programlisting>
</figure>
<para>
You can run this example on the command line by entering
<programlisting>
<![CDATA[
  servingxml -r resources.xml employees -o output/employees.txt employees 
       user=scott password=spring job=ANALYST
]]>
</programlisting>
</para>
</section>

<section>
<title>Mapping the results of an ad hoc SQL query into XML (employees)</title>
<para>
The example below illustrates how to prepare a resources script that will generate and execute 
a different SQL statement depending on passed parameters.
</para>
<para>
The desired output is shown below.
</para>

<figure id="adhoc-analysts.xml">
  <title>Output XML file employees.xml</title>
<programlisting>
<![CDATA[
<?xml version="1.0" encoding="utf-8"?>
<employees>
   <ANALYST>
      <employee employee-no="7902">
         <name>FORD</name>
      </employee>
      <employee employee-no="7788">
         <name>SCOTT</name>
      </employee>
   </ANALYST>
   <CLERK>
      <employee employee-no="7876">
         <name>ADAMS</name>
      </employee>
      <employee employee-no="7900">
         <name>JAMES</name>
      </employee>
      <employee employee-no="7934">
         <name>MILLER</name>
      </employee>
      <employee employee-no="7369">
         <name>SMITH</name>
      </employee>
   </CLERK>
</employees>
]]></programlisting>
</figure>
<para>
The following resources script does the transformation.
</para>
<figure id="resources.xml">
  <title>Resources script</title>
<programlisting><![CDATA[
<sx:resources xmlns:sx="http://www.servingxml.com/core">

  <sx:service id="employees">
    <sx:serialize>
      <sx:content ref="employeesDoc"/>
    </sx:serialize>
  </sx:service>

  <sx:recordContent id="employeesDoc">
    <sx:sqlReader>
      <sx:sqlConnectionPool ref="jdbcPool"/>
      
      <sx:parameter name="jobList">
        <sx:toString value="{$job}" separator=",">
          <sx:quoteSymbol character="'" escapeCharacter="'"/>
        </sx:toString>
      </sx:parameter>
      
      <sx:sqlQuery recordType = "employee">
        SELECT EMPNO, ENAME AS NAME,JOB FROM EMP WHERE 1=1 
        <sx:choose>
          <sx:when test="$jobList">
            AND JOB IN ({$jobList})
          </sx:when>
        </sx:choose>
        ORDER BY JOB,ENAME
      </sx:sqlQuery>
        
    </sx:sqlReader>
    
    <sx:recordMapping ref="employeesToXml"/>
    
  </sx:recordContent>
  
  <sx:recordMapping id="employeesToXml">
    <employees>
      <sx:groupBy fields="JOB">
        <sx:elementMap element="{JOB}">
          <sx:onRecord>
            <employee>
              <sx:fieldAttributeMap field="EMPNO" attribute="employee-no"/>
              <sx:fieldElementMap field="NAME" element="name"/>
            </employee>
          </sx:onRecord>
        </sx:elementMap>
      </sx:groupBy>
    </employees>
  </sx:recordMapping>

  <sx:sqlConnectionPool id="jdbcPool"
                         driver="oracle.jdbc.driver.OracleDriver"
                         databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev"
                         user="scott"
                         password="spring"
                         minConnections="2"
                         testStatement="SELECT * FROM DUAL"/>

</sx:resources>
]]></programlisting>
</figure>
<para>
Executing the <code>employees</code> service with two <code>job</code> parameters, like this
<programlisting>
<![CDATA[
    servingxml -r resources.xml employees -o output/employees.xml employees 
        job=ANALYST job=CLERK
]]>
</programlisting>
results in the following SQL statement being sent to the Oracle server:
<programlisting>
<![CDATA[
SELECT EMPNO, ENAME AS NAME,JOB 
  FROM EMP 
  WHERE 1=1 AND JOB IN ('ANALYST','CLERK') 
  ORDER BY JOB,ENAME
]]></programlisting>
</para>
<para>
But executing the <code>employees</code> service with no parameters results in a SQL select
statement that returns all employees for all job categories.
</para>
</section>
<section>
<title>Using multiple readers to join data across different data sources (chained readers)</title>
<indexterm><primary>SQL reader</primary><secondary>chained</secondary></indexterm>
<para>
The example below illustrates how to prepare a resources script that will execute 
two queries to two different databases, where the second is joined with the first.
</para>
<para>
The desired output is shown below.
</para>

<figure id="adhoc-analysts.xml">
  <title>Output XML file deptEmployees.xml</title>
<programlisting>
<![CDATA[
<?xml version="1.0" encoding="utf-8"?>
<employees>
  <department dept-no="10" dept-name="ACCOUNTING">
    <employee employee-no="7782">
      <name>CLARK</name>
    </employee>
    <employee employee-no="7839">
      <name>KING</name>
    </employee>
  </department>
  <department dept-no="30" dept-name="SALES">
    <employee employee-no="7499">
      <name>ALLEN</name>
    </employee>
    <employee employee-no="7698">
      <name>BLAKE</name>
    </employee>
  </department>
</employees>
]]></programlisting>
</figure>
<para>
The following resources script does the transformation.
</para>
<figure id="resources.xml">
  <title>Resources script</title>
<programlisting><![CDATA[
<sx:resources xmlns:sx="http://www.servingxml.com/core">

  <sx:service id="employees">
    <sx:serialize>
      <sx:content ref="employeesDoc"/>
    </sx:serialize>
  </sx:service>

  <sx:recordContent id="employeesDoc">
    <sx:recordStream>
      <sx:sqlReader>
        <sx:sqlConnectionPool ref="devPool"/>
        <sx:sqlQuery recordType = "employee">
        SELECT DEPTNO, DNAME FROM DEPT ORDER BY DNAME
        </sx:sqlQuery>
      </sx:sqlReader>
                                                                                            
      <!-- for each row from the previous reader, run this reader -->
      <sx:sqlReader>
        <sx:sqlConnectionPool ref="dev2Pool"/>
        <sx:sqlQuery recordType = "employee">
        SELECT EMPNO, ENAME AS NAME,JOB, {DEPTNO} AS DEPTNO, '{DNAME}' AS DEPT FROM EMP  WHERE DEPTNO = {DEPTNO}
        ORDER BY ENAME
        </sx:sqlQuery>
      </sx:sqlReader>
    </sx:recordStream>

    <sx:recordMapping ref="employeesToXml"/>

  </sx:recordContent>

  <sx:recordMapping id="employeesToXml">
    <employees>
      <sx:groupBy fields="DEPT">
        <department>
          <sx:fieldAttributeMap field="DEPTNO" attribute="dept-no"/>
          <sx:fieldAttributeMap field="DEPT" attribute="dept-name"/>
          <sx:onRecord>
            <employee>
              <sx:fieldAttributeMap field="EMPNO" attribute="employee-no"/>
              <sx:fieldElementMap field="NAME" element="name"/>
            </employee>
          </sx:onRecord>
        </department>
      </sx:groupBy>
    </employees>
  </sx:recordMapping>

  <sx:sqlConnectionPool id="devPool"
                        driver="oracle.jdbc.driver.OracleDriver"
                        databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev"
                        user="scott"
                        password="spring"
                        minConnections="2"
                        testStatement="SELECT * FROM DUAL"/>

  <sx:sqlConnectionPool id="dev2Pool"
                        driver="oracle.jdbc.driver.OracleDriver"
                        databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev2"
                        user="scott"
                        password="spring"
                        minConnections="2"
                        testStatement="SELECT * FROM DUAL"/>

</sx:resources>
]]></programlisting>
</figure>
<para>
Executing the <code>employees</code> service, like this
<programlisting>
<![CDATA[
    servingxml -r resources-multipleReaders.xml employees -o output/deptEmployees.xml employees 
]]>
</programlisting>
results in the following two SQL statements being sent to the Oracle "dev2" database by the second reader:
<programlisting>
<![CDATA[
SELECT EMPNO, ENAME AS NAME, 10 AS DEPTNO, 'ACCOUNTING' AS DEPT 
  FROM EMP WHERE DEPTNO = 10
  ORDER ENAME

SELECT EMPNO, ENAME AS NAME, 30 AS DEPTNO, 'SALES' AS DEPT 
  FROM EMP WHERE DEPTNO = 30
  ORDER ENAME
]]></programlisting>
</para>
</section>
</section>

<section><title>XML to Database</title>
<section>
<title>Load Master Detail</title>
<titleabbrev>Master Detail</titleabbrev>
<indexterm><primary>SQL</primary><secondary>INSERT</secondary><tertiary>master/detail</tertiary></indexterm>
<para>
This example shows how to prepare a resources script that will insert
master/detail records into separate master and detail tables
    as a single transaction.
</para>
<para>
The input file is an XML file.
</para>
<figure id="masterDetail.xml">
  <title>Input XML file masterDetail.xml</title>
<programlisting>
<![CDATA[
<?xml version="1.0" encoding="utf-8"?>
<masterDetail>
  <master id="1">
    <name>aName</name>
    <detail id="11">
      <date>2008-09-03</date>
      <type>aType</type>
    </detail>
    <detail id="12">
      <date>2008-09-06</date>
      <type>aType</type>
    </detail>
    <detail id="13">
      <date>2008-09-10</date>
      <type>aType</type>
    </detail>
  </master>
  <master id="2">
    <name>anotherName</name>
    <detail id="21">
      <date>2008-10-03</date>
      <type>aType</type>
    </detail>
    <detail id="22">
      <date>2008-10-06</date>
      <type>aType</type>
    </detail>
    <detail id="23">
      <date>2008-10-07</date>
      <type>anotherType</type>
    </detail>
  </master>
</masterDetail>
]]>
</programlisting>
</figure>
<para>
The following resources script performs the update.
</para>
<figure id="resources-masterDetail.xml">
  <title>Resources script</title>
<programlisting><![CDATA[
<sx:resources xmlns:sx="http://www.servingxml.com/core">

  <sx:service id="loadMasterDetail">
    <sx:recordStream>
      <sx:subtreeRecordReader>
        <sx:transform>
          <sx:document/>
        </sx:transform>
        <sx:inverseRecordMapping ref="masterDetailMapping"/>
      </sx:subtreeRecordReader>
      <!-- You should validate the record with an sx:recordValidator here -->
      <sx:sqlWriter ref="masterDetailWriter"/>
      <sx:discardHandler>
        <sx:log message="{$sx:message}"/>
        <!-- You can set up a record pipeline here to write bad records to a file or database table -->
      </sx:discardHandler>
    </sx:recordStream>
  </sx:service>

  <sx:inverseRecordMapping id="masterDetailMapping">
    <sx:onSubtree path="/masterDetail/master">
      <sx:flattenSubtree recordType="master">
        <sx:subtreeFieldMap select="@id" field="masterId"/>
        <sx:subtreeFieldMap select="name" field="name"/>
        <sx:subtreeFieldMap match="detail" field="details">
          <sx:flattenSubtree recordType="detail">
            <sx:subtreeFieldMap select="@id" field="detailId"/>
            <sx:subtreeFieldMap select="date" field="date"/>
            <sx:subtreeFieldMap select="type" field="type"/>
          </sx:flattenSubtree>
        </sx:subtreeFieldMap>
      </sx:flattenSubtree>
    </sx:onSubtree>
  </sx:inverseRecordMapping>

  <sx:sqlWriter id="masterDetailWriter">                          
    <sx:sqlConnectionPool ref="jdbcPool"/>
    <sx:sqlUpdate>
        INSERT INTO master(master_id,name) VALUES({masterId},'{name}')
      <sx:sqlUpdateDetail field="details">
        <sx:sqlUpdate>
          INSERT INTO detail(detail_id,detail_date,detail_type) 
          VALUES({detailId},to_date('{date}','yyyy-mm-dd'),'{type}')
        </sx:sqlUpdate>
      </sx:sqlUpdateDetail>
    </sx:sqlUpdate>
  </sx:sqlWriter>

  <sx:sqlConnectionPool id="jdbcPool"
                        driver="oracle.jdbc.driver.OracleDriver"
                        databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev"
                        user="scott"
                        password="spring"
                        minConnections="2"
                        testStatement="SELECT * FROM DUAL"/>

</sx:resources>
]]></programlisting>
</figure>
  <para>
    The inverse record mapping instructions in the script will produce a stream 
    of composite records of type "master" that contain a repeating group field 
    named "details" having subrecords of type "detail".
  </para>
<para>
Executing the <code>loadMasterDetail</code> service, like this,
<programlisting>
<![CDATA[
servingxml -i data/masterDetail.xml -r resources-masterDetail.xml loadMasterDetail]]>
</programlisting>
results in the following update statements being sent to the RDMS and committed 
to the database:
</para>
<programlisting>
<![CDATA[
  INSERT INTO master(master_id,name) VALUES(1,'aName')
  INSERT INTO detail(detail_id,detail_date,detail_type) 
  VALUES(11,to_date('2008-09-03','yyyy-mm-dd'),'aType')
  INSERT INTO detail(detail_id,detail_date,detail_type) 
  VALUES(12,to_date('2008-09-06','yyyy-mm-dd'),'aType')
  INSERT INTO detail(detail_id,detail_date,detail_type) 
  VALUES(13,to_date('2008-09-10','yyyy-mm-dd'),'aType')
]]></programlisting>
<para>
The second set of master/detail records fails, however,
because the last detail has a value for <code>detail_type</code>,
  "anotherType", that 
exceeds the length of the column, 10.  The following error message
is written to the log:
</para>
<programlisting>
<![CDATA[
SEVERE: ORA-12899: value too large for column "SCOTT"."DETAIL"."DETAIL_TYPE" 
(actual: 11, maximum: 10) 
]]></programlisting>
  </section>
</section>
<section><title>SQL Query to Database</title>
<section>
<title>SQL inserts</title>
<titleabbrev>SQL inserts</titleabbrev>
<indexterm><primary>SQL</primary><secondary>INSERT</secondary></indexterm>
<para>
This example shows how to prepare a resources script that will read rows from one database table
and write them to another.
</para>
<para>
The following resources script does the transformation.
</para>
<figure id="resources-insert_employees.xml">
  <title>Resources script</title>
<programlisting><![CDATA[
<sx:resources xmlns:sx="http://www.servingxml.com/core"
               xmlns:msv="http://www.servingxml.com/extensions/msv">

  <sx:service id="loadEmployees">
    <sx:recordStream>
      <sx:sqlReader ref="employeesReader"/>
      <msv:recordValidator>
        <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
          <!-- This element's name matches the value of the name attribute in the sx:sqlQuery element. -->
          <xsd:element name="employee" type="EmployeeType"/>
          <xsd:complexType name="EmployeeType">
            <xsd:sequence>
              <xsd:element name="EMPNO" type="xsd:integer"/>
              <xsd:element name="NAME" type="xsd:string"/>
              <xsd:element name="JOB" type="xsd:string"/>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:schema>
      </msv:recordValidator>

      <sx:sqlWriter ref="employeesWriter"/>
      <sx:discardHandler>
        <sx:log message="{$sx:message}"/>
        <!-- You can include a record pipeline here to write bad records to a file or database table -->
      </sx:discardHandler>
    </sx:recordStream>
  </sx:service>

  <sx:sqlWriter id="employeesWriter">                          
    <sx:sqlConnectionPool ref="jdbcPool"/>
    <sx:sqlUpdate>
        INSERT INTO EMP_HISTORY(EMPNO, ENAME) VALUES({EMPNO},'{NAME}')
    </sx:sqlUpdate>
  </sx:sqlWriter>

  <sx:sqlReader id="employeesReader">
    <sx:sqlConnectionPool ref="jdbcPool"/>

    <sx:parameter name="jobList">
      <sx:toString value="{$job}" separator=",">
        <sx:quoteSymbol character="'" escapeCharacter="'"/>
      </sx:toString>
    </sx:parameter>

    <sx:sqlQuery recordType = "employee">
        SELECT EMPNO, ENAME AS NAME,JOB FROM EMP WHERE 1=1
      <sx:choose>
        <sx:when test="$jobList">
            AND JOB IN ({$jobList})
        </sx:when>
      </sx:choose>
        ORDER BY JOB,ENAME
    </sx:sqlQuery>

  </sx:sqlReader>

  <sx:sqlConnectionPool id="jdbcPool"
                        driver="oracle.jdbc.driver.OracleDriver"
                        databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev"
                        user="scott"
                        password="spring"
                        minConnections="2"
                        testStatement="SELECT * FROM DUAL"/>

</sx:resources>
]]></programlisting>
</figure>
<para>
Executing the <code>loadEmployees</code> service with two <code>job</code> parameters, like this
<programlisting>
<![CDATA[
    servingxml -r resources-insert_employees.xml loadEmployees 
        job=ANALYST job=CLERK
]]>
</programlisting>
results in the following SQL statement being sent to the Oracle server:
<programlisting>
<![CDATA[
SELECT EMPNO, ENAME AS NAME,JOB 
  FROM EMP 
  WHERE 1=1 AND JOB IN ('ANALYST','CLERK') 
  ORDER BY JOB,ENAME
]]></programlisting>
</para>
<para>
These results are then written to the database table <code>EMP_HISTORY</code>.
</para>
</section>

<section>
<title>Batched SQL inserts</title>
<titleabbrev>Batched inserts</titleabbrev>
<indexterm><primary>SQL</primary><secondary>INSERT</secondary></indexterm>
<indexterm><primary>SQL</primary><secondary>batch</secondary></indexterm>
<para>
This example shows how to prepare a resources script that will read rows from one database table
and write them in batches to another.
</para>
<para>
The following resources script does the transformation.
</para>
<figure id="resources-batch_insert_employees.xml">
  <title>Resources script</title>
<programlisting><![CDATA[
<sx:resources xmlns:sx="http://www.servingxml.com/core"
               xmlns:msv="http://www.servingxml.com/extensions/msv">

  <sx:service id="loadEmployees">
    <sx:recordStream>
      <sx:sqlReader ref="employeesReader"/>
      <msv:recordValidator>
        <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
          <!-- This element's name matches the value of the name attribute in the sx:sqlQuery element. -->
          <xsd:element name="employee" type="EmployeeType"/>
          <xsd:complexType name="EmployeeType">
            <xsd:sequence>
              <xsd:element name="EMPNO" type="xsd:integer"/>
              <xsd:element name="NAME" type="xsd:string"/>
              <xsd:element name="JOB" type="xsd:string"/>              
            </xsd:sequence>
          </xsd:complexType>
        </xsd:schema>
      </msv:recordValidator>

      <sx:sqlWriter ref="employeesWriter"/>
      <sx:discardHandler>
        <sx:log message="{$sx:message}"/>
        <!-- You can include a record pipeline here to write bad records to a file or database table -->
      </sx:discardHandler>
    </sx:recordStream>
  </sx:service>

  <sx:sqlBatchWriter id="employeesWriter" batchSize="4">
    <sx:sqlConnectionPool ref="jdbcPool"/>
    <sx:sqlUpdate>
        INSERT INTO EMP_HISTORY(EMPNO, ENAME) VALUES( {EMPNO}, '{NAME}')
    </sx:sqlUpdate>
  </sx:sqlBatchWriter>

  <sx:sqlReader id="employeesReader">
    <sx:sqlConnectionPool ref="jdbcPool"/>

    <sx:parameter name="jobList">
      <sx:toString value="{$job}" separator=",">
        <sx:quoteSymbol character="'" escapeCharacter="'"/>
      </sx:toString>
    </sx:parameter>

    <sx:sqlQuery recordType = "employee">
        SELECT EMPNO, ENAME AS NAME,JOB FROM EMP WHERE 1=1
      <sx:choose>
        <sx:when test="$jobList">
            AND JOB IN ({$jobList})
        </sx:when>
      </sx:choose>
        ORDER BY JOB,ENAME
    </sx:sqlQuery>

  </sx:sqlReader>

  <sx:sqlConnectionPool id="jdbcPool"
                        driver="oracle.jdbc.driver.OracleDriver"
                        databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev"
                        user="scott"
                        password="spring"
                        minConnections="2"
                        testStatement="SELECT * FROM DUAL"/>

</sx:resources>
]]></programlisting>
</figure>
<para>
Executing the <code>loadEmployees</code> service with two <code>job</code> parameters, like this
<programlisting>
<![CDATA[
    servingxml -r resources-batch_insert_employees.xml loadEmployees 
        job=ANALYST job=CLERK
]]>
</programlisting>
results in the following SQL statement being sent to the Oracle server:
<programlisting>
<![CDATA[
SELECT EMPNO, ENAME AS NAME,JOB 
  FROM EMP 
  WHERE 1=1 AND JOB IN ('ANALYST','CLERK') 
  ORDER BY JOB,ENAME
]]></programlisting>
</para>
<para>
These results are then written in batches of 4 to the database table <code>EMP_HISTORY</code>.
</para>
</section>
<section>
<title>Prepared SQL inserts</title>
<titleabbrev>SQL prepare</titleabbrev>
<indexterm><primary>SQL</primary><secondary>INSERT</secondary></indexterm>
<indexterm><primary>SQL</primary><secondary>PREPARE</secondary></indexterm>
<para>
This example shows how to prepare a resources script that will read rows from one database table
and write them using a prepared SQL statement to another.
</para>
<para>
The following resources script does the transformation.
</para>
<figure id="resources-prepare_insert_employees.xml">
  <title>Resources script</title>
<programlisting><![CDATA[
<sx:resources xmlns:sx="http://www.servingxml.com/core"
               xmlns:msv="http://www.servingxml.com/extensions/msv">

  <sx:service id="loadEmployees">
    <sx:recordStream>
      <sx:sqlReader ref="employeesReader"/>

      <msv:recordValidator>
        <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
          <!-- This element's name matches the value of the name attribute in the sx:sqlQuery element. -->
          <xs:element name="employee" type="EmployeeType"/>
          <xs:complexType name="EmployeeType">
            <xs:sequence>
              <xs:element name="EMPNO" type="xs:integer"/>
              <xs:element name="NAME" type="xs:string"/>
              <xs:element name="JOB" type="xs:string"/>
              <xs:element name="MGR" type="xs:integer"/>
              <xs:element name="HIREDATE" type="xs:date"/>
              <xs:element name="SAL" type="xs:decimal"/>
              <xs:element name="COMM" type="xs:decimal" minOccurs="0"/> 
              <xs:element name="DEPTNO" type="xs:integer"/>
            </xs:sequence>
          </xs:complexType>
        </xs:schema>
      </msv:recordValidator>

      <sx:sqlWriter ref="employeesWriter"/>
      <sx:discardHandler>
        <sx:log message="{$sx:message}"/>
        <!-- You can include a record pipeline here to write bad records to a database table -->
        <sx:sqlWriter ref="discardWriter"/> 
        <sx:discardHandler>
          <sx:log message="{$sx:message}"/>
        </sx:discardHandler>
      </sx:discardHandler>
    </sx:recordStream>
  </sx:service>

  <sx:sqlWriter id="employeesWriter" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <sx:sqlConnectionPool ref="jdbcPool"/>
    <sx:sqlUpdate>                                                                                                                        
      <!-- Because the SQL insert executes a prepared statement, values must be
            associated with appropriate types.  In this example, because these values are 
            coming from a SQL query source, they will already be associated with SQL types, 
            and no further action is really necessary.  In the case of other data sources, however,
            values by default will be associated with string types, and may need
            to be recast in parameter assignments, as shown below.
      -->
      <sx:parameter name="employee-no" value="{EMPNO}" type="xs:long"/>
      <sx:parameter name="mgr" value="{MGR}" type="xs:long"/>
      <sx:parameter name="hiredate" value="{HIREDATE}" type="xs:date"/>              
      <sx:parameter name="salary" value="{SAL}" type="xs:decimal"/>              
      <sx:parameter name="commission" value="{COMM}" type="xs:decimal"/>              
      <sx:sqlPrepare>
        INSERT INTO EMP_HISTORY(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) 
        VALUES({$employee-no},{NAME},{JOB}, {$mgr}, {$hiredate},  {$salary}, {$commission})
      </sx:sqlPrepare>
    </sx:sqlUpdate>
  </sx:sqlWriter>                                                                              

  <sx:sqlWriter id="discardWriter" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <sx:sqlConnectionPool ref="jdbcPool"/>
    <sx:sqlUpdate>
      <sx:parameter name="employee-no" value="{EMPNO}" type="xs:long"/>
      <sx:parameter name="mgr" value="{MGR}" type="xs:long"/>
      <sx:parameter name="hiredate" value="{HIREDATE}" type="xs:date"/>              
      <sx:parameter name="salary" value="{SAL}" type="xs:decimal"/>              
      <sx:parameter name="commission" value="{COMM}" type="xs:decimal"/>              
      <sx:sqlPrepare>
        INSERT INTO EMP_DISCARD(MESSAGE, EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, 
                                COMM) 
        VALUES({$message}, {$employee-no}, {NAME}, {JOB}, {$mgr}, {$hiredate},
               {$salary}, {$commission})
      </sx:sqlPrepare>
    </sx:sqlUpdate>
  </sx:sqlWriter>                                                                              
                                                              
  <sx:sqlReader id="employeesReader">                         
    <sx:sqlConnectionPool ref="jdbcPool"/>

    <sx:parameter name="jobList">
      <sx:toString value="{$job}" separator=",">
        <sx:quoteSymbol character="'" escapeCharacter="'"/>
      </sx:toString>
    </sx:parameter>

    <sx:sqlQuery recordType = "employee">
        SELECT EMPNO, ENAME AS NAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP WHERE 1=1
      <sx:choose>
        <sx:when test="$jobList">
            AND JOB IN ({$jobList})
        </sx:when>
      </sx:choose>
        ORDER BY JOB,ENAME
    </sx:sqlQuery>

  </sx:sqlReader>

  <sx:sqlConnectionPool id="jdbcPool"
                        driver="oracle.jdbc.driver.OracleDriver"
                        databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev"
                        user="scott"
                        password="spring"
                        minConnections="2"
                        testStatement="SELECT * FROM DUAL"/>

</sx:resources>
]]></programlisting>
</figure>
<para>
Executing the <code>loadEmployees</code> service with two <code>job</code> parameters, like this
<programlisting>
<![CDATA[
    servingxml -r resources-prepare_insert_employees.xml loadEmployees 
        job=SALESMAN job=MANAGER
]]>
</programlisting>
results in the following SQL statement being sent to the Oracle server:
<programlisting>
<![CDATA[
SELECT EMPNO, ENAME AS NAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO 
  FROM EMP 
  WHERE 1=1 AND JOB IN ('SALESMAN','MANAGER') 
  ORDER BY JOB,ENAME
]]></programlisting>
</para>
<para>
These results are then written in batches of 4 to the database table <code>EMP_HISTORY</code>.
</para>
</section>
<section>
<title>Batched prepared SQL inserts</title>
<titleabbrev>Batched prepared inserts</titleabbrev>
<indexterm><primary>SQL</primary><secondary>INSERT</secondary></indexterm>
<indexterm><primary>SQL</primary><secondary>prepare</secondary></indexterm>
<indexterm><primary>SQL</primary><secondary>batch</secondary></indexterm>
<para>
This example shows how to prepare a resources script that will read rows from one database table
and write them in batches, using a prepared SQL statement, to another.
</para>
<para>
The following resources script does the transformation.
</para>
<figure id="resources-batch_prepare_insert_employees.xml">
  <title>Resources script</title>
<programlisting><![CDATA[
<sx:resources xmlns:sx="http://www.servingxml.com/core"
               xmlns:msv="http://www.servingxml.com/extensions/msv">

  <sx:service id="loadEmployees">
    <sx:recordStream>
      <sx:sqlReader ref="employeesReader"/>
      <msv:recordValidator>
        <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
          <!-- This element's name matches the value of the name attribute in the sx:sqlQuery element. -->
          <xs:element name="employee" type="EmployeeType"/>
          <xs:complexType name="EmployeeType">
            <xs:sequence>
              <xs:element name="EMPNO" type="xs:integer"/>
              <xs:element name="NAME" type="xs:string"/>
              <xs:element name="JOB" type="xs:string"/>
              <xs:element name="MGR" type="xs:integer"/>
              <xs:element name="HIREDATE" type="xs:date"/>
              <xs:element name="SAL" type="xs:decimal"/>
              <xs:element name="COMM" type="xs:decimal" minOccurs="0"/> 
              <xs:element name="DEPTNO" type="xs:integer"/>
            </xs:sequence>
          </xs:complexType>
        </xs:schema>
      </msv:recordValidator>

      <sx:sqlWriter ref="employeesWriter"/>
      <sx:discardHandler>
        <sx:log message="{$sx:message}"/>
        <!-- You can include a record pipeline here to write bad records to a file or database table -->
      </sx:discardHandler>
    </sx:recordStream>
  </sx:service>

  <sx:sqlBatchWriter id="employeesWriter" xmlns:xs="http://www.w3.org/2001/XMLSchema" batchSize="4">
    <sx:sqlConnectionPool ref="jdbcPool"/>
    <sx:sqlUpdate>
      <!-- Because the SQL insert executes a prepared statement, values must be
           associated with appropriate types.  In this example, because these values are 
           coming from a SQL query source, they will already be associated with SQL types, 
           and no further action is really necessary.  In the case of other data sources, however,
           values by default will be associated with string types, and may need
           to be recast in parameter assignments, as shown below.
      -->
      <sx:parameter name="employee-no" value="{EMPNO}" type="xs:long"/>
      <sx:parameter name="mgr" value="{MGR}" type="xs:long"/>
      <sx:parameter name="hiredate" value="{HIREDATE}" type="xs:date"/>              
      <sx:parameter name="salary" value="{SAL}" type="xs:decimal"/>              
      <sx:parameter name="commission" value="{COMM}" type="xs:decimal"/>              
      <sx:sqlPrepare>                                                                                                            
        INSERT INTO EMP_HISTORY(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM) 
        VALUES({$employee-no},{NAME},{JOB}, {$mgr}, {$hiredate},  {$salary}, {$commission})
      </sx:sqlPrepare>
    </sx:sqlUpdate>
  </sx:sqlBatchWriter>

  <sx:sqlReader id="employeesReader">
    <sx:sqlConnectionPool ref="jdbcPool"/>

    <sx:parameter name="jobList">
      <sx:toString value="{$job}" separator=",">
        <sx:quoteSymbol character="'" escapeCharacter="'"/>
      </sx:toString>
    </sx:parameter>

    <sx:sqlQuery recordType = "employee">
        SELECT EMPNO, ENAME AS NAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP WHERE 1=1
      <sx:choose>
        <sx:when test="$jobList">
            AND JOB IN ({$jobList})
        </sx:when>
      </sx:choose>
        ORDER BY JOB,ENAME
    </sx:sqlQuery>

  </sx:sqlReader>

  <sx:sqlConnectionPool id="jdbcPool"
                        driver="oracle.jdbc.driver.OracleDriver"
                        databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev"
                        user="scott"
                        password="spring"
                        minConnections="2"
                        testStatement="SELECT * FROM DUAL"/>

</sx:resources>
]]></programlisting>
</figure>
<para>
Executing the <code>loadEmployees</code> service with two <code>job</code> parameters, like this
<programlisting>
<![CDATA[
    servingxml -r resources-batch_prepare_insert_employees.xml loadEmployees 
        job=SALESMAN job=MANAGER
]]>
</programlisting>
results in the following SQL statement being sent to the Oracle server:
<programlisting>
<![CDATA[
SELECT EMPNO, ENAME AS NAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO 
  FROM EMP 
  WHERE 1=1 AND JOB IN ('SALESMAN','MANAGER') 
  ORDER BY JOB,ENAME
]]></programlisting>
</para>
<para>
These results are then written in batches of 4 to the database table <code>EMP_HISTORY</code>, using prepared SQL statements.
</para>
</section>
<section>
<title>Updating an employee record using command-line parameters (employee update)</title>
<para>
The example below illustrates how to prepare a resources script that will change an employee
job categorization using command line parameters.  
</para>
<figure id="sql-resources.xml">
  <title>Resources script</title>
<programlisting><![CDATA[
<sx:resources xmlns:sx="http://www.servingxml.com/core">

  <sx:service id="updateEmployee">
    <sx:recordStream>
      <sx:parameterReader recordType="employee"/>
      <sx:sqlWriter>
        <sx:sqlConnectionPool ref="jdbcPool"/>
        <sx:sqlUpdate>
          UPDATE EMP SET JOB = '{$job}' WHERE EMPNO='{$empNo}'
        </sx:sqlUpdate>
      </sx:sqlWriter>
    </sx:recordStream>
  </sx:service>
  
  <sx:sqlConnectionPool id="jdbcPool"
                        driver="oracle.jdbc.driver.OracleDriver"
                        databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev"
                        user="scott"
                        password="spring"
                        minConnections="2"
                        testStatement="SELECT * FROM DUAL"/>

</sx:resources>
]]></programlisting>
</figure>
<para>
You can run this example on the command line by entering
<programlisting>
<![CDATA[
servingxml -r resources.xml -o updateEmployee 
    empNo=7499 job=ANALYST
]]>
</programlisting>
</para>
</section>

<section>
<title>Insert if record does not exist, update otherwise (insert/update).</title>
<titleabbrev>insert/update</titleabbrev>
<indexterm><primary>SQL</primary><secondary>INSERT/UPDATE</secondary></indexterm>
<para>
This example shows how to prepare a resources script that will read rows from one database table
and write them to another, inserting if the record does not already exist, updating otherwise.
</para>
<para>
The following resources script does the transformation.
</para>
<figure id="resources-loadEmployees.xml">
  <title>Resources script</title>
<programlisting><![CDATA[
<sx:resources xmlns:sx="http://www.servingxml.com/core"
               xmlns:msv="http://www.servingxml.com/extensions/msv">

  <sx:service id="loadEmployees">
    <sx:recordStream>
      <sx:sqlReader ref="employeesReader"/>
      <msv:recordValidator>
        <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
          <!-- This element's name matches the value of the name attribute in the sx:sqlQuery element. -->
          <xs:element name="employee" type="EmployeeType"/>
          <xs:complexType name="EmployeeType">
            <xs:sequence>
              <xs:element name="EMPNO" type="xs:integer"/>
              <xs:element name="NAME" type="xs:string"/>
              <xs:element name="JOB" type="xs:string"/>
            </xs:sequence>
          </xs:complexType>
        </xs:schema>
      </msv:recordValidator>

      <sx:sqlWriter ref="employeesWriter"/>
      <sx:discardHandler>
        <sx:log message="{$sx:message}"/>
        <!-- You can include a record pipeline here to write bad records to a file or database table -->
      </sx:discardHandler>
    </sx:recordStream>
  </sx:service>

  <sx:sqlWriter id="employeesWriter">
    <sx:sqlConnectionPool ref="jdbcPool"/>
    <sx:sqlUpdateChoice>
      <sx:sqlQuery>
        SELECT EMPNO FROM EMP_HISTORY WHERE EMPNO = {EMPNO}
      </sx:sqlQuery>
      <sx:recordNotFound>
        <sx:sqlUpdate>
          INSERT INTO EMP_HISTORY(EMPNO, ENAME) VALUES({EMPNO},'{NAME}')
        </sx:sqlUpdate>
      </sx:recordNotFound>
      <sx:recordFound>
        <sx:sqlUpdate>
          UPDATE EMP_HISTORY SET ENAME = '{NAME}' WHERE EMPNO = {EMPNO}
        </sx:sqlUpdate>
      </sx:recordFound>
    </sx:sqlUpdateChoice>
  </sx:sqlWriter>

  <sx:sqlReader id="employeesReader">
    <sx:sqlConnectionPool ref="jdbcPool"/>

    <sx:parameter name="jobList">
      <sx:toString value="{$job}" separator=",">
        <sx:quoteSymbol character="'" escapeCharacter="'"/>
      </sx:toString>
    </sx:parameter>

    <sx:sqlQuery recordType = "employee">
        SELECT EMPNO, ENAME AS NAME,JOB FROM EMP WHERE 1=1
      <sx:choose>
        <sx:when test="$jobList">
            AND JOB IN ({$jobList})
        </sx:when>
      </sx:choose>
        ORDER BY JOB,ENAME
    </sx:sqlQuery>

  </sx:sqlReader>

  <sx:sqlConnectionPool id="jdbcPool"
                        driver="oracle.jdbc.driver.OracleDriver"
                        databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev"
                        user="scott"
                        password="spring"
                        minConnections="2"
                        testStatement="SELECT * FROM DUAL"/>

</sx:resources>
]]></programlisting>
</figure>
<para>
Execute the <code>loadEmployees</code> service with two <code>job</code> parameters, like this
</para>
<programlisting>
<![CDATA[
    servingxml -r resources-insertUpdate.xml loadEmployees 
        job=ANALYST job=CLERK
]]>
</programlisting>
</section>

<section>
<title>Writing rows to a database table as they pass through a pipe, to an XML file (employees-tee)</title>
<titleabbrev>employees tee</titleabbrev>
<indexterm><primary>tee</primary><secondary>record</secondary></indexterm>
<para>
This example shows how to prepare a resources script that will read rows from an RDBMS,
write them to a database table as they pass through the pipe, and finally produce an XML file.
</para>
<para>
The desired XML file is shown below.
</para>

<figure id="employees.xml">
  <title>Output XML file employees.xml</title>
<programlisting>
<![CDATA[
<?xml version="1.0" encoding="utf-8"?>
<employees>
   <ANALYST>
      <employee employee-no="7902">
         <name>FORD</name>
      </employee>
      <employee employee-no="7788">
         <name>SCOTT</name>
      </employee>
   </ANALYST>
   <CLERK>
      <employee employee-no="7876">
         <name>ADAMS</name>
      </employee>
      <employee employee-no="7900">
         <name>JAMES</name>
      </employee>
      <employee employee-no="7934">
         <name>MILLER</name>
      </employee>
      <employee employee-no="7369">
         <name>SMITH</name>
      </employee>
   </CLERK>
</employees>
]]></programlisting>
</figure>
<para>
The following resources script does the transformation.
</para>
<figure id="resources.xml">
  <title>Resources script</title>
<programlisting><![CDATA[
<sx:resources xmlns:sx="http://www.servingxml.com/core">

  <sx:service id="employees">
    <sx:serialize>
      <sx:content ref="employeesDoc"/>
    </sx:serialize>
  </sx:service>

  <sx:recordContent id="employeesDoc">
    <sx:sqlReader>
      <sx:sqlConnectionPool ref="jdbcPool"/>

      <sx:parameter name="jobList">
        <sx:toString value="{$job}" separator=",">
          <sx:quoteSymbol character="'" escapeCharacter="'"/>
        </sx:toString>
      </sx:parameter>

      <sx:sqlQuery recordType = "employee">
        SELECT EMPNO, ENAME AS NAME,JOB FROM EMP WHERE 1=1
        <sx:choose>
          <sx:when test="$jobList">
            AND JOB IN ({$jobList})
          </sx:when>
        </sx:choose>
        ORDER BY JOB,ENAME
      </sx:sqlQuery>
    </sx:sqlReader>

    <sx:recordTee>
      <sx:sqlWriter>
        <sx:sqlConnectionPool ref="jdbcPool"/>
        <sx:sqlUpdate>
            INSERT INTO EMP_LOG(EMPNO, ENAME) VALUES({EMPNO},'{NAME}')
        </sx:sqlUpdate>
      </sx:sqlWriter>
    </sx:recordTee>

    <sx:recordMapping ref="employeesToXml"/>
  </sx:recordContent>
                           
  <sx:recordMapping id="employeesToXml">
    <employees>
      <sx:groupBy fields="JOB">
        <sx:elementMap element="{JOB}">
          <sx:onRecord>
            <employee>
              <sx:fieldAttributeMap field="EMPNO" attribute="employee-no"/>
              <sx:fieldElementMap field="NAME" element="name"/>
            </employee>
          </sx:onRecord>
        </sx:elementMap>
      </sx:groupBy>
    </employees>
  </sx:recordMapping>

  <sx:sqlConnectionPool id="jdbcPool"
                        driver="oracle.jdbc.driver.OracleDriver"
                        databaseUrl="jdbc:oracle:thin:@127.0.0.1:1521:dev"
                        user="scott"
                        password="spring"
                        minConnections="2"
                        testStatement="SELECT * FROM DUAL"/>

</sx:resources>
]]></programlisting>
</figure>
<para>
Executing the <code>employees</code> service with two <code>job</code> parameters, like this
<programlisting>
<![CDATA[
    servingxml -r resources-employeesTee.xml employees -o output/employees.xml employees 
        job=ANALYST job=CLERK
]]>
</programlisting>
results in the following SQL statement being sent to the Oracle server:
<programlisting>
<![CDATA[
SELECT EMPNO, ENAME AS NAME,JOB 
  FROM EMP 
  WHERE 1=1 AND JOB IN ('ANALYST','CLERK') 
  ORDER BY JOB,ENAME
]]></programlisting>
</para>
<para>
Each <code>EMPNO</code> and <code>ENAME</code> pair is written to the database table <code>EMP_LOG</code>, and all rows are 
ultimately mapped to the XML file <filename>employees.xml</filename>.
</para>
</section>
</section>
</section>

