Home

	Expand Search

Java in Adaptive Server Enterprise

Chapter 7 Selecting Data with XQL

Chapter 7

Selecting Data with XQL

This chapter describes how you use XQL to select raw data from Adaptive Server, using the XQL language and display the results as an XML document.

isql displays only the first 50 characters of a result set that is derived from XML data. However, the examples in this chapter display the entire result set for purposes of illustration. To see the entire result set for any of the examples, use com.sybase.xml.xql.XqlDriver to run the query. See "Other usages of the XQL package". You can also use the JDBC client, which helps you to store the result as a java.lang.String.

Adaptive Server features a query engine written in Java, which you can either install in the server, or run outside the server. Running it outside the server is like running any Java program on the command line.

This chapter first addresses running the query engine as a standalone program, outside Adaptive Server. See"Installing XQL in Adaptive Server" for instructions on running the query engine inside Adaptive Server.

Accessing the XML parser

Whether you install your query engine as a standalone program or inside Adaptive Server, you must first access the XML parser. Sybase recommends the xerces.jar (vs.1.3.1) parser, available at

• $SYBASE/ASE-12_5/lib/xerces.jar (UNIX )

• %SYBASE%\5\ASE-12_5\lib/xerces.jar (Windows NT)

Xerces Java Parser .

You can also use any parser that is compliant with SAX 2.0.

Setting the CLASSPATH environment variable

To create a standalone program outside Adaptive Server, you must set your CLASSPATH environment variable to include the directories that contain xerces.jar and xml.zip. For UNIX , enter:

setenv     CLASSPATH $SYBASE/ASE-12_5/lib/xerces.jar

$SYBASE/ASE-12_5/lib/xml.zip

For Windows NT, enter:

set CLASSPATH = D:\%SYBASE%\ASE-12_5\lib\xerces.jar

D:\%SYBASE%\ASE-12_5\lib\xml.zip

Installing XQL in Adaptive Server

This section assumes you have already enabled Java in Adaptive Server. For information, see Chapter 2, "Preparing for and Maintaining Java in the Database."

installjava copies a JAR file into Adaptive Server and makes the Java classes in that JAR file available for use in the current database. The syntax is:

installjava 
 -f file_name
 [-new | -update ]
 ...

Where:

• file_name is the name of the JAR file you are installing in the server.

• new informs the server this is a new file.

• update informs the server you are updating an existing JAR file.

For more information about installjava, see the Utility Guide.

To add support for XML in Adaptive Server, you must install the xml.zip and xerces.jar files. These files are located in the directories $SYBASE/ASE-12_5/lib/xml.zip and $SYBASE/ASE-12_5/lib/xerces.jar

For example, to install xml.zip, enter:

installjava -Usa -P -Sserver_name -f $SYBASE/ASE-12_5/lib/xml.zip

To install xerces.jar, enter:

installjava -Usa -P -Sserver_name -f $SYBASE/ASE-12_5/lib/xerces.jar

To install xerces.jar in a database, you must increase the size of tempdb by 10MB.

Converting a raw XML document to a parsed version

Use the parse() method to convert and parse a raw text or image XML document and store the result. Use the alter table command to convert the raw XML document. For example:

alter table XMLTEXT add xmldoc IMAGE null
update XMLTEXT
set xmldoc = com.sybase.xml.xql.Xql.query.parse(xmlcol)

This example converts the xmlcol column of the XMLTEXT table to parsed data and stores it in the xmldoc column.

Inserting XML documents

Use the parse() method to insert an XML document, which takes the XML document as the argument and returns sybase.aseutils.SybXmlStream.

Adaptive Server has an implicit mapping between image or text data and InputStream. You can pass image or text columns to parse() without doing any casting. The parse() UDF parses the document and returns sybase.ase.SybXmlStream, which Adaptive Server uses to write the data to the image column. Adaptive Server writes this data to image columns only, not to text columns. The following is an insert statement; where XMLDAT is an image column:

insert XMLDAT

values (.., com.sybase.xml.xql.Xql.parse("<xmldoc></xmldoc>",..))

Updating XML documents

To update a document, delete the original data and then insert the new data. The number of updates to a document or portion of a document are infrequent compared to the number of reads. An update is similar to:

