Oracle XMLType data type support

The SQL actor adapter supports the Oracle XMLType data type for standard SQL statements, parameterized statements, and stored procedures if the value of the <data-type> element is XMLTYPE, an Oracle XMLType data type in the adapter configuration or adapter request.

The following figure shows an XML sample of the SQL actor adapter request that supports the Oracle XMLType data type.

XML sample of the SQL actor adapter request supporting the Oracle XMLType data type


<sql-adapter-request>
<statements>
 <statement>
   <query><![CDATA[INSERT INTO warehouses VALUES ( 500, XMLType('<Warehouse whNo="100">
     <Building>Rented</Building> </Warehouse>'), 'Tower Records', 1003)]]></query>
 </statement>
 <statement>
   <query><![CDATA[INSERT INTO warehouses VALUES ( 600, XMLType( '<Warehouse whNo="100">
     <Building>Rented</Building> </Warehouse>'), 'Tower Records', 1003)]]></query>
 </statement>
</statements>
</sql-adapter-request>


The following figure shows an XML sample of the SQL actor adapter response that supports the Oracle XMLType data type.

XML sample of the SQL actor adapter response supporting Oracle XMLType data type


<sql-adapter-response>
   <statement-responses>
       <statement-response>
           <statement-metadata>
<response-set-count>0</response-set-count>
               <status>success</status>
           </statement-metadata>
           <response-sets>
               <response-set index = "1">
                   <response-set-metadata>
                       <update-count>1</update-count>
                       <status>success</status>
                   </response-set-metadata>
               </response-set>
           </response-sets>
       </statement-response>
       <statement-response>
           <statement-metadata>
               <response-set-count>0</response-set-count>
               <status>success</status>
           </statement-metadata>
           <response-sets>
               <response-set index = "1">
                   <response-set-metadata>
                       <update-count>1</update-count>
                       <status>success</status>
                   </response-set-metadata>
               </response-set>
           </response-sets>
       </statement-response>
<statement-response>
           <statement-metadata>
               <response-set-count>0</response-set-count>
               <status>success</status>
           </statement-metadata>
           <response-sets>
               <response-set index = "1">
                   <response-set-metadata>
                       <update-count>1</update-count>
                       <status>success</status>
                   </response-set-metadata>
               </response-set>
           </response-sets>
       </statement-response>
   </statement-responses>
</sql-adapter-response>
<sql-adapter-request>
   <statements>
       <statement>
           <query><![CDATA[INSERT INTO warehouses VALUES (?,?,?,?)]]></query>
           <parameter-sets>
               <parameter-set>
                   <parameter>
                       <data-type>NUMBER</data-type>
                       <value>4785</value>
                   </parameter>
                   <parameter>
                       <data-type>NUMBER</data-type>
                       <value>4785</value>
                   </parameter>
            <parameter>
<data-type>XMLTYPE</data-type>
                       <value><![CDATA[<Warehouse whNo="189">
<Building>TestXMLparasupport</Building></Warehouse>]]></value>
                   </parameter>
                   <parameter>
<data-type>VARCHAR</data-type>
                       <value>OneEightNine</value>
                   </parameter>
                   <parameter>
                   <parameter>
<data-type>NUMBER</data-type>
                       <value>4895</value>
                   </parameter>
               </parameter-set>
           </parameter-sets>
       </statement>
   </statements>
</sql-adapter-request>
<sql-adapter-response>
   <statement-responses>
       <statement-response>
           <statement-metadata>
               <response-set-count>1</response-set-count>
               <status>success</status>
           </statement-metadata>
           <response-sets>
               <response-set index = "1">
                   <response-set-metadata>
                       <update-count>1</update-count>
                       <status>success</status>
                   </response-set-metadata>
               </response-set>
           </response-sets>
       </statement-response>
   </statement-responses>
</sql-adapter-response>

Ensure that you perform the following tasks:

  1. Copy the required third-party (client-specific) .jar files to the %AO_HOME%/lib/add-ons directory.
  2. Locate the .jar files in the appropriate location where the Oracle database is installed.


Third-party (client-specific) JAR files to be copied

Note

The Oracle thin client supports only queries that do not perform the XQuery operation on the XMLType data type.

For example:

  • INSERT INTO warehouses VALUES (100, XMLType('<Warehouse whNo="100"><Building>Owned</Building></Warehouse>'), 'Tower Records', 1003);
  • UPDATE warehouses SET warehouse_spec = XMLType('<Warehouse whono="222"><Building>Leased1</Building></Warehouse>');

You need Oracle Call Interface (OCI) client libraries if you use XQuery strings in the SQL query. In other words, you need to use an OCI (thick) client if the SQL query has an XQuery operation on the XMLType data type, such as extract().

For example: DELETE FROM warehouses e WHERE e.warehouse_spec.extract ('//Building/text()').getStringVal()= 'Lease')

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*