How to connect to database using XSL Transform policy ? error: Unknown extension instruction

[#1]

How to connect to database using XSL Transform policy?

1. Using XSL Transform policy, I am trying to connect to the Dabase and fetch some details that need to be populated in the output XML.

2. As per the APIGEE documentation, SAXON is used (I assume that Saxon EE is being used though i did not see any documentation on it).

3. I created an XSL to connect it to DB and using SAXON sql extention, but I am getting the error "Unknown extension instruction"

ERROR: { "fault": { "faultstring": "Evaluation of XSL XSL_getConferenceRooms.xsl failed with reason: \"Unknown extension instruction\"", "detail": { "errorcode": "steps.xsl.XSLEvaluationFailed" } } }

4. Please let me know how to connect to database using XSL Transform policy by making the DB connection and fetch with in the XSL.

5. If there is any other way to connect to DB with in XSL other than saxon sql extention in apigee, please mention that as well.

XSL:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" 
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:saxon="http://saxon.sf.net/" 
                xmlns:sql="http://saxon.sf.net/sql" extension-element-prefixes="saxon sql" exclude-result-prefixes="sql saxon">
  <!-- Database details -->
  <xsl:param name="driver" select="'oracle.jdbc.driver.OracleDriver'" as="xs:string"/>
  <xsl:param name="database" select="'jdbc:oracle:thin:@XXXXXX:1521:YYY'" as="xs:string"/> 
  <xsl:param name="user" select="'AAA'" as="xs:string"/>
  <xsl:param name="password" select="'BBB'" as="xs:string"/>
  <!-- DB query -->
  <xsl:variable name="DBquery">Product_ID="<xsl:value-of select="ProductItem/ProductItemId"/>"</xsl:variable>
  <xsl:template match="/">
    <xsl:if test="not(element-available('sql:connect'))">
      <xsl:message> sql-connect is not available </xsl:message>
    </xsl:if>
    <xsl:message> Connecting to Oracle ...</xsl:message>
    <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">
      </sql:connect>
    </xsl:variable>
    <ns1:root>
      <sql:query query="{$DBquery}" connection="$connection" table="ABC.ACCOUNT" column="CITY" row-tag="row" column-tag="col" />
    </ns1:root>
    <sql:close connection="$connection"/>
  </xsl:template>
</xsl:stylesheet>

[DChiesa]

You cannot do that. As far as I know the xsl extensions like sql are disabled when the XSL is run within Apigee Edge. It is possible to connect to an Oracle database using the thin driver from a Java callout. You could retrieve the data and then use that to populate your XSLT. A better idea might be to layer a service on top of the Oracle DB, and access the data over HTTP.

If you need more specific help with any of this, let me know.

[#3]

Thanks Dino for this quick answer !