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.

xmlparse_call ::= 
      xmlparse(general_string_expression
      [options_parameter][returns_type])
 options_parameter ::=  [,] option option_string
 option_string ::= basic_string_expression
 returns type ::= [,] returns {image | binary | varbinary [(integer )]}
 

If the value of the xml_data_expression is not valid XML:

• If the explicit or default options specifies xmlerror=exception, an exception is raised.

• If the explicit or default options specifies xmlerror=null, a null value will be returned.

• If the explicit or default options specifies xmlerror=message, a character string containing an XML element with thee exception messages is returned. This value is valid parsed XML.

• Global variable @@error returns the error number of the last error, whether the value of xmlerror is exception, null, or message.

If the value of the xml_data_expression is not valid XML:

• If the explicit or default options specifies xmlerror=exception, an exception is raised.

• If the explicit or default options specifies xmlerror=null, a null value will be returned.

• If the explicit or default options specifies xmlerror=message, then a character string containing an XML element with the exception message is returned. This value is valid parsed XML.

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 the following example, the xmlerror options determine the treatment of invalid XML documents by the xmlparse function:

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

  update sample_docs
  set image_doc = xmlparse(text_doc option 'xmlerror=exception')
  
  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:

  update sample_docs
  set image_doc = xmlparse(text_doc option 'xmlerror=null')
  
  select image_doc from sample_docs
  where name_doc='invalid doc'
  ------
  NULL
  

• If xmlerror=message, then parsed XML document with the error message is returned:

  update sample_docs
  set image_doc = xmlparse(text_doc option 'xmlerror=message')
  
  select xmlextract('/', image_doc) 
  from sample_docs
  where name_doc = 'invalid doc'
  ------------------------
  <xml_parse_error>The input ended before all started tags were ended.  Last tag started was 'a'</xml_parse_error>
  

This command restores the sample_docs table to its original state:

delete from sample_docs
where name_doc='invalid doc'