Chapter 12. XML (eXtensible Markup Language) Support


  1. Declaration of XML Types
  2. XML Functions
  3. XML sample

Declaration of XML Types

DECLARE v_xml_obj  xml default '<?xml version="1.1"?><foo/>';
select v_xml_obj;

set v_xml_obj=xmlattributes('tom' as name);
select v_xml_obj;

XML Functions

  1. XMLPARSE ( { DOCUMENT | CONTENT } string1): Returns a value of type xml. If string1 resolves to null, then the function returns null. If you specify DOCUMENT, then string1 must resolve to a singly rooted XML document. If you specify CONTENT, then string1 must resolve to a valid XML value.
  2. XMLSERIALIZE ( { DOCUMENT | CONTENT } value_expr [[AS] type]): Returns a string or LOB containing the contents of value_expr. If you specify DOCUMENT, then the value_expr must be a valid XML document. If you specify CONTENT, then the value_expr need not be a singly rooted XML document. However it must be valid XML content. The data type specified can be a string type (VARCHAR) or CLOB . The default is CLOB.
  3. XMLCOMMENT(string1): Returns an XML comment with the specified text as content. The text cannot contain "--" or end with a "-" so that the resulting construct is a valid XML comment. If the argument is null, the result is null.
  4. XMLELEMENT([NAME] name [, XMLATTRIBUTES(value [[AS] attname] [, ... ])] [, content, ...]): Returns an XML element with the given name, attributes, and content.
  5. XMLATTRIBUTES(value [[AS] attname] [, ... ]): Returns attribute values.
  6. XMLFOREST(value [[AS] elementname] [, ... ]): Returns an XML fragment that is the concatenation of these converted arguments.
  7. XMLCONCAT(expr[,...]): Returns an XML content fragment. Null values are omitted; the result is only null if there are no nonnull arguments.
  8. XMLPI(NAME name [, value_expr]]): Return an XML processing instruction using name and optionally the evaluated result of value_expr. A processing instruction is commonly used to provide to an application information that is associated with all or part of an XML document. The application uses the processing instruction to determine how best to process the XML document. The optional value_expr must resolve to a string. If you omit value_expr, then a zero-length string is the default. The value returned by the function takes this form: Name cannot specify xml in any case combination for identifier, and cannot contain the consecutive characters ?>.
  9. XMLAGG(expr [order_by_clause]): Returns a collection of XML fragments. Any arguments that return null are dropped from the result.
  10. XML_TABLE(xmlContent[,xPath]): XML_TABLE is a powerful function that enables the easy decomposition of XML data into relational format. It can be quoted as a table in SQL.

XML sample

select * from xml_table ('<?xml version="1.0" encoding="ISO-8859-1"?><bookstore><book category="COOKING">  <title lang="en">Everyday Italian</title>  <author>Giada De Laurentiis</author>  <year>2005</year>  <price>30.00</price></book><book category="WEB">  <title lang="en">Learning XML</title>  <author>Erik T. Ray</author>  <year>2003</year>  <price>39.95</price></book></bookstore>','/bookstore');

select xmlcomment('hello');
select xmlcomment('<!--hello-->');

select xmlattributes('tom' as name);

SELECT xmlelement(name cat);

SELECT xmlelement(name cat, xmlattributes('tom' as name));

SELECT xmlelement(name cat, xmlattributes('tom' as name),'Tom is ','ca','t');

SELECT xmlelement(name "cat$tom", xmlattributes('Demo for invalid XML  name' as "a&b"));

SELECT xmlelement(name cat, xmlattributes('tom' as name),'Tom is ','ca','t');

SELECT xmlelement(name cat, xmlattributes('tom' as name),xmlelement(name abc),  xmlcomment('test'), xmlelement(name xyz));

SELECT XMLELEMENT("test", XMLELEMENT("test1", XMLATTRIBUTES(1 "test1_attribute"),NULL), XMLELEMENT("test2", XMLATTRIBUTES('' "test2_attribute"),''), XMLELEMENT("test3", XMLATTRIBUTES(NULL "test3_attribute")),':)');

SELECT XMLELEMENT("TEST",  '123',   XMLELEMENT("AA",   XMLATTRIBUTES('1235678' "test_attribute"), XMLELEMENT("BB",  XMLATTRIBUTES('z' "attr",NULL "Go"),  'XXX'),  XMLELEMENT("CC", 'XXX')),     'qwe')

SELECT XMLCONCAT(XMLELEMENT("First",'John'), XMLELEMENT("Last", 'Morgan')) AS "Name";

SELECT xmlconcat('<abc/>', '<bar>foo</bar>');

SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');

Copyright © 2003-2019 Heng Xing Tian Tai Lab | All Rights Reserved. |