Overview
XML data management in Sybase Adaptive Server Enterprise is
implemented with the XML Management Package. This tutorial will cover the database’s role in XML data management, ASE 12.5 data management support, and XML processing in ASE 12.5. We’ll start with the role of a database in XML data management.
Database’s Role in XML Data Management
Most XML processing without a database is done in the
application layer. XML processing in the application layer has its
disadvantages. Parsing XML in the application layer doesn’t include storing and querying the parsed XML document. Querying XML data with XPath and XQuery with
a query engine isn’t supported. Transforming data with a XSLT processor and
storing data in a file system is less optimal than database-based
transformation and storage.
A database may be used for storing and indexing XML data, querying XML data, and mapping and transforming XML data.
XML Storage and Indexing
An XML document may be parsed and the data in the document
may be stored in a relational database. The most optimal way of storing an XML
document is in a database that supports integrated XML data management. A
database integrated with XML data management provides methods for storing,
indexing, and querying XML data.
XML Query
A database user may want to query a database for XML data
stored in a database. A database query can span multiple XML documents. A
database integrated with XML data management provides a query engine for
querying XML data based on SQL/XML, XQuery, and XPath.
XML Mapping and Transformation
XML data stored in a database may have to be transformed
into another format. A database integrated with XML data management supports
the transformation of SQL/XML data.
ASE 12.5 XML Data Management Support
Adaptive Server Enterprise 12.5 supports XML storage, XML
indexing, XML querying, and XML mapping and transformation. XML data management
in Adaptive Server Enterprise is implemented with the XML Management Package.
XML Storage and Indexing
ASE 12.5 integrates XML data management with the RDBMS. Optimal
methods are used for storing, indexing, and querying XML data instead of the
RDBMS-based storing, indexing, and querying methods. XML data is stored like
SQL data. Indexing XML elements, attributes, and paths is supported.
XML Query
ASE query engines support querying data based on SQL/XML
(SQLX), XPath, and XQuery. ASE provides for the complete interchangeability of
SQL-XML data. SQL operations can be done on XML data and XML operations can be
done on SQL data.
XML Mapping and Transformation
ASE supports XML mapping and transformation. With Transact
SQL, XML output can be produced from data in an RDBMS. ASE supports SQLX,
XPath, and Xquery, which may be required in data transformation. An XSLT engine
can be deployed in ASE to transform the data. SQL and XML data can be
transformed into another format.
XML processing in ASE is done with XQL and the ResultSetXml class.
XQL
With XQL (the XML query language), SQL data in a database can be represented as an XML document. XQL is implemented with the com.sybase.xml.xql.Xql class. The XQL class is the interface to the XML query engine.
Parse (String xmlDoc)
‘xmlDoc’ is a String representing an XML document. The
return type of the parse() method is SybXmlStream, which may be used to query a
document with XQL. For example, an XML document stored in a database table
(XMLDAT) column (xmldoc) can be updated with the parse() method as shown below.
update XMLDAT
set
xmldoc=com.sybase.xml.xql.Xql.parse(“<xmldoc></xmldoc>”)
parse(InputStream xml_document, boolean validate)
- ‘xml_document’ is an InputStream representing an XML document.
- If ‘validate’ is true an XML document is validated with a specified DTD.
The return type of the parse() method is SybXmlStream, which can be used to query a document with XQL. For example, an XML document stored in a database table (XMLDAT) column (xmldoc) can be updated with the parse() method as shown below.
update XMLDAT
set xmldoc=com.sybase.xml.xql.Xql.parse(new
FileInputStream(“file.xml”), true);
setParser(String parserName)
- The setParser() method specifies the parser for the parse() method.
- The default parser is xerces-1.3.1
query(String query, String xmlDoc)
- ‘query’ is a String representing an XML element or attribute. The parameter ‘xmlDoc’ is the XML document being queried. The return type of the query() method is a String.
For example, an element or an attribute can be retrieved from an XML document as shown here.
String result=Xql.query(“catalog/journal”, “<xml>...</xml>”);
query(String query, InputStream xmlDoc)
- ‘query’ is a String representing an XML element or attribute. ‘xmlDoc’ is an InputStream representing an XML document. The return type of the query() method is a String.
For example, an element or an attribute can be retrieved from an XML document.
String result=Xql.query(“/catalog/journal”, “new FileInputStream(“file.xml”)”);
ResultSetXml Class
A ResultSetXml class is a subclass of the JXML class. An XML
document can be generated from a SQL result set with the xml.resultset.ResultSetXml
class. The ResultSetXml class is also used to modify the XML document generated
from a SQL result set. The modified ResultSetXml object can be converted to SQL
data in the database. A ResultSetXml object is created from an XML ResultSet
document or SQL query. An XML ResultSet document is an XML document that
validates with the XML ResultSet DTD. The XML ResultSet DTD is illustrated in
Listing 1.
Generating ResultSetXml from an XML ResultSet Document
A ResultSetXml object can be generated from an XML ResultSet document.
xml.resultset.ResultSetXml rsx = new xml.resultset.ResultSetXml(“XmlDocument”);
- ‘XmlDocument’ is a String variable containing an XML ResultSet document.
The ResultSetXml(“Xml ResultSet document”) constructor also validates the XML ResultSet document.
Generating ResultSetXml from a SQL Result Set
With the ResultSetXml class an XML ResultSet document can be generated from a SQL query as shown here.
xml.resultset.ResultSetXml
rsx =
new
xml.resultset.ResultSetXml (query, cdata
Columns, colNames, server);
- query is an SQL query that returns a result set.
- cdataColumns indicates which columns should be XML CDATA sections.
- colNames indicates if the resulting XML should specify “name” attribute in the “Columns” element.
- server specifies the Adaptive server on which to execute the query.
The ResultSetXml class has methods to access and update the elements of an XML document and can be used to validate an XML result set document with the XML ResultSet DTD.
String getColumn(int rowNumber, int columnNumber)
- ‘rowNumber’ is the index of a row in the result set.
- ‘columnNumber’ is the index of a column in the result set.
For example, Select rsx>>getColumn(3,4) returns the String value of the fourth column in the third row. rsx is a ResultSetXml variable.
String getColumn(int rowNumber, int columnName)
- ‘rowNumber is the index of a row in the result set.
- ‘columnNumber’ is the name of a column in the result set. For example, Select rsx>>getColumn(3, “columnName”) returns the String value of the ‘columnName’ coulmn in the third row.rsx is a ResultSetXml variable.
void setColumn(int rowNumber, int columnNumber, newValue)
- ‘rowNumber’ and ‘columnNumber’ are the same as for getColumn() methods.
- The setColumn() methods sets the value of the specified column to ‘newValue’.
For example, Select rsx=rsx>>setColumn(3,4,”new value”) sets the column value of the column in the fourth column and the third row to ‘new value.’
void setColumn(int rowNumber, String columnName, newValue)
- ‘rowNumber’ and ‘columnNumber’ are as specified for getColumn() methods.
- The setColumn() method sets the value of the specified column name to ‘newValue.’
For example, select rsx=rsx>>setColumn(3, “columnName”, “new value”) sets the column value of ‘columnName’ column in the third row to ‘new value.’
Conclusion
The Adaptive Server Enterprise 12.5 has integrated support
for XML data management.
XML data can be stored in a database and data in a database can be converted into an XML document. XQL and ResultSetXml classes are used to store, index, and query XML data in a database.