Understanding XMLType In Oracle Database.

XMLType is used to store and manipulate XML data. You can use XMLType to define database columns and PL/SQL variables containing XML documents.Methods defined on XMLType enable you to instantiate new XMLType values,to extract portions of an XML document, and to otherwise manipulate the contents of an XML document in various ways.

 XMLType :- A built-in object type that enables you to store XML documents in a database column or in a PL/SQL variable.

 XQuery :- A query language used for retrieving and constructing XML documents.

SQL> CREATE TABLE fallsXML
  2  (
  3   fall_id NUMBER,
  4   fall XMLType
  5  );

Table created.

--The fall column in this table is of type XMLType and can hold XML data. To store XML data into this column, you must invoke the static CreateXML method, passing it your XML data. CreateXML accepts XML data as input and instantiates a new XMLType object to hold that data. The new object is then returned as the method's result, and it is that object that you must store in the column. CreateXML is overloaded to accept both VARCHAR2 strings and CLOBs as input.

SQL> INSERT INTO fallsXML VALUES (1, XMLType.CreateXML(
  2  '<?xml version="1.0"?>
  3  <fall>
  4  <name>Munising Falls</name>
  5  <county>Alger</county>
  6  <state>MI</state>
  7  <url>
  8  http://michiganwaterfalls.com/munising_falls/munising_falls.html
  9  </url>
 10  </fall>'));

1 row created.

SQL> INSERT INTO fallsXML VALUES (2, XMLType.CreateXML(
  2  '<?xml version="1.0"?>
  3  <fall>
  4  <name>Au Train Falls</name>
  5  <county>Alger</county>
  6  <state>MI</state>
  7  <url>
  8  http://michiganwaterfalls.com/autrain_falls/autrain_falls.html
  9  </url>
 10  </fall>'));

1 row created.

SQL> INSERT INTO fallsXML VALUES (3, XMLType.CreateXML(
  2  '<?xml version="1.0"?>
  3  <fall>
  4  <name>Laughing Whitefish Falls</name>
  5  <county>Alger</county>
  6  <state>MI</state>
  7  <url>
  8  http://michiganwaterfalls.com/whitefish_falls/whitefish_falls.html
  9  </url>
 10  </fall>'));

1 row created.

SQL> COMMIT;

Commit complete.

--You can query XML data in the table using various XMLType methods. The existsNode method used in the following example allows you to test for the existence of a specific XML node in an XML document. The built-in SQL EXISTSNODE function, also in the example, performs the same test.

SQL> SELECT f.fall_id
  2  FROM fallsxml f
  3  WHERE f.fall.existsNode('/fall/url') > 0;

   FALL_ID
----------
         1
         2
         3

SQL> SELECT f.fall_id
  2  FROM fallsxml f
  3  WHERE EXISTSNODE(f.fall,'/fall/url') > 0;

   FALL_ID
----------
         1
         2
         3

SQL> <<demo_block>>
  2  DECLARE
  3   fall XMLType;
  4   url VARCHAR2(100);
  5  BEGIN
  6  
  7   SELECT f.fall
  8   INTO demo_block.fall
  9   FROM fallsXML f
 10   WHERE f.fall_id = 1;
 11 
 12   url := fall.extract('/fall/url/text()').getStringVal;
 13   DBMS_OUTPUT.PUT_LINE(url);
 14  END;
 15  /

--OUTPUT :::
http://michiganwaterfalls.com/munising_falls/munising_falls.html

/*
To get the text of the URL, I invoke two of XMLType’s methods :-

	extract - Returns an XML document, of type XMLType, containing only the specified fragment of the original XML document. Use XPath notation to specify the fragment you want returned.

	getStringVal - Returns the text of an XML document.
*/

--You can even index XMLType columns to allow for efficient retrieval of XML documents based on their content. You do this by creating a function-based index, for which you need the QUERY REWRITE privilege.

SQL> CREATE INDEX falls_by_name
  2   ON fallsxml f (
  3   SUBSTR(
  4   XMLType.getStringVal(
  5   XMLType.extract(f.fall,'/fall/name/text()')
  6   ),1,80
  7  ));

Index created.

-- The getStringVal method returns a string that is too long to index, resulting in an ORA-01450 : maximum key length (3166) exceeded error. Thus, when creating an index like this, use SUBSTR to restrict the results to some reasonable length.