I am trying to query MySQL database from XSLT2.0 using SQL query and populate a parameter in my XSLT file. I am using saxon9ee.jar along with saxon9-sql.jar.
I found the this link, but it didn't resolve my issue.
I am getting below error :
Connecting MySQL...
Connected...
Error on line 43 of DB_Query_XSLT_Test.xsl:
XTDE1450: Unknown extension instruction
; SystemID: file:///C:/Users/davo/platform/src/config/xslt/DB_Query_XSLT_Test.xsl; Line#: 43; Column#: -1
net.sf.saxon.trans.XPathException: Unknown extension instruction
at net.sf.saxon.expr.ErrorExpression.evaluateItem(ErrorExpression.java:58)
at net.sf.saxon.expr.ErrorExpression.iterate(ErrorExpression.java:71)
at net.sf.saxon.expr.Expression.process(Expression.java:467)
at net.sf.saxon.instruct.DocumentInstr.evaluateItem(DocumentInstr.java:305)
at net.sf.saxon.functions.StringFn.evaluateItem(StringFn.java:62)
at net.sf.saxon.instruct.ValueOf.processLeavingTail(ValueOf.java:246)
at net.sf.saxon.instruct.Block.processLeavingTail(Block.java:619)
at net.sf.saxon.instruct.Instruction.process(Instruction.java:93)
at net.sf.saxon.instruct.ElementCreator.processLeavingTail(ElementCreator.java:301)
at net.sf.saxon.instruct.Instruction.process(Instruction.java:93)
at net.sf.saxon.instruct.ElementCreator.processLeavingTail(ElementCreator.java:301)
at net.sf.saxon.instruct.Instruction.process(Instruction.java:93)
at net.sf.saxon.instruct.ElementCreator.processLeavingTail(ElementCreator.java:301)
at net.sf.saxon.instruct.Block.processLeavingTail(Block.java:619)
at net.sf.saxon.instruct.Instruction.process(Instruction.java:93)
at net.sf.saxon.instruct.ElementCreator.processLeavingTail(ElementCreator.java:301)
at net.sf.saxon.instruct.Template.applyLeavingTail(Template.java:229)
at net.sf.saxon.instruct.ApplyTemplates.applyTemplates(ApplyTemplates.java:354)
at net.sf.saxon.Controller.transformDocument(Controller.java:1849)
at net.sf.saxon.Controller.transform(Controller.java:1700)
at com.alu.ipprd.aor.fsw.tt.util.xml.transform.XSLTUtil.transformXML(XSLTUtil.java:52)
at com.alu.ipprd.aor.fsw.tt.util.xml.transform.XSLTUtil.main(XSLTUtil.java:214)
<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:tro="http://www.example.org/TroubleTicketWS_v3/">
<soapenv:Header/>
<soapenv:Body>
<tro:CreateTroubleTicketByValueRequest
XSLT:
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="http://saxon.sf.net/sql"
extension-element-prefixes="sql"
exclude-result-prefixes="xs">
<xsl:output method="xml" encoding="UTF-8" indent="yes" />
<xsl:variable name="driver" select="'com.mysql.jdbc.Driver'"
as="xs:string" />
<xsl:variable name="database"
select="'jdbc:mysql://xxx.xxx.xxx.xxx:3306/platform'" as="xs:string" />
<xsl:variable name="user" select="'dbremote'" as="xs:string" />
<xsl:variable name="password" select="'dbremote'" as="xs:string" />
<xsl:template match="/">
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:tro="http://www.example.org/TroubleTicketWS_v3/">
<soapenv:Header />
<soapenv:Body>
<tro:CreateTroubleTicketByValueRequest>
<ticketkey>
<xsl:message>Connecting MySQL...</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:fallback>
<xsl:message terminate="yes">Connection to MySQL failed.</xsl:message>
</xsl:fallback>
</sql:connect>
</xsl:variable>
<xsl:message>Connected...</xsl:message>
<xsl:variable name="internalticketid" select="/tTroubleticket/internalticketid" />
<xsl:variable name="troubleticketkey">
<sql:query connection="$connection" table="T_TROUBLETICKET"
column="TROUBLETICKETKEY" row-tag="ticket" column-tag="ttf"
where="INTERNALTICKETID={$internalticketid}" />
</xsl:variable>
<xsl:value-of select="$troubleticketkey" />
</ticketkey>
</tro:CreateTroubleTicketByValueRequest>
</soapenv:Body>
</soapenv:Envelope>
</xsl:template>
</xsl:stylesheet>
Java Class:
package com.fsw.tt.util.xml.transform;
import java.io.StringReader;
import java.io.StringWriter;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.TransformerFactoryConfigurationError;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;
//import net.sf.saxon.TransformerFactoryImpl;
import com.saxonica.config.EnterpriseTransformerFactory;
public class XSLTUtil {
TransformerFactory factory = null;
public XSLTUtil() {
//factory = new TransformerFactoryImpl();
factory = new EnterpriseTransformerFactory();
}
public String transformXML(String inXML, String XSLTFilename) throws TransformerFactoryConfigurationError, TransformerException {
StringWriter xmlResultResource = new StringWriter();
try {
Source xslDoc = new StreamSource(XSLTFilename);
Transformer transformer = factory.newTransformer(xslDoc);
transformer.transform(new StreamSource(new StringReader(inXML)), new StreamResult(xmlResultResource));
} catch (TransformerException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return xmlResultResource.getBuffer().toString();
}
public static void main(String[] args) throws TransformerFactoryConfigurationError, TransformerException {
String xmlSourceResource = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>"
+ "<tTroubleticket>"
+ "<internalticketid>INC0789543765790</internalticketid>"
+ "</tTroubleticket>";
String xsltFilename = "C:/Users/davo/platform/src/config/xslt/DB_Query_XSLT_Test.xsl";
XSLTUtil util = new XSLTUtil();
String transformedXML = util.transformXML(xmlSourceResource, xsltFilename);
System.out.println(transformedXML);
}
}
The error is thrown at Line Number 43 in my XSLT, which is <xsl:value-of select="$troubleticketkey" />.
I am not sure what's wrong in this line.
I am not even sure if it is connecting to MySQL DB also.
Please let me know if I am missing something.
