XMLDB : XML Functions continued ...

 Below post gives a brief idea of some of the important functions used in XML queries such as

XMLElement creates a element 

XMLAttributes adds attributes to an element 

XMLForest creates forest of elements 

XMLAgg creates a single element from a collection of elements 

XMLQuery 

XMLCast 

XMLCOMMENT 

XMLExists 

XMLParse

XMLElement & XMLAttribute  

Takes a name as an identifier and an otpional list of attributes and is a key xml function to create nested xml documents.

Attribute is what further describes an XMLELEMENT.

SELECT XMLELEMENT("Employee Details", XMLATTRIBUTES(e.employee_id as "EmployeeId", e.last_name as "LastName"),
 XMLELEMENT("Dept:", XMLATTRIBUTES(e.department_id as "DeptId",
 (SELECT d.department_name FROM departments d
 WHERE d.department_id = e.department_id) as "DeptName")),
 XMLELEMENT("Salary", e.salary),
 XMLELEMENT("Hiredate", TO_CHAR(e.hire_date,'dd-MON-rrrr')))
 FROM employees e
 WHERE employee_id = 201;

Output

<Employee Details EmployeeId="201" LastName="Hartstein">
	<Dept DeptId="20" DeptName="Marketing"></Dept>
	<Salary>13000</Salary>
	<Hiredate>17-FEB-2004</Hiredate>
</Employee Details>

XMLForest 

XMLForest converts each of its argument parameters to XML and returns an XML fragment which is a concatenation of these individual converted parameters.

SELECT XMLELEMENT("Employee Contact Details", 
                  XMLFOREST(e.EMPLOYEE_ID, e.FIRST_NAME || ' ' || e.LAST_NAME  "EmpName", e.EMAIL, e.PHONE_NUMBER)) as  "Emp Contact Details"
FROM employees e WHERE employee_id = 208; 

--output

<Employee Contact Details><EMPLOYEE_ID>208</EMPLOYEE_ID><EmpName>William Gietz</EmpName><EMAIL>WGIETZ2</EMAIL><PHONE_NUMBER>515.123.8181</PHONE_NUMBER></Employee Contact Details>

XMLAgg 

XMLAgg aggregates rows. 

You can use this function in a group by clause to aggregate elements based on input condition. 

Example - Below example uses XMLAgg to give a comma separate aggregate list of employees department wise.

select department_id, rtrim(
                   xmlagg(
                     xmlelement("x",first_name||':'||last_name ||',')
                          ).extract('//text()'),',') enames
    from employees
  group by department_id;

Output of the query comes as follows

department_id    enames
10	            Jennifer:Whalen
20	            Michael:Hartstein,Pat:Fay
30	            Den:Raphaely,Karen:Colmenares,Guy:Himuro,Sigal:Tobias,Shelli:Baida,Alexander:Khoo
40	            Susan:Mavris  
......

XMLQuery 

It is used to construct or query XML data. It takes an XQuery string as input to evaluate and extract data from the XMLType column passed in the PASSING clause to return the CONTENT which can either be an XML document conforming with XML 1.0 document or a document fragment conforming to XML 1.0 semantics.

SELECT 
XMLQuery(
          '/EmployeeInformation/EmployeeDetails/name/text()'
          PASSING EMP_DETAILS_XML RETURNING CONTENT)
FROM emp_xml_data;

EmpName
-------
Stephen King
William Gietz

Below example uses XMLQuery to return telephone details for all records for employees where name matches 'Stephanie Graph'. For other records this value will be null.

SELECT EMPLOYEE_ID, XMLQuery (
'for $i in /EmployeeInformation/EmployeeDetails
 where $i /name = "Stephanie Graph"
 order by $i/name
 return $i/name'
passing by value EMP_DETAILS_XML
RETURNING CONTENT) PhoneNo
FROM emp_xml_data;

employee_id      PhoneNo
-----------      -------------
210	             515.123.8255
208	             null
209	             null

XMLCast 

For the data inserted in EMP_DETAILS_XML table created in p2, below is the output of XMLCast SQL/XML standard function XMLCast casts its first argument to the scalar SQL data type specified by its second argument. The first element is the XML output OF XMLQuery.

SELECT XMLCast(XMLQuery('/EmployeeInformation/EmployeeDetails/name'
                        PASSING EMP_DETAILS_XML RETURNING CONTENT)
               AS VARCHAR2(4000)) as "Emp Name"
FROM emp_xml_data;
  
Emp Name
--------
William Gietz
Stephen King  

SELECT XMLCast(
         XMLQuery('$p/EmployeeInformation/EmployeeDetails[addr/@addr_id="10001"]/job/text()'
                  PASSING EMP_DETAILS_XML AS "p" RETURNING CONTENT)
               AS VARCHAR2(30)) as "Job"
  FROM emp_xml_data
  WHERE EMPLOYEE_ID = 210;  