update XMLDAT 
set xmldoc = com.sybase.xml.xql.Xql.parse("<xmldoc></xmldoc>")

Deleting XML documents

Deleting an XML document is similar to deleting any text column. For example, to delete a table named XMLDAT, enter:

delete XMLDAT

Memory requirements for running the query engine inside Adaptive Server

Depending on the size of the XML data you want to select and present as an XQL document, you may need to increase memory. For a typical XML document of size 2K, Sybase recommends that you set the configuration parameters in Java Services to the values shown in Table 7-1. For more information on configuration parameters, see the Sybase Adaptive Server System Administration Guide.

Using XQL

XML Query Language (XQL) has been designed as a general-purpose query language for XML. XQL is a path-based query language for addressing and filtering the elements and text of XML documents, and is a natural extension to XSL syntax. XQL provides a concise, understandable notation for pointing to specific elements and for searching for nodes with particular characteristics. XQL navigation is through elements in the XML tree.

SQL and XQL are independent languages. The examples presented here apply to XQL only.

The most common XQL operators include:

• Child operator, / - indicates hierarchy. The following example returns <book> elements that are children of <bookstore> elements from the xmlcol column of the xmlimage table:

  select com.sybase.xml.xql.Xql.query("/bookstore/book", xmlcol)
  from xmlimage
  
   <xql_result>
              <book style=autobiography>
   <title>S 

• Descendant operator, // - indicates that the query searches through any number of intervening levels. That is, a search using the descendant operator finds an occurrence of an element at any level of the XML structure. The following query finds all the instances of <emph> elements that occur in an <excerpt> element:

  select com.sybase.xml.xql.Xql.query

          ("/bookstore/book/excerpt//emph",xmlcol)
  from xmlimage
  
   <xql_result>
              <emph>I</emph>
   </xql_result>

• Equals operator, = - specifies the content of an element or the value of an attribute. The following query finds all examples where "last-name = Bob":

  select com.sybase.xml.xql.Xql.query

          ("/bookstore/book/author[last-name='Bob']", xmlcol)
  from xmlimage
  
   <xql_result>
              <author>
              <first-name>Joe</first-name>
              <last-name>Bob</last-name>
              <award>Trenton Literary Review Honorable Mention</award></author>
              <author>
              <first-name>Mary</first-name>
              <last-name>Bob</last-name>
              <publication>Selected Short Stories of
              <first-name>Mary</first-name>
              <last-name>Bob</last-name></publication></author>
              <author>
              <first-name>Toni</first-name>
              <last-name>Bob</last-name>            
              <degree from=Trenton U>B.A.</degree>
              <degree from=Harvard>Ph.D.</degree>
              <award>Pulizer</award>
              <publication>Still in Trenton</publication>
              <publication>Trenton Forever</publication></author>
  "</xql_result> 

• Filter operator, [ ] - filters the set of nodes to its left, based on the conditions inside the brackets. This example finds any occurrences of authors whose first name is Mary that are listed in a book element:

  select com.sybase.xml.xql.Xql.query

          ("/bookstore/book[author/first-name = 'Mary']", xmlcol)
  from xmlimage
   <xql_result>
              <book style=textbook>
              <title>History of Trenton</title>
              <author>
              <first-name>Mary</first-name>
              <last-name>Bob</last-name>
              <publication>Selected Short Stories of
              <first-name>Mary</first-name>
              <last-name>Bob</last-name></publication></author>
   <price>55</price></book>

• Subscript operator, [index_ordinal] - finds a specific instance of an element. This example finds the second book listed in the XML document. Remember that XQL is zero-based, so it begins numbering at 0:

  select com.sybase.xml.xql.Xql.query("/bookstore/book[1]", xmlcol)
  from xmlimage 
  Query  returned true and the  result is
   <xql_result>
                      <book style=textbook>
                      <title>History of Trenton</title>
                      <author>
                      <first-name>Mary</first-name>
                      <last-name>Bob</last-name>
                      <publication>Selected Short Stories of
                      <first-name>Mary</first-name>
                      <last-name>Bob</last-name></publication></author>
                      <price>55</price></book>
   </xql_result> 

