Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search
Chapter 1 Introduction to XML Services [Table of Contents] Chapter 3 XML Language and XML Query Language

XML Services in Adaptive Server Enterprise

[-] Chapter 2 XML Query Functions

Chapter 2

XML Query Functions

This chapter describes the XML query functions in detail, and describes the general format of the option_string parameter.

XML query functions

This section describes the SQL extensions for accessing and processing XML documents in SQL statements. The functions are as follows:

Table 2-1: XML query functions

Function

Description

xmlextract

A built-in function that applies an XML query expression to an XML document and returns the selected result.

xmltest

A SQL predicate that applies an XML query expression to an XML document and returns the boolean result.

xmlparse

A built-in function that parses an XML document for more efficient processing.

xmlrepresentation

A built-in function that determines whether a given image column contains a parsed XML document.

Example sections

The descriptions of these functions include examples that reference Appendix B, "The sample_docs Example Table," which includes a script for creating and populating the table.

xmlextract

A built-in function that applies the XML_query_expression to the xml_data_expression and returns the result. This function resembles a SQL substring operation.

Syntax

xmlextract_expression ::=
xmlextract (xml_query_expression,xml_data_expression
      [optional_parameters])
 xml_query_expression ::=basic_string_expression 
 xml_data_expression ::= general_string_expression
 optional_parameters ::=
     options_parameter
     | return_type
     | options_parameterreturn_type
 options_parameter ::= [,] option option_string
 returns_type ::= [,] returns {varchar[(integer)] | text | image }
 option_string ::= [,] basic_string_expression

Description

See Chapter 3, "XML Language and XML Query Language,"for the following topics:

option_string

The general format of the option_string is described in option_strings: general format.

The option supported for the xmlextract function is:

 xmlerror = {exception| null| message}

Exceptions

If the value of the xml_data_expression is not not valid XML, or is an all blank or empty string:

If the return_type of the xmlextract_expression is varchar and the runtime result of evaluating the xml_query_expression parameter is longer than the maximum length of a varchar, then an exception is raised: 

 result exceeded maximum varchar length

Examples

The following examples use the sample_docs table described in Appendix B, "The sample_docs Example Table".

This example selects the title of documents that have a bookstore/book/price of 55 or a bookstore/book/author/degree whose from attribute is "Harvard".

select xmlextract('/bookstore/book[price=55        
   | author/degree/[@from="Harvard"] ]/title'
     text_doc )
from sample_docs
------------------------------------------------------ 
 <title>History of Trenton</title> 
 <title>Trenton Today, Trenton Tomorrow</title>   

NULL 

NULL

The following example selects the row/pub_id elements of documents whose row elements either have a price element that is less than 10 or a city element equal to "Boston". This query returns three rows:

select xmlextract('//row[price<10 | city="Boston" ]/pub_id',     
     text_doc) from sample_docs2>  
------------------------------------   
NULL            

 <pub_id>0736</pub_id>

 <pub_id>0736</pub_id>   
 <pub_id>0877</pub_id>   
 <pub_id>0736</pub_id>   
 <pub_id>0736</pub_id>     

(3 rows affected)

The following example selects the price of "Seven Years in Trenton" as an integer. This query has a number of steps.

  1. To select the price of "Seven Years in Trenton" as an XML element: 

    select xmlextract 
('/bookstore/book[title="Seven Years in Trenton"]/price',text_doc)
from sample_docs
where name_doc='bookstore' 
--------------------------------------
 <price>12</price>

  2. The following attempts to select the full price as an integer by adding a returns integer clause:

    select xmlextract  
     ('/bookstore/book[title="Seven Years in Trenton"]/price',
    text_doc returns integer)
    from sample_docs
    where name_doc='bookstore' 
