To Store and Select XML Data

Some of the member functions of XMLType are

createXML      - creates XMLTYPE instance.
existsNode     - Checks if XPath can find valid nodes.
XMLExists      - Similar to existsNode with some minor differences.
extract()      - Uses XPATH to extract XML fragment
isFragment     - check if a document is a fragment
getClobVal     - gets document as a CLOB
getStringVal   - gets value as a string
getNumberVal   - gets numeric value as a number
isSchemaBased  - returns 1 if schema based(0 if not)
isSchemaValid  - returns TRUE if XMLType is valid
schemaValidate - validates XMLType using Schema
Transform      - apply XSL stylesheet to XMLType
XMLType        - constructs an xmlType instance from CLOB, VARCHAR2 or object

Below are a few examples of using existsNode, XMLExists and extract functions when querying XMLType datatype columns from the database. 

Data is inserted into the table by reading the XML document from a predefined directory on the database server as shown below. 

--Create an oracle directory on the database server pointing to the directory in which the xml to be inserted will be stored.

CREATE DIRECTORY xmldir AS 'D:\temp\xml';

Store the file containing data in xml format in the directory defined by XMLDIR on the database server.

--A Sample EmployeeDetails.xml file for employee details is shown below.

<EmployeeInformation 
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:noNamespaceSchemaLocation="http://www.oracle.com/xdb/po.xsd">
  <Employee>208</Employee>
  <Actions>
    <Action>
      <Name>William</Name>
	  <Id>208</Id>
      <Date xsi:nil="true"/>
    </Action>
  </Actions>
  <Reject/>
  <Country>Brazil</Country>
  <Company>Oracle Corporation Pvt Ltd</Company>
  <EmployeeDetails>
    <name>William Gietz</name>
    <email>wgietz2@oramail.com</email>
    <telephone>515.123.8181</telephone>
	<job>Public Accountant</job>
	<salary>8390</salary>
	<hire_date>24-AUG-2001</hire_date>
  </EmployeeDetails>
  <EmpJobHistoryDetails>
    <Job Id="AD_ASST">
      <Description>Administration Assistant</Description>
      <JobDetail Department="Executive" StartDate="17-SEP-1995" EndDate="17-JUN-2001"/>
    </Job>
    <Job Id="FI_ACCOUNT">
      <Description>Accountant</Description>
      <JobDetail Department="Accounting" StartDate="18-JUN-2001" EndDate="30-JUN-2002"/>
    </Job>
    <Job Id="AC_ACCOUNT">
      <Description>Accountant</Description>
      <JobDetail Department="Accounting" StartDate="01-JUL-2002" EndDate="30-NOV-2016"/>
    </Job>
  </EmpJobHistoryDetails>
</EmployeeInformation>

--Create the table having an XMLType datatype to store employee details xml.

CREATE TABLE HR.EMP_XML_DATA 
   (	EMPLOYEE_ID NUMBER, 
    	EMP_DETAILS_XML XMLTYPE, 
	    INSERT_DATE DATE, 
	    INSERT_USER_ID NUMBER
   )
TABLESPACE USERS 
   XMLTYPE COLUMN EMP_DETAILS_XML STORE AS BASICFILE CLOB (
  TABLESPACE USERS ) ;

INSERT INTO EMP_XML_DATA (EMPLOYEE_ID, EMP_DETAILS_XML,INSERT_DATE,INSERT_USER_ID)
  VALUES (208,XMLType(bfilename('XMLDIR', 'EmployeeDetails.xml'),nls_charset_id('AL32UTF8')),SYSDATE,10);

Alternatively, the xml can be directly inserted into the table also using createXML as follows