• Boolean expressions - you can use Boolean expressions within filter operators. For example, this query returns all <author> elements that contain at least one <degree> and one <award>:

  select com.sybase.xml.xql.Xql.query

          ("/bookstore/book/author[degree and award]", xmlcol)
  from xmlimage
  
   <xql_result>
              <author>
              <first-name>Toni</first-name>
              <last-name>Bob</last-name>
              <degree from=Trenton U>B.A.</degree>
              <degree from=Harvard>Ph.D.</degree>
              <award>Pulizer</award>
              <publication>Still in Trenton</publication>
              <publication>Trenton Forever</publication></author>
   </xql_result> 

Query structures that affect performance

This section describes examples that use the XML query engine in different ways.

Examples

The placement of the where clause in a query affects processing. For example, this query selects all the books whose author's first name is Mary:

select com.sybase.xml.xql.Xql.query

    ("/bookstore/book[author/first-name ='Mary']", xmlcol)

from XMLDAT

where

        com.sybase.xml.xql.Xql.query("/bookstore/book

            [author/first-name= 'Mary']", xmlcol)!=

        convert(com.sybase.xml.xql.Xql, null)>>EmptyResult

<xql_result ><book style="textbook">
    <title>History of Trenton</title>
    <author>
    <first-name>Mary</first-name>
    <last-name>Bob</last-name>
    <publication>
    Selected Short Stories of
    <first-name>Mary</first-name>
    <last-name>Bob</last-name>
    </publication>
    </author>
    <price>55</price>
 </book></xql_result>

query() is invoked twice, once in the where clause and once in the select clause, which means the query executes twice and may be slow for large documents.

Thus, you can save the result set in an object while executing the query in the where clause and then restore the result in the select clause.

Or, you can write a class like HoldString, thats concatenates the results obtained from every invocation of com.sybase.xml.xql.Xql.query(), for each XML document in each row:

declare @result HoldString

select @result = new HoldString()

select @result>>get()

from XMLDAT

where

    @result>>put(com.sybase.xml.xql.Xql.query

    ("/bookstore/book[author/first-name= 'Mary']", xmlcol))!=

convert(com.sybase.xml.xql.Xql,null)>>EmptyResult

Sybase advises that you do not store the result set in the where clause. The query does not always execute the where clause, so trying to retrieve its result in the select clause may generate an erroneous result set. HoldString is an example class.

Because Adaptive Server stores each document in a column of a given row, when the query scans a set of rows in the where clause, more than one row may satisfy the search criteria. If this occurs, the query returns a separate XML result document for each qualified row. For example, if you create the following table:

create table XMLTAB ( xmlcol image)
insert XMLTAB values 
    ( com.sybase.xml.xql.Xql.parse(<xml><A><B><C>c</C></B></A></xml>));
insert XMLTAB values 
    ( com.sybase.xml.xql.Xql.parse(<xml><D><E><C>c</C></E></D></xml>));

Then execute this query:

select com.sybase.xml.xql.Xql.query("//C", xmlcol)
from XMLTAB

You would expect to get the following result set:

<xql_result>
 <C>c</C>
 <C>c</C>
 </xql_result>

Instead, the result set returns the same row twice, once from the select clause and once from the where clause:

<xql_result>
 <C>c</C>
 </xql_result>

 <xql_result>
 <C>c</C>
 </xql_result>

Other usages of the XQL package

Sybase does not support these usages of the XQL package. These usages require JDK 1.2 or higher.

You can query XML documents from the command line, using the standalone application com.sybase.xml.xql.XqlDriver.

You can use Java package methods provided in com.sybase.xml.xql.Xql to query XML documents in Java applications. You can also use these Java package methods to query XML documents in Adaptive Server 12.5, using the Java VM feature.

com.sybase.xml.xql.XqlDriver can parse and query only XML documents stored as files on your local system. You cannot use com.sybase.xml.xql.XqlDriver to parse or query XML documents stored in a database or over the network.

com.sybase.xml.xql.XqlDriver can be useful for developing XQL scripts and learning XQL. However, Sybase recommends that you use com.sybase.xml.xql.XqlDriver only as a standalone program, and not as part of another Java application, because com.sybase.xml.xql.XqlDriver includes a main() method. A Java program can only include one main() method, and if you include com.sybase.xml.xql.XqlDriver in another Java program that includes main(), the application attempts to implement both main() methods, which causes an error in Java.

Sybase recommends that applications use the com.sybase.xml.xql.Xql class to interface with the XML query engine. The methods of this class are specified in the section "Methods in com.sybase.xml.xql.Xql".

com.sybase.xml.xql.XqlDriver syntax

The syntax for com.sybase.xml.xql.XqlDriver is:

java com.sybase.xml.xql.XqlDriver 
-qstring XQL_query
-validate true | false
-infile string
-outfile string
-help
-saxparser string

Where:

• qstring specifies the XQL query you are running.

• validate checks the validity of the XML documents.

• infile is the XML document you are querying.

• outfile is the operating system file where you are storing the parsed XML document.

• help displays the com.sybase.xml.xql.XqlDriver syntax.

• saxparser specifies the name of a CLASSPATH parser that is compliant with SAX 2.0.

For information about XQL, see "Using XQL".

This query selects all the book titles from bookstore.xml:

java com.sybase.xml.xql.XqlDriver -qstring "/bookstore/book/title" 

    -infile bookstore.xml

Query  returned true and the  result is

 <xql_result>
 <title>Seven Years in Trenton</title>
 <title>History of Trenton</title>
 <title>Trenton Today, Trenton Tomorrow</title>
 </xql_result> 

This example lists all the author's first names from bookstore.xml. XQL uses a zero-based numbering system; that is, "0" specifies the first occurrence of an element in a file.

java com.sybase.xml.xql.XqlDriver 

    -qstring "/bookstore/book/author/first-name[0]" 

    -infile bookstore.xml
Query  returned true and the  result is

 <xql_result>
        <first-name>Joe</first-name>
        <first-name>Mary</first-name>
        <first-name>Toni</first-name>
 </xql_result> 

The following example lists all the authors in bookstore.xml whose last name is "Bob":

java com.sybase.xml.xql.XqlDriver 

    -qstring "/bookstore/book/author[last-name='Bob']" 

    -infile bookstore.xmlQuery  returned true and the  result is

 <xql_result>
    <author>
    <first-name>Joe</first-name>
    <last-name>Bob</last-name>
    <award>Trenton Literary Review Honorable Mention</award></author>
    <author>
    <first-name>Mary</first-name>
    <last-name>Bob</last-name>
    <publication>Selected Short Stories of
    <first-name>Mary</first-name>
    <last-name>Bob</last-name></publication></author>
    <author>
    <first-name>Toni</first-name>
    <last-name>Bob</last-name>
    <degree from=Trenton U>B.A.</degree>
    <degree from=Harvard>Ph.D.</degree>
    <award>Pulizer</award>
    <publication>Still in Trenton</publication>
    <publication>Trenton Forever</publication></author>
 </xql_result> 

Validating your document

The valid option invokes a parser that makes sure the XML document you are querying conforms to its DTD. Your standalone XML document must have a valid DTD before you run the validate option.

For example, this command makes sure the bookstore.xml document conforms to its DTD:

java com.sybase.xml.xql.XqlDriver -qstring "/bookstore" -validate 

    -infile bookstore.xml

Using XQL to develop standalone applications

You can use XQL to develop standalone applications, JDBC clients, JavaBeans, and EJBs to process XML data. The query() and parse() methods in com.sybase.xml.xql.Xql enable you to query and parse XML documents. Because you can write standalone applications, you do not have to depend on Adaptive Server to supply the result set. Instead, you can query XML documents stored as operating system files or stored out on the Web.

The following example uses the FileInputStream() query to read bookstore.xml, and the URL() method to read a Web page named bookstore.xml which contains information about all the books in the bookstore:

String result; 
FileInputStream XmlFile = new FileInputStream("bookstore.xml");
if ((result = 
            Xql.query("/bookstore/book/author/first-name", XmlFile))
            != Xql.EmptyResult ) 
{
        System.out.println(result);
}else{
        System.out.println("Query returned false\n");
}

URL _url = new URL("http://mybookstore/bookstore.xml");
if ((result = 
            Xql.query("/bookstore/book/author/first-name",url.openStream()))
                != Xql.EmptyResult ) 
{
            System.out.println(result);
}else{
            System.out.println("Query returned false\n");

}

The following code fragment uses the Xql.query method to query the xmlcol column in the XMLTEXT file:

String selectQuery = "select xmlcol from XMLTEXT";

Statement stmt = _con.createStatement();

ResultSet rs = (SybResultSet)stmt.executeQuery(selectQuery);

String result;

InputStream is = null;

while ((rs != null) && (rs.next()))

{        

        is = rs.getAsciiStream(1);

        result = Xql.query("/bookstore/book/author", is);

}

the following example assumes that the parsed XML data is stored in an image column of the XMLDOC table. Although this application fetches an image column as a binary stream, it does not parse this during the query because it identifies the content of this binary stream as a parsed XML document. Instead, the application creates a SybXmlStream instance from it and then executes the query. All this is done using the Xql.query() method, and does not require any input from the user.

String selectQuery = "select xmlcol from XMLDOC";

Statement stmt = _con.createStatement();

ResultSet rs = (SybResultSet)stmt.executeQuery(selectQuery);

InputStream is = null;

String result

while ((rs != null) && (rs.next()))

{

        is = rs.getBinaryStream(1);

        result = Xql.query("/bookstore/book/author/first-name", is));

}