Msg 249, Level 16, State 1:
Line 1:
Syntax error during explicit conversion of VARCHAR value '<price>12</price>' to an INT field.

  3. To specify a returns clause with a numeric, money, or date-time datatype, the XML query must return value suitable for conversion to the specified datatype. The query must therefore use the text() reference to remove the XML tags:

    select xmlextract
  ('/bookstore/book[title="Seven Years in Trenton"]/price/text()',
  text_doc returns integer)
  from sample_docs
  where name_doc='bookstore'
-----------
         12

  4. To specify a returns clause with a numeric, money, or date-time datatype, the XML query must also return a single value, not a list. For example, the following query returns a list of prices:

    select xmlextract
    ('/bookstore/book/price',   
    text_doc)
    from sample_docs
    where name_doc='bookstore'
-----------
 <price>12</price>
 <price>55</price>
 <price intl="canada" exchange="0.7">6.50</price>

  5. Adding the text() reference yields the following result:

    select xmlextract
('/bookstore/book/price/text()',
text_doc)
from sample_docs
where name_doc='bookstore'
----------------------------- 
12 55 6.50

  6. Specifying the returns integer clause produces an exception, indicating that the combined values aren't suitable for conversion to integer:

    select xmlextract
   ('/bookstore/book/price/text()',
   text_doc returns integer)
   from sample_docs
   where name_doc='bookstore'
Msg 249, Level 16, State 1:
Line 1:
Syntax error during explicit conversion of VARCHAR value '12556.50' to an INT field.

To illustrate the xmlerror options, the following command inserts an invalid document into the sample_docs table:

insert into sample_docs (name_doc, text_doc) 
values ('invalid doc', '<a>unclosed element<a>)

(1 row affected)

In the following example, the xmlerror options determine the treatment of invalid XML documents by the xmlextract function:

The xmlerror option is used by xmlextract only when the xml_data_expression is a varchar or text expression, or an image expression whose value is an unparsed XML document. In other words, the xmlerror option doesn't apply to a document that is a parsed XML document or to a document returned by an explicit nested call by xmlparse.

For example, in the following xmlextract call, the xml_data_expression is an unparsed character-string document, so the xmlerror option applies to it. The document is invalid XML, so an exception is raised, and the xmlerror option indicates that the exception message should be returned as an XML document with the exception message:

select xmlextract('/',  '<a>A<a>'  option'xmlerror=message')
---------------------------------------------------
 <xml_parse_error>The input ended before all started tags were ended.    
   Last tag started was 'a'</xml_parse_error>

In the following xmlextract call, the xml_data_expression is returned by an explicit call by the xmlparse function (see section xmlparse). Therefore, the default xmlerror option of the explicit xmlparse call applies, rather than the xmlerror option of the outer xmlextract call. That default xmlerror option is exception, so the explicit xmlparse call raises an exception:

select xmlextract('/', xmlparse('<a>A<a>')
     option 'xmlerror=message'))
---------------------------------------------------
Msg 14702, Level 16, State 0:
Line 2:
XMLPARSE(): XML parser fatal error   
 <<The input ended before all started tags were ended.
 Last tag started was 'a'>> at line 1, offset 8.

To apply the xmlerror=message option to the explicit nested call of xmlparse, specify it as an option in that call:

select xmlextract('/',       
   xmlparse('<a>A<a>' option 'xmlerror=message'))
----------------------------------------------------
 <xml_parse_error>The input ended before all started
tags were ended. Last tag started was
'a'</xml_parse_error>

To summarize the treatment of the xmlerror option for unparsed XML documents and nested calls of xmlparse:

This command restores the sample_docs table to its original state:

delete from sample_docs
where na_doc='invalid doc'

xmltest

A predicate that evaluates the XML query expression, which can reference the XML document parameter, and returns a Boolean result. Similar to a SQL like predicate.

Syntax

xmltest_predicate ::=
      xml_query_expression [not] xmltest xml_data
      [option option_string] xml_data ::=
      xml_data_expression | (xml_data_expression)
 xml_query_expression::= basic_string_expression
 xml_data_expression ::= general_string_expression
 option_string ::= basic_string_expression