Job
----------
Accountant

XMLComment 

XMLComment allows you to embed comments in your XML document. It uses the normal syntax of embedding comments in between comment tags ""

SELECT XMLCOMMENT('A simple XMLCOMMENT') 
   AS "XMLCOMMENT" FROM DUAL;

XMLCOMMENT
----------
<!--A simple XMLCOMMENT-->

Similary you can embed comments in you XML as follows

SELECT XMLELEMENT("EMP_INFO",
           XMLELEMENT("Emp Name",'Scott'),
           XMLCOMMENT('Test Comment'),
           XMLELEMENT("Emp Last Name", 'Tiger')
         )
FROM dual;
   
<EMP_INFO>
	<Emp Name>Scott</Emp Name>
	<!--Test Comment-->
	<Emp Last Name>Tiger</Emp Last Name>
</EMP_INFO>
</code></pre>

XMLConcat 

XMLConcat can be used to concatenate multiple XMLElement types. In the below example Employee Name and Salary are concatenated under a common head Employee Details.

SELECT XMLAgg(XMLConcat(xmlelement("Employee Details",XMLElement("Employee Name", e.first_name || e.last_name),
XMLElement("Salary", e.salary)))) AS "XML Struct"
FROM employees e
WHERE EMPLOYEE_ID = 208;

<Employee Details>
	<Employee Name>WilliamGietz</Employee Name>
	<Salary>8390</Salary>
</Employee Details>

Examples of XMLExists 

--To check the count of records conforming to a particular XML structure using XMLExists.

SELECT count(*) FROM EMP_XML_DATA
  WHERE XMLExists('$p/EmployeeInformation/Employee' PASSING EMP_DETAILS_XML AS "p");

--To check the count of employees having Job="Accountant" using XMLExists

SELECT count(*) FROM EMP_XML_DATA
WHERE XMLExists('$p/EmployeeInformation/EmployeeDetails[job="Accountant"]'
                  PASSING EMP_DETAILS_XML AS "p");

--to select count of employees having addr_id attribute.

SELECT count(*) FROM EMP_XML_DATA
WHERE XMLExists('$p/EmployeeInformation/EmployeeDetails/addr/@addr_id'
                  PASSING EMP_DETAILS_XML AS "p");

--to select address details of an employee having addrss Id attribute = "10002" using XMLExists and extract

SELECT extract(EMP_DETAILS_XML, '/EmployeeInformation/EmployeeDetails/addr')  
FROM EMP_XML_DATA
WHERE XMLExists('$p/EmployeeInformation/EmployeeDetails/addr[@addr_id="10002"]'
                  PASSING EMP_DETAILS_XML AS "p");

--output

<addr xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        addr_id="10002" addr_text1="greenwood apts" addr_text2="new jersey county" addr_text3="new york"/>				  
</code></pre>  

XMLParse 

XMLParse parses and generates an XML instance from an input string. If the input string does not conform to a valid xmltype, then an appropriate error is displayed. Depending on argument DOCUMENT or CONTENT the input can be a singly rooted XML document or a valid XML.

SELECT XMLPARSE(CONTENT '100 <purchaseOrder poNo="10001"> 
   <customerName> Scott Tiger</customerName>
   <itemNo>100050</itemNo>
   </purchaseOrder>' 
) AS PO FROM DUAL;

100 <purchaseOrder poNo="10001"> 
   <customerName> Scott Tiger</customerName>
   <itemNo>100050</itemNo>
   </purchaseOrder> 

If the xml is malformed such as if a closing tag of any element does not match you can get the error as follows  

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00225: end-element tag "purchaseOrder" does not match start-element tag "itemNo"
Error at line 4
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00225: end-element tag "itemNos" does not match start-element tag "itemNo"
Error at line 3
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.

When you specify WELLFORMED in the argument, you are guareenting that the input is a well formed 

XML document and the database does not perfrom validity checks to ensure the input is well formed.

XMLTABLE 

XMLTABLE is an XML function that evaluates an XQuery expression and returns the result as a relational table. The XMLTABLE function is implicitly joined with the table and applied to each of the rows in the table. Here the 'COLUMNS' clause identifies each xpath expression in terms of the name and datatype. The PATH is an XPATH expression that identifes each column in the XML. To select an attribute under any xpath, you will give is with '@' as shown in the example below. Below is an example of using XMLTABLE 

For sample data refer to blog post 

To store and Select XML Data

SELECT X.* 
FROM EMP_XML_DATA, 
XMLTABLE ('$d/EmployeeInformation/EmployeeDetails' passing EMP_DETAILS_XML as "d" 
   COLUMNS 
   name    VARCHAR(100)     PATH 'name',
   email    VARCHAR(20)     PATH 'email',
   job     VARCHAR(25)     PATH 'job',
   address varchar2(100) PATH 'addr/@addr_text1') AS X;

