xmlextract

option_string

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

The options supported for the xmlextract function are:

xmlerror = {exception | null | message}
ncr = {no | non_ascii | non_server}

For a description of the ncr option, including its default value, see “XML Support for I18N”.

Exceptions

Examples

The following examples use the sample_docs table described in Appendix A, “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:

• A null value from the bookstore row

• A single “<row>...</row>” element from the publishers row

• 4 “<row>...</row>” elements from the titles row

select xmlextract('//row[price<10 | city="Boston" ]/pub_id',     
     text_doc) from sample_docs2>  
------------------------------------   
NULL            
XML Services<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'
   ----------------------------- 
   12556.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:

• If xmlerror=exception (this is the default), an exception is raised:

  select xmlextract('//row', text_doc 
     option 'xmlerror=exception')
  from sample_docs
  
  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 23.
  

• If xmlerror=null, a null value is returned:

  select xmlextract('//row', test_doc       
    option 'xmlerror=null')
  from sample_docs
  
  (0 rows affected)
  

• If xmlerror=message, a parsed XML document with an error message will be returned:

  select xmlextract('//row', test_doc      
    option 'xmlerror=message')
  from sample_docs
  ----------------------------------
  

  <xml_parse_error>The input ended before all startedtags were ended. Last tag started was 'a'</xml_parse_error>
  

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:

• The xmlerror option is used by xmlextract only when the document operand is an unparsed document.

• When the document operand is an explicit xmlparse call, the implicit or explicit xmlerror option of that call overrides the implicit or explicit xmlerror option of the xmlextract.

This command restores the sample_docs table to its original state:

delete from sample_docs
where na_doc=’invalid doc’

---

Copyright © 2011. Sybase Inc. All rights reserved.

View this document as PDF