OracleXMLQuery class is to convert results from queries on the Oracle Database to XML format, it also provides a useful method that returns an W3C XML Schema for that same XML format based on the provided database query.The API documentation for OracleXMLQuery includes a simple code example of how the class
OracleXMLQuery can be used to return XML corresponding to the provided database query. As that example shows, the OracleXMLQuery.getXMLString() method can be used to easily obtain the query results in XML format as long as a valid database connection (java.sql.Connection) and a valid query (in Java String format) is provided to the OracleXMLQuery constructor.The following code (OracleXMLQueryExample) is a class that demonstrates several key features of
OracleXMLQuery with some additional convenience methods.
package oraclexmlexamples;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.xml.sql.query.OracleXMLQuery;
import org.w3c.dom.Document;
public class XQueryDbAccess
{
public XQueryDbAccess ()
{
}
/**
* Place passed-in XML Schema documents on OutputStream and provide that
* OutputStream to caller.
*
* @param aXmlSchema XML Schema associated with an OracleXMLQuery.
* @return OuputStream containing XML Schema.
*/
public OutputStream outputSchema( final Document[] aXmlSchema )
{
final int arraySize = aXmlSchema.length;
final ByteArrayOutputStream os = new ByteArrayOutputStream();
for ( int i=0; i < arraySize; ++i )
{
try
{
os.write(aXmlSchema[i].toString().getBytes());
}
catch (IOException ioEx)
{
System.err.println(ioEx.getMessage());
}
}
return os;
}
/**
* Provide database connection.
*
* @return Database connection.
*/
public Connection getConnection()
{
Connection dbConnection = null;
try
{
DriverManager.registerDriver(
new oracle.jdbc.driver.OracleDriver());
dbConnection = DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:XE","hr","hr");
}
catch (SQLException sqlExGetConn)
{
System.err.println( "Unable to acquire local database connection - "
+ sqlExGetConn.getMessage() );
}
return dbConnection;
}
/**
* Return provided SQL query results in XML format with specified rowset and
* row tags.
*
* @param aQuery The SQL query to be executed.
* @param aRootTag The label for the XML's root tag.
* @param aRowTag The XML tag for each row.
*/
public String getQueryResultsInXml( final String aQuery,
final String aRootTag,
final String aRowTag )
{
OracleXMLQuery qry = new OracleXMLQuery( getConnection(), aQuery );
qry.useLowerCaseTagNames(); // all letters in tag name are lowercase
qry.setRowsetTag( aRootTag );
qry.setRowTag( aRowTag );
Document[] xmlSchema = qry.getXMLSchema();
System.out.println(outputSchema(xmlSchema));
return qry.getXMLString();
}
public static void main ( String[] args )
{
XQueryDbAccess xQueryDbAccess = new XQueryDbAccess();
final String allPresentersQuery = "select * from employees";
System.err.println(
xQueryDbAccess.getQueryResultsInXml(
allPresentersQuery, "Employees", "Employee") );
}
}
The highlighted code above shows the
OracleXMLQuery class in action. The root tag of the generated XML will be "Employees" (note the plural) and each row of the database returned results will be in an element named "Employee" (note that singular) because these are specified via the OracleXMLQuery.setRowsetTag(String) and OracleXMLQuery.setRowTag(String) methods respectively. In my example above, I also used the OracleXMLQuery.useLowercaseTagNames() method to have all constructed XML tags (except those I explicitly defined for rowset and row) to be all lowercase. Had I not specified this, all of these same elements would have been in all uppercase letters, which I do not like for XML tags.A small portion of the XML generated from this query on the hr/hr database via
OracleXMLQuery.getXMLString() is shown next (it is not all shown because there are 107 individual employees, but showing more than a few of the employees entries does not provide significant additional benefit).
<?xml version = '1.0'?>
<Employees>
<Employee num="1">
<employee_id>100</employee_id>
<first_name>Steven</first_name>
<last_name>King</last_name>
<email>SKING</email>
<phone_number>515.123.4567</phone_number>
<hire_date>6/17/1987 0:0:0</hire_date>
<job_id>AD_PRES</job_id>
<salary>24000</salary>
<department_id>90</department_id>
</Employee>
<Employee num="2">
<employee_id>101</employee_id>
<first_name>Neena</first_name>
<last_name>Kochhar</last_name>
<email>NKOCHHAR</email>
<phone_number>515.123.4568</phone_number>
<hire_date>9/21/1989 0:0:0</hire_date>
<job_id>AD_VP</job_id>
<salary>17000</salary>
<manager_id>100</manager_id>
<department_id>90</department_id>
</Employee>
<Employee num="3">
<employee_id>102</employee_id>
<first_name>Lex</first_name>
<last_name>De Haan</last_name>
<email>LDEHAAN</email>
<phone_number>515.123.4569</phone_number>
<hire_date>1/13/1993 0:0:0</hire_date>
<job_id>AD_VP</job_id>
<salary>17000</salary>
<manager_id>100</manager_id>
<department_id>90</department_id>
</Employee>
. . .
. . .
. . .
<Employee num="106">
<employee_id>205</employee_id>
<first_name>Shelley</first_name>
<last_name>Higgins</last_name>
<email>SHIGGINS</email>
<phone_number>515.123.8080</phone_number>
<hire_date>6/7/1994 0:0:0</hire_date>
<job_id>AC_MGR</job_id>
<salary>12000</salary>
<manager_id>101</manager_id>
<department_id>110</department_id>
</Employee>
<Employee num="107">
<employee_id>206</employee_id>
<first_name>William</first_name>
<last_name>Gietz</last_name>
<email>WGIETZ</email>
<phone_number>515.123.8181</phone_number>
<hire_date>6/7/1994 0:0:0</hire_date>
<job_id>AC_ACCOUNT</job_id>
<salary>8300</salary>
<manager_id>205</manager_id>
<department_id>110</department_id>
</Employee>
</Employees>
The code example also included a call to the
OracleXMLQuery.getXMLSchema() method. This method provides an easy mechanism for obtaining a W3C XML Schema document defining the grammar for the XML generated above via the OracleXMLQuery.getXMLString(String) method. The generated XML Schema for this query is shown next.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Employees">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Employee" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="employee_id" type="xsd:integer" minOccurs="0"/>
<xsd:element name="first_name" nillable="true" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="20"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="last_name" nillable="true" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="25"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="email" nillable="true" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="25"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="phone_number" nillable="true" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="20"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="hire_date" type="xsd:string" minOccurs="0"/>
<xsd:element name="job_id" nillable="true" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="10"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="salary" type="xsd:double" nillable="true" minOccurs="0"/>
<xsd:element name="commission_pct" type="xsd:float" nillable="true" minOccurs="0"/>
<xsd:element name="manager_id" type="xsd:integer" nillable="true" minOccurs="0"/>
<xsd:element name="department_id" type="xsd:integer" nillable="true" minOccurs="0"/>
</xsd:sequence>
<xsd:attribute name="num" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
As the example above and the XML output and XML Schema output shown above demonstrate, it is very simple to generate XML and XML Schema from an Oracle database SQL query using the
OracleXMLQuery class.The primary reason one might choose not to use
OracleXMLQuery is that, as part of Oracle's XML SQL Utility (XSU), it has always been a proprietary, non-standard approach to generating XML from an Oracle relational database query. However, when circumstances warrant (proprietary nature is not an issue), this is about as easy as it gets for simply converting general SQL query results into XML.As discussed in Mastering XML Generation in Oracle Database 10g Release 2, Oracle has been part of the efforts to standardize XML generation in SQL with Part 14 of the SQL 2003 and SQL 2005 standards. Some commonly used SQL/XML functions include
XMLElement() [including XMLAttributes() clause] and XMLForest(). These SQL/XML functions are a little more involved than using the OracleXMLQuery class, but they are standardized and are more flexible than using that class. I briefly discussed Oracle's implementation of SQL/XML at RMOUG Training Days 2004 in the presentation To Oracle XML and Beyond: PDF and Pictures.
No comments:
Post a Comment