Output

name            email               job                  address   
Stephanie Graph	sgraph@oramail.com	Accountant	         greenwood apts
William Gietz	wgietz2@oramail.com	Public Accountant	
Stephen King	sking@oramail.com	Accountant	   

XMLPl 

XMLPl(identifier, value_expr); generates an XML processing instruction using the identifier and the evaluated result of value_expr to provide an application with information that is associated with all or part of an XML document. The application uses this processing instruction to determine how best to process the XML document.

SELECT XMLPI(NAME "Order analysisComp", 'imported, reconfigured, disassembled')
   AS "XMLPI" FROM DUAL;

Output

<?Order analysisComp imported, reconfigured, disassembled?>   

XMLSerialize 

Creates a string or LOB from a valid input XML document or XML type. 

Basic usage XMLSerialize (CONTENT|DOCUMENT input); 

If you specify DOCUMENT, the input must be a valid XML document 

If you specify CONTENT, the input must be a valid XML type. Example

SELECT XMLSERIALIZE(CONTENT XMLTYPE('Scott'))
   FROM DUAL;

--output

<EmpName>Scott</EmpName>   

XMLROOT 

Lets you create an XML be providing the version and standalone properties in the XML root information. If the version input argument is specified as NO VALUE, the version defaults to 1.0. If the STANDALONE clause is skipped out, this is blank in the output returned. Example

SELECT XMLROOT ( XMLType('100001'), VERSION '1.0', STANDALONE YES)
   AS "XMLROOT" FROM DUAL; 

--output

<?xml version="1.0" standalone="yes"?>
<empid>100001</empid>

XMLSequence

XMLSEQUENCE(XMLType) This takes an XMLType instance and returns a varry of the top level nodes in the XMLType. This has been superseded by the more readable XMLTable as show above 

Example

SELECT VALUE(p)
   FROM EMP_XML_DATA e, 
   TABLE(XMLSEQUENCE(EXTRACT(EMP_DETAILS_XML, '/EmployeeInformation/EmployeeDetails/*'))) p
   WHERE e.employee_id = 208;

--output

<email>wgietz2@oramail.com</email>
<hire_date>24-AUG-2001</hire_date>
<name>William Gietz</name>
<telephone>515.123.8181</telephone>
<salary>8390</salary>
<job>Public Accountant</job>

There is one more form of this function where you pass a refcursor input and it returns an XMLSequence type XML for each row of the cursor.

XMLPatch 

XMLPatch patches an XML document with the changes specified and returns a patched XML document. The first argument specifies the name of the input XMLType document to be patched

create table emp_xml of xmltype;
 
insert into emp_xml values (
    xmltype('<emp>
   <emp_details empName="scott" empId="1"/>
   <emp_details empName="jim" empId="2"/>
   <emp_details empName="jack" empId="3"/>
  </emp>')
); 
 
 
select xmlserialize(document
            xmlpatch(
              object_value
            , xmlquery(
              'declare namespace xd = "http://xmlns.oracle.com/xdb/xdiff.xsd"; (::)
               element xd:xdiff
               {
                 attribute              xsi:schemaLocation { "http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" }
               , processing-instruction oracle-xmldiff     { ''operations-in-docorder="true" output-model="current"'' }
               , for $i at $p in /emp/emp_details
                 return element xd:update-node
                 {
                   attribute xd:node-type  { "attribute" }
                 , attribute xd:xpath      { concat("/emp/emp_details[", $p, "]") }
                 , attribute xd:attr-local { "empName" }
                 , element   xd:content    { concat($i/@empName, $i/@empId) }
                 }
               }'
              passing object_value
              returning content
              )
            )
            as clob indent
          )
   from emp_xml
   ;

--Output

XMLSERIALIZE(DOCUMENTXMLPATCH(
--------------------------------------------------------------------------------
<emp>
  <emp_details empName="scott1" empId="1"/>
  <emp_details empName="jim2" empId="2"/>
  <emp_details empName="jack3" empId="3"/>
</emp>

References 

http://www.asktheway.org/official-documents/oracle/E50529_01/ADXDB/xdb_xquery.htm#ADXDB5088

http://devel.hotpilot.cz/ora-12cR1-lin-64-inst/E50529_01/ADXDB/xdb03usg.htm#ADXDB5803 https://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb_xquery.htm#CBAIIEFG https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions237.htm#SQLRF06230 https://odieweblog.wordpress.com/2012/06/19/how-to-update-xml-nodes-with-values-from-same-doc/ https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions234.htm#SQLRF20026

Thought for the day

It is very easy to defeat someone, but it is very hard to win someone

--Anonymous