Description

option_string

The general format of the option_string is described in option_strings: general format.

The option supported for the xmltest predicate is xmlerror = {exception | null}.

The message alternative, which is supported for xmlextract and xmlparse, is not valid for xmltest. See the Exceptions section.

Exceptions

If the value of the xml_data_expression is not valid XML, or is an all blank or empty string:

Examples

These examples use the sample_docs table described in Appendix B, "The sample_docs Example Table. ".

This example selects the name_doc of each row whose text_doc contains a row/city element equal to "Boston".

select name_doc from sample_docs
where  '//row[city="Boston"]' xmltest text_doc 
  name_doc      
------------------------ 
  publishers        

(1 row affected)
In the following example the xmltest predicate
returns false/true,
for a Boolean false/true result
and for an empty/not-empty result. 
-- A boolean true is 'true':
select case when '/a="A"' xmltest '<a>A</a>'
            then 'true' else 'false' end2>        
-----  
true  

-- A boolean false is 'false'
select case when '/a="B"' xmltest '<a>A</a>'
            then 'true' else 'false' end
-----  
false

-- A non-empty result is 'true'
select case when '/a' xmltest '<a>A</a>'             
             then 'true' else 'false' end       

-----  true  
-- An empty result is 'false'
select case when '/b' xmltest '<a>A</a>'
            then 'true' else 'false' end 
-----  
false

-- An empty result is 'false' (second example)
select case when '/b="A"' xmltest '<a>A</a>'
            then 'true' else 'false' end        
-----  
false
 
