Sometimes the built-in transformer is not working very well, so you have to use their own transformer.
Will create its own transformer.
create or replace and compile java source named ora_20000_xmlutility_jaxp as
package ru.ora_20000.xml;
import java.io.IOException;
import java.io.StringWriter;
import java.io.Reader;
import java.io.StringReader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import oracle.sql.CLOB;
import org.w3c.dom.Document;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
public class xmlutility_jaxp {
private static Document parseDocument(String xmlText) throws Exception
{
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
factory.setNamespaceAware(true);
DocumentBuilder builder;
try {
builder = factory.newDocumentBuilder();
return builder.parse(new InputSource(new StringReader(xmlText)));
} catch (ParserConfigurationException e) {
throw new Exception("parseDocument ERROR-ParserConfigurationException: " + e.getMessage());
} catch (IOException e) {
throw new Exception("parseDocument ERROR-IOException: " + e.getMessage());
} catch (SAXException e) {
throw new Exception("parseDocument ERROR-SAXException: " + e.getMessage());
}
}
private static String processXSL(Document xmlDocument, Document xsltDocument) throws Exception
{
TransformerFactory tFactory = TransformerFactory.newInstance();
Transformer transformer;
StringWriter strWriter = new StringWriter();
try {
transformer = tFactory.newTransformer(new DOMSource(xsltDocument));
transformer.setOutputProperty(OutputKeys.INDENT, "yes");
transformer.transform(new DOMSource(xmlDocument), new StreamResult(strWriter));
return strWriter.getBuffer().toString();
} catch (TransformerConfigurationException e) {
throw new Exception("processXSL ERROR-TransformerConfigurationException: " + e.getMessage());
} catch (TransformerException e) {
throw new Exception("processXSL ERROR-TransformerException: " + e.getMessage());
}
}
public static String getStringFromClob(CLOB clob) throws Exception
{
Reader reader;
StringBuffer stringBuffer;
try
{
reader = clob.getCharacterStream();
stringBuffer = new StringBuffer();
int numchars;
char[] buffer = new char[clob.getChunkSize()];
while ((numchars = reader.read(buffer, 0, clob.getChunkSize())) != -1)
{
stringBuffer.append(buffer, 0, numchars);
}
reader.close();
} catch (SQLException e) {
throw new Exception("getStringFromClob ERROR-SQLException: " + e.getMessage());
} catch (IOException e) {
throw new Exception("getStringFromClob ERROR-IOException: " + e.getMessage());
}
return stringBuffer.toString();
}
public static CLOB getClobFromString(String string) throws Exception
{
CLOB clob = null;
Writer writer = null;
try {
Connection connection = DriverManager.getConnection("jdbc:default:connection:");
clob = CLOB.createTemporary(connection, true, CLOB.DURATION_SESSION);
writer = clob.setCharacterStream(1L);
if (string != null) { writer.write(string); }
writer.flush();
writer.close();
} catch (SQLException e) {
throw new Exception("getClobFromString ERROR-SQLException: " + e.getMessage());
} catch (IOException e) {
throw new Exception("getClobFromString ERROR-IOException: " + e.getMessage());
} finally {
if (writer != null) { writer.close(); }
}
return clob;
}
public static String transformXmlString(String xmlText, String xslText) throws Exception
{
if (xmlText == null || xslText == null)
{
return null;
}
Document xmlDoc = parseDocument(xmlText);
Document xslDoc = parseDocument(xslText);
return processXSL(xmlDoc, xslDoc);
}
public static CLOB transformXmlClob(CLOB xmlClob, CLOB xslClob) throws Exception
{
if (xmlClob == null || xslClob == null)
{
return null;
}
String xmlText = getStringFromClob(xmlClob);
String xslText = getStringFromClob(xslClob);
String xmlResult = transformXmlString(xmlText, xslText);
return getClobFromString(xmlResult);
}
}
Let's create pl\sql wrapper for java methods.
create or replace package ora_20000_xmlutility_jaxp is
function XMLTransformClob( xmlText clob
,xslText clob
) return clob;
function XMLTransform( xmlText varchar2
,xslText varchar2
) return varchar2;
end ora_20000_xmlutility_jaxp;
/
create or replace package body ora_20000_xmlutility_jaxp is
function XMLTransformClob( xmlText clob
,xslText clob
) return clob
is
language java name 'ru.ora_20000.xml.transformXmlClob(oracle.sql.CLOB, oracle.sql.CLOB) return oracle.sql.CLOB';
function XMLTransform( xmlText varchar2
,xslText varchar2
) return varchar2
is
language java name 'ru.ora_20000.xml.transformXmlString(java.lang.String, java.lang.String) return java.lang.String';
end ora_20000_xmlutility_jaxp;
/
Run the sample for testing
SQL>
SQL> -- create xml_sample_table
SQL> create table XML_SAMPLE
2 (
3 XMLTEXT clob not null
4 )
5 ;
Table created
SQL>
SQL>
SQL> -- insert sample xml
SQL> insert into XML_SAMPLE
2 values(q'~<?xml version="1.0"?>
3 <catalog>
4 <book id="bk101">
5 <author>Gambardella, Matthew</author>
6 <title>XML Developer's Guide</title>
7 <genre>Computer</genre>
8 <price>44.95</price>
9 <publish_date>2000-10-01</publish_date>
10 <description>An in-depth look at creating applications
11 with XML.</description>
12 </book>
13 <book id="bk102">
14 <author>Ralls, Kim</author>
15 <title>Midnight Rain</title>
16 <genre>Fantasy</genre>
17 <price>5.95</price>
18 <publish_date>2000-12-16</publish_date>
19 <description>A former architect battles corporate zombies,
20 an evil sorceress, and her own childhood to become queen
21 of the world.</description>
22 </book>
23 <book id="bk103">
24 <author>Corets, Eva</author>
25 <title>Maeve Ascendant</title>
26 <genre>Fantasy</genre>
27 <price>5.95</price>
28 <publish_date>2000-11-17</publish_date>
29 <description>After the collapse of a nanotechnology
30 society in England, the young survivors lay the
31 foundation for a new society.</description>
32 </book>
33 <book id="bk104">
34 <author>Corets, Eva</author>
35 <title>Oberon's Legacy</title>
36 <genre>Fantasy</genre>
37 <price>5.95</price>
38 <publish_date>2001-03-10</publish_date>
39 <description>In post-apocalypse England, the mysterious
40 agent known only as Oberon helps to create a new life
41 for the inhabitants of London. Sequel to Maeve
42 Ascendant.</description>
43 </book>
44 <book id="bk105">
45 <author>Corets, Eva</author>
46 <title>The Sundered Grail</title>
47 <genre>Fantasy</genre>
48 <price>5.95</price>
49 <publish_date>2001-09-10</publish_date>
50 <description>The two daughters of Maeve, half-sisters,
51 battle one another for control of England. Sequel to
52 Oberon's Legacy.</description>
53 </book>
54 <book id="bk106">
55 <author>Randall, Cynthia</author>
56 <title>Lover Birds</title>
57 <genre>Romance</genre>
58 <price>4.95</price>
59 <publish_date>2000-09-02</publish_date>
60 <description>When Carla meets Paul at an ornithology
61 conference, tempers fly as feathers get ruffled.</description>
62 </book>
63 <book id="bk107">
64 <author>Thurman, Paula</author>
65 <title>Splish Splash</title>
66 <genre>Romance</genre>
67 <price>4.95</price>
68 <publish_date>2000-11-02</publish_date>
69 <description>A deep sea diver finds true love twenty
70 thousand leagues beneath the sea.</description>
71 </book>
72 <book id="bk108">
73 <author>Knorr, Stefan</author>
74 <title>Creepy Crawlies</title>
75 <genre>Horror</genre>
76 <price>4.95</price>
77 <publish_date>2000-12-06</publish_date>
78 <description>An anthology of horror stories about roaches,
79 centipedes, scorpions and other insects.</description>
80 </book>
81 <book id="bk109">
82 <author>Kress, Peter</author>
83 <title>Paradox Lost</title>
84 <genre>Science Fiction</genre>
85 <price>6.95</price>
86 <publish_date>2000-11-02</publish_date>
87 <description>After an inadvertant trip through a Heisenberg
88 Uncertainty Device, James Salway discovers the problems
89 of being quantum.</description>
90 </book>
91 <book id="bk110">
92 <author>O'Brien, Tim</author>
93 <title>Microsoft .NET: The Programming Bible</title>
94 <genre>Computer</genre>
95 <price>36.95</price>
96 <publish_date>2000-12-09</publish_date>
97 <description>Microsoft's .NET initiative is explored in
98 detail in this deep programmer's reference.</description>
99 </book>
100 <book id="bk111">
101 <author>O'Brien, Tim</author>
102 <title>MSXML3: A Comprehensive Guide</title>
103 <genre>Computer</genre>
104 <price>36.95</price>
105 <publish_date>2000-12-01</publish_date>
106 <description>The Microsoft MSXML3 parser is covered in
107 detail, with attention to XML DOM interfaces, XSLT processing,
108 SAX and more.</description>
109 </book>
110 <book id="bk112">
111 <author>Galos, Mike</author>
112 <title>Visual Studio 7: A Comprehensive Guide</title>
113 <genre>Computer</genre>
114 <price>49.95</price>
115 <publish_date>2001-04-16</publish_date>
116 <description>Microsoft Visual Studio 7 is explored in depth,
117 looking at how Visual Basic, Visual C++, C#, and ASP+ are
118 integrated into a comprehensive development
119 environment.</description>
120 </book>
121 </catalog>
122 ~')
123
124 /
1 row inserted
SQL>
Xml example is taken from here "Sample XML File (books.xml)".
Simple xlst creates html.
select ora_20000_xmlutility_jaxp.xmltransformclob
(xml_sample.xmltext,
q'~<?xml version="1.0" encoding="Windows-1251"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:sdt="http://localhost/schemas/data" version="1.0">
<xsl:output method="html"/>
<xsl:template match="/">
<html>
<head>
<title>Books</title>
</head>
<body>
<table border='1'>
<tr><th>title</th><th>author</th></tr>
<xsl:for-each select="/catalog/book">
<tr>
<td><xsl:value-of select="title"/></td>
<td><xsl:value-of select="author"/></td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>~'
)
from xml_sample
And the result
<html xmlns:sdt="http://localhost/schemas/data">
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Books</title>
</head>
<body>
<table border="1">
<tr>
<th>title</th><th>author</th>
</tr>
<tr>
<td>XML Developer's Guide</td><td>Gambardella, Matthew</td>
</tr>
<tr>
<td>Midnight Rain</td><td>Ralls, Kim</td>
</tr>
<tr>
<td>Maeve Ascendant</td><td>Corets, Eva</td>
</tr>
<tr>
<td>Oberon's Legacy</td><td>Corets, Eva</td>
</tr>
<tr>
<td>The Sundered Grail</td><td>Corets, Eva</td>
</tr>
<tr>
<td>Lover Birds</td><td>Randall, Cynthia</td>
</tr>
<tr>
<td>Splish Splash</td><td>Thurman, Paula</td>
</tr>
<tr>
<td>Creepy Crawlies</td><td>Knorr, Stefan</td>
</tr>
<tr>
<td>Paradox Lost</td><td>Kress, Peter</td>
</tr>
<tr>
<td>Microsoft .NET: The Programming Bible</td><td>O'Brien, Tim</td>
</tr>
<tr>
<td>MSXML3: A Comprehensive Guide</td><td>O'Brien, Tim</td>
</tr>
<tr>
<td>Visual Studio 7: A Comprehensive Guide</td><td>Galos, Mike</td>
</tr>
</table>
</body>
</html>
Читать полностью
Мой список блогов