You can write EJB code fragments that serve as query engines on an EJB server.

The code fragment below includes an EJB called XmlBean. XmlBean includes the query() method, which allows you to query any XML document on the Web. In this component, query() first creats an XmlDoc object, then queries the document.

The remote interface looks like:

public interface XmlBean extends javax.ejb.EJBObject

{

            /**

        * XQL Method

    */

    public String XQL(String query, URL location) throws

java.rmi.RemoteException

;

}

public class XmlBean extends java.lang.Object implements

javax.ejb.SessionBean

{

            ....

            /***

            * XQL Method

            */

        public String XQL(String query, java.net.URL location) throws

            java.rmi.RemoteException

{

            try {

                    String result;

                    if((result = 

                        Xql.query(query, location.openStream())) !=

                        Xql.EmptyResult)

                    {

                            return (result);

                    }else{

                            return (null);

                                }

                    }catch(Exception e){

                        throw new java.rmi.RemoteException(e.getMessage()));

                    }

                }

....

}

And the client code looks like:

....

Context ctx = getInitialContext();

// make the instance of the class in Jaguar

XmlBeanHome -beanHome =

(XmlBeanHome)ctx.lookup("XmlBean");

_xmlBean = (XmlBean)_beanHome.create();

URL u = new URL("http://mywebsite/bookstore.xml");