To illustrate the xmlerror options, the
following command inserts an invalid document into the sample_docs table:
insert into sample_docs (name_doc, text_doc) 
values ('invalid doc', '<a>unclosed element<a>)

(1 row affected)
In the following examples, the xmlerror options
determine the treatment of invalid XML documents by the xmltest predicate.
This command restores the sample_docs table to its
original state:
delete from sample_docs
where name_doc='invalid doc'
xmlparse
A built-in function that parses the XML document passed as
a parameter, and returns an image value that
contains a parsed form of the document.
Syntax
xmlparse_call ::= 
      xmlparse(general_string_expression   [options_parameter])
 options_parameter ::=  [,] option option_string
 option_string ::= basic_string_expression
Description
See Chapter 3, "XML Language and XML Query Language,"for the following topics: 
Option
Exceptions
If the value of the xml_data_expression is
not valid XML, then: 
Examples
These examples use the sample_docs table described
in 
As created and initialized, the text_doc column of
the sample_docs table contains documents, and the image_doc column
is null. You can update the image_doc columns to contain
parsed XML versions of the text_doc columns: 
update sample_docs
set image_doc = xmlparse(text_doc) 

(3 rows affected)
You can then apply the xmlextract function
to the parsed XML documents in the image column
in the same way as you apply it to the unparsed XML documents in
the text column. Operations on parsed XML documents
generally execute faster than on unparsed XML documents.
select name_doc,   
  xmlextract('/bookstore/book[title="History of Trenton"]/price', text_doc)    
    as extract_from_text_doc,  
  xmlextract('/bookstore/book[title="History of Trenton"]/price', image_doc)    
    as extract_from_image_doc
from sample_docs

name_doc   extract_from_text_doc  extract_from_image_doc
----------  ---------------------  ------------------------ 
bookstore   <price>55</price>     <price>55</price>               
publishers  NULL                  NULL                    
titles      NULL                  NULL       
(3 rows affected)
To illustrate the xmlerror options, this
command inserts an invalid document into the sample_docs table
insert into sample_docs (name_doc, text_doc)
values ('invalid doc', '<a>unclosed element<a>)

(1 row affected)
In this example, the xmlerror options determine
the treatment of invalid XML documents by the xmlparse function:
This command restores the sample_docs table to its
original state:
delete from sample_docs
where name_doc='invalid doc'
xmlrepresentation
Examines the image parameter, and returns
an integer value indicating whether the parameter contains parsed
XML data or other sorts of image data.
Syntax
xmlrepresentation_call::= 
xmlrepresentation(image_expression)
Description
Examples
These examples use the sample_docs table described
in Appendix B, "The sample_docs Example Table. ".
This example illustrates the basic xmlrepresentation function.
-- Return a non-zero value 
-- for a document that is not parsed XML
select xmlrepresentation(        
        xmlextract('/', '<a>A</a>' returns image)

-----------            
1

-- Return a zero for a document that is parsed XML
select xmlrepresentation(        
        xmlparse(         
          xmlextract('/', '<a>A</a>' returns image))
----------- 
0
Columns of datatype image can contain
both parsed XML documents (generated by the xmlparse function)
and unparsed XML documents.   After the update commands
in the following example, the image_doc column
of  the sample_docs table contains
a parsed XML document for the titles document,
an unparsed (character-string) XML document for the bookstore document,
and a null for the publishers document (the
original value).
update sample_docs 
set image_doc = xmlextract('/', text_doc returns image)
where name_doc = 'bookstore'

update sample_docs
set image_doc = xmlparse(text_doc)
where name_doc = 'titles'
You can use the xmlrepresentation function
to determine whether the value of an image column
is a parsed XML document:
select name_doc, xmlrepresentation(image_doc)from sample_docs 

name_doc   
---------     -----------  
bookstore     1  
publishers    NULL 
titles        0 

(3 rows affected)
You can update an image column and set
all of its values to parsed XML documents. If the image column
contains a mixture of parsed and unparsed XML documents, a simple
update raises an exception.
update sample_docs set image_doc = xmlparse(image_doc)
Msg 14904, Level 16, State 0:
Line 1:
XMLPARSE: Attempt to parse an already parsed XML    document.
You can avoid such an exception by using the xmlrepresentation function:
update sample_docs
set image_doc = xmlparse(image_doc)
where xmlrepresentation(image_doc) != 0

(1 row affected)
The following command restores the sample_docs table
to its original state.
update sample_docs
set image_doc = null
option_strings: general format
This section specifies the general format, syntax and processing
of option string parameters in XML Services. Actions of individual
options are described in the functions that reference them.Any function
that has an option_string parameter
accepts the union of all options, and ignores any options that do
not apply to that particular function.
For example, forxmlj does not have an XML
document parameter, but it still accepts an option_string containing
the xmlerror option (which specifies actions
for invalid XML operands).This "union options" approach
lets you use a single option_string variable
for all XML Services functions. 
Syntax
option_string::= basic_string_expression
Description
Table  2-2:  Option string values
Option name 
Option value 
Function
binary
hex | base64
forxmlj and for
xml clause
columnstyle
element | attribute
forxmlj and for
xml clause
dtdvalidate
yes | no
xmlparse
format
yes | no
forxmlj and for
xml clause
header
yes | no 
forxmlj and for
xml clause
incremental
yes | no
for xmlj clause
nullstyle
attribute | omit
forxmlj and for
xml clause
nullclause
null | empty
forsqlcreatej
forsqlscriptj
prefix
SQL name (C)
forxmlj and for
xml clause
root
yes | no
forxmlj and for
xml clause
rowname
SQL name (row)
forxmlj and for
xml clause
schemaloc
quoted string with a URL
forxmlj and for
xml clause
statement
yes | no
for xmlj and forxml clause
tablename
SQL name (resultset)
forxmlj and for
xml clause
targetns
quoted string with a URL
forxmlj and for
xml clause
xmlerror
exception | null | message
all functions with XML operands
The defaults of options that specify
keywords are underlined. The defaults of options that
specify SQL names are parenthesized. The defaults of options that
specify string values are the empty string, or a single-space character.

      
      

      

        
          Chapter 1 Introduction to XML Services
          
              [Table of Contents]
          Chapter 3 XML Language and XML Query Language