INSERT INTO HR.EMP_XML_DATA VALUES
(
   209,
   SYS.XMLTYPE.CREATEXML(
      '<employeeinformation xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nonamespaceschemalocation="http://www.oracle.com/xdb/po.xsd">
  <employee>209</employee>
  <actions>
    <action>
      <name>Stephen</name>
	  <id>209</id>
      <date xsi:nil="true">
    </date></action>
  </actions>
  <reject>
  <country>USA</country>
  <company>Oracle Corporation Pvt Ltd</company>
  <employeedetails>
    <name>Stephen King</name>
    <email>sking@oramail.com</email>
    <telephone>515.123.8271</telephone>
	<job>Accountant</job>
	<salary>4500</salary>
	<hire_date>4-AUG-1995</hire_date>
  </employeedetails>
  <empjobhistorydetails>
  </empjobhistorydetails>
</reject></employeeinformation>'),SYSDATE, 10);

--select example using existsNode

SELECT x.EMP_DETAILS_XML
  FROM emp_xml_data x
  WHERE existsNode(EMP_DETAILS_XML, '/EmployeeInformation[Employee="208"]') = 1
  AND EMPLOYEE_ID =  208;

--select example to extract only EmployeeDetails for an employee having phone number "515.123.8181"

SELECT extract(EMP_DETAILS_XML, '/EmployeeInformation/EmployeeDetails') "EmpDetails"
  FROM EMP_XML_DATA
  WHERE existsNode(EMP_DETAILS_XML, '/EmployeeInformation/EmployeeDetails[telephone="515.123.8181"]') = 1
AND EMPLOYEE_ID =  208;  

--In this example, you can also simply give a node and check for existence without checking the value of the node.

SELECT extract(EMP_DETAILS_XML, '/EmployeeInformation/EmployeeDetails') "EmpDetails"
  FROM EMP_XML_DATA
  WHERE existsNode(EMP_DETAILS_XML, '/EmployeeInformation/EmployeeDetails') = 1
  AND EMPLOYEE_ID =  208;

Instead of existsNode you can also use XMLExists as it is almost similar to existsNode but for some minor differences. 

Some of the differences are XMLExists accepts an arbitrary XQuery expression. 

existsNode accepts only an XPath expression (XPath is a proper subset of XQuery). 

XMLExists returns a Boolean value, TRUE or FALSE; 

existsNode returns 1 or 0.

SELECT EMP_DETAILS_XML
  FROM EMP_XML_DATA
  WHERE XMLExists('/EmployeeInformation[Employee="208"]'
                  PASSING EMP_DETAILS_XML);
		  
SELECT EMP_DETAILS_XML
  FROM EMP_XML_DATA
  WHERE XMLExists('/EmployeeInformation/EmployeeDetails'
                  PASSING EMP_DETAILS_XML);  

--below is an example to use EXTRACT and EXTRACTVALUE

SELECT EXTRACT(EMP_DETAILS_XML, '/EmployeeInformation/EmployeeDetails') 
FROM EMP_XML_DATA
WHERE EXTRACTVALUE(EMP_DETAILS_XML, '/EmployeeInformation/Employee') = '208';

The difference between EXTRACT and EXTRACTVALUE is With EXTRACT you will get the output with the tags. With EXTRACTVALUE you will get the exact value between the tags. You can use the below query to see the difference in both the outputs

SELECT EXTRACT(EMP_DETAILS_XML, '/EmployeeInformation/Employee'), EXTRACTVALUE(EMP_DETAILS_XML, '/EmployeeInformation/Employee') 
FROM EMP_XML_DATA
WHERE EXTRACTVALUE(EMP_DETAILS_XML, '/EmployeeInformation/Employee') = '208';

extract                     extractvalue
------------------------    ------------
<Employee>208</Employee>    208

You can create function-based indexes using SQL function existsNode to speed up the execution.

example

CREATE INDEX idx_emp_xml_index ON EMP_XML_DATA(EMP_DETAILS_XML.existsNode('/EmployeeInformation/EmployeeDetails')); 

You can also create an XMLIndex index to help speed up arbitrary XPath searching. 

 SQL functions such as XMLQuery, XMLTable, XMLExists, XMLCast, extract, extractValue, and existsNode can take advantage of XMLIndex to improve the performance of XML retrieval.

Example of function based indexes

CREATE UNIQUE INDEX i_purchase_order_reference
ON env_analysis ea ( 
   SUBSTR(
      SYS.XMLTYPE.GETSTRINGVAL (         
         SYS.XMLTYPE.EXTRACT(   
            ea.report, '/Report/Substance/text()')),1,30));

Generic xml index

CREATE INDEX idx_emp_xml ON EMP_XML_DATA(EMP_DETAILS_XML) INDEXTYPE IS XDB.XMLIndex;

CREATE INDEX t_xml_f ON t_xml(lower(extractValue(xml, '/name')));

create index test_xmlindex on xmltest (a)
indextype is XDB.XMLIndex parameters ('paths (include (/root/a/@test))');