String res= xmlBean.XQL("/bookstore/book/author/first-name",u);

XQL methods

The XQL methods that Sybase supports and provides with Adaptive Server follow. For more information on these methods see the Web sites in the Reference section of Chapter 6, "Introduction to XML in the Database".

• attribute

• comment

• element

• id

• node

• pi

• textNode

• textName

• text

• value

Methods in com.sybase.xml.xql.Xql

The following methods are specific to com.sybase.xml.xql.Xql.

parse(String xmlDoc)

Description

Takes a Java string as an argument and returns SybXmlStream. You can use this to query a document using XQL.

Syntax

parse(String xml_document)

Where:

• String is a Java string.

• xml_document is the XML document where the string is located.

Examples

Example 1

The following example:

SybXmlStream xmlStream = Xql.parse("<xml>..</xml>);)

Returns SybXmlStream.

Usage

The parser does not:

• • Validate the document if a DTD is provided.

  • Parse any external DTDs

  • Perform any external links (for example, XLinks)

  • Navigate through IDREFs

parse(InputStream xml_document, boolean validate)

Description

Takes an InputStream and a boolean flag as arguments.The flag indicates that the parser should validate the document according to a specified DTD. Returns SybXmlStream. You can use this to query a document using XQL.

Syntax

parse(InputStream  xml_document,
boolean validate)

Where:

• InputStream is an input stream.

• xml_document is the XML document where the input stream originates.

Examples

Example 1

The following example

SybXmlStream is = Xql.parse(new FileInputStream("file.xml"), true);

Returns SybXmlStream.

Usage

• A true value in the flag indicates that the parser should validate the document according to the specified DTD.

• A false value in the flag indicates that the parser does not validate the document according to the specified DTD.

• The parser does not:

  • Parse any external DTDs

  • Perform any external links (for example, XLinks)

  • Navigate through IDREFs

query(String query, String xmlDoc)

Description

Queries an XML document. Uses the XML document as the input argument.

Syntax

query(String query,String xmlDoc)

Where:

• String query is the string you are searching for.

• String xmldoc is the XML document you are querying.

Examples

Example 1

The following returns the result as a Java string:

String result= Xql.query("/bookstore/book/author",
"<xml>...</xml>");

Usage

Returns a Java string.

query(String query, InputStream xmlDoc)

Description

Queries an XML document using an input stream as the second argument.

Syntax

query(String query,InputStream xmlDoc)

Where:

• String query is the string you are searching for.

• Input Stream xmlDoc is the XML document you are querying.

Examples

Example 1

This example queries the bookstore for authors listed in bookstore.Xql.

FileInputStream xmlStream = new FileInputStream("doc.xml");
String result = Xql.query("/bookstore/book/author", xmlStream);

The following example queries an XML document on the Web using a URL as the search argument:

URL xmlURL = new URL("http://mywebsite/doc.xml");
String result = Xql.query("/bookstore/book/author", xmlURL.openStream());

Usage

Returns a Java string.

query(String query, SybXmlStream xmlDoc)

Description

Queries the XML document using a parsed XML document as the second argument.

Syntax

query(String query, SybXmlStream )

Where:

• String query is the string you are searching for.

• xmldoc is the parsed XML document you are querying.

Examples

Example 1

This example queries the bookstore for authors listed in bookstore.Xml.

SybXmlStream xmlStream = Xql.parse("<xml>..</xml>);
String result = Xql.query("/bookstore/book/author",xmlStream);

query(String query, JXml jxml)

Description

Queries an XML document stored in a JXML format.

Syntax

query(String query, JXml jxml)

Where:

• String query is the string you are searching.

• JXml jxml is an object created from the classes located in $SYBASE/ASE-12_5/samples/

Examples

Example 1

This example queries for authors in bookstore.Xql

JXml xDoc = new JXml("<xml>...</xml>");;
String result = Xql.query("/bookstore/book/author", xDoc);

Usage

Allows you to execute a query on an JXML document using XQL.

sybase.aseutils.SybXmlStream

Description

Defines an interface that an InputStream needs to access parsed XML data while querying.

Syntax

sybase.aseutils.SybXmlStream interface

com.sybase.xml.xql.store.SybMemXmlStream

Description

Holds the parsed XML document in main memory, an implementation of SybXMLStream that Sybase provides.

Syntax

com.sybase.xml.xql.store.SybMemXmlStream

Usage

The parse() method returns an instance of SybMemXmlStream after parsing an XML document.

com.sybase.xml.xql.store.SybFileXmlStream

Description

Allows you to query a file in which you have stored a parsed XML document.

Syntax

com.sybase.xml.xql.store.SybFileXmlStream {file_name}

Where file_name is the name of the file in which you stored the parsed XML document.

Examples

Example 1

In the following, a member of the RandomAccessFile reads a file and positions the data stream:

SybXmlStream xis = Xql.parse("<xml>..</xml>");
FileOutputStream ofs = new FileOutputStream("xml.data");
((SybMemXmlStream)xis).writeToFile(ofs);

SybXmlStream is = new SybFileXmlStream("xml.data");
String result = Xql.query("/bookstore/book/author", is);

setParser(String parserName)

Description

This static method specifies the parser that the parse method should use. You should make sure that the specified parser class is accessible through the CLASSPATH and is compliant with SAX 2.0.

Syntax

setParser (String parserName)

Where string is the name of the parser class.

Examples

Example 1

Xql.setParser("com.yourcompany.parser")

reSetParser

Description

This static method resets the parser to the default parser that Sybase supplies (xerces.jar, Version. 1.3.1).

Syntax

reSetParser

Examples

Example 1

This example resets your parser to the Sybase default parser.

xql.resetParser()