XML Functions [ SQL ]

1. Extract

      Syntax : extract (xmltype, xpath)

Example:


select empxml,extract(empxml,'/ROWSET/ROW[empNo="2" and dept="10"]')
from
(select xmltype('<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <empNo>1</empNo>
  <ename>Kiran</ename>
  <dept>10</dept>
  <salary> 1000 </salary>
 </ROW>
 <ROW>
  <empNo>2</empNo>
  <ename>Vishali</ename>
  <dept>10</dept>
  <salary> 1500 </salary>
 </ROW>
</ROWSET>
') empxml from dual);



select empxml,extract(empxml,'/ROWSET/ROW/empNo')
from
(select xmltype('<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <empNo>1</empNo>
  <ename>Kiran</ename>
  <dept>10</dept>
  <salary> 1000 </salary>
 </ROW>
 <ROW>
  <empNo>2</empNo>
  <ename>Vishali</ename>
  <dept>10</dept>
  <salary> 1500 </salary>
 </ROW>
</ROWSET>
') empxml from dual);

2. EXISTSNODE

EXISTSNODE determines whether traversal of an XML document using a specified path results in any nodes

The return value is NUMBER:

Syntax : extractsNode (xmltype, xpath)

• 0 if no nodes remain after applying the XPath traversal on the document
• 1 if any nodes remain

Example:
select empxml,
          existsnode(empxml,'/ROWSET/ROW/ssn') ssnTag,
          existsnode(empxml,'/ROWSET/ROW/dept') deptTag
from 
(select xmltype('<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <empNo>1</empNo>
  <ename>Kiran</ename>
  <dept>10</dept>
  <salary> 1000 </salary>
 </ROW>
 <ROW>
  <empNo>2</empNo>
  <ename>Vishali</ename>
  <dept>10</dept>
  <salary> 1500 </salary>
 </ROW>
</ROWSET>
') empxml from dual);

3. DELETEXML

DELETEXML deletes the node or nodes matched by the XPath expression in the target XML.

Syntax : DELETEXML(xmltype ,xpath)

select empxml,DELETEXML(empxml,'/ROWSET/ROW/dept')

from 

(select xmltype('<?xml version="1.0"?>

<ROWSET>

 <ROW>

  <empNo>1</empNo>

  <ename>Kiran</ename>

  <dept>10</dept>

  <salary> 1000 </salary>

 </ROW>

 <ROW>

  <empNo>2</empNo>

  <ename>Vishali</ename>

  <dept>10</dept>

  <salary> 1500 </salary>

 </ROW>

</ROWSET>

') empxml from dual);

4. InsertChildXML

INSERTCHILDXML inserts a user-supplied value into the target XML at the node indicated by the XPath expression

Example:


select empxml,insertchildxml(empxml,'/ROWSET/ROW','ssn',xmltype('<ssn></ssn>'))
from 
(select xmltype('<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <empNo>1</empNo>
  <ename>Kiran</ename>
  <dept>10</dept>
  <salary> 1000 </salary>
 </ROW>
 <ROW>
  <empNo>2</empNo>
  <ename>Vishali</ename>
  <dept>10</dept>
  <salary> 1500 </salary>
 </ROW>
</ROWSET>
') empxml from dual);

5. InsertXMLBefore

INSERTXMLBEFORE inserts a user-supplied value into the target XML before the node indicated by the XPath expression.

Example:

select empxml,INSERTXMLBEFORE(empxml,'/ROWSET/ROW/dept',xmltype('<ssn></ssn>'))

from 

(select xmltype('<?xml version="1.0"?>

<ROWSET>

 <ROW>

  <empNo>1</empNo>

  <ename>Kiran</ename>

  <dept>10</dept>

  <salary> 1000 </salary>

 </ROW>

 <ROW>

  <empNo>2</empNo>

  <ename>Vishali</ename>

  <dept>10</dept>

  <salary> 1500 </salary>

 </ROW>

</ROWSET>

') empxml from dual);

6. XMLROOT

XMLROOT lets you create a new XML value by providing version and standalone properties in the XML root information

Syntax: XMLROOT(xmltype, Version '1.0',StandAlone [Yes/No]) 

Example

select 

xmlroot (

 xmltype('<ROWSET>

                       <Row>

                             <SampleTag>143598</SampleTag>

                             <SampleTag1> 13433 </SampleTag1>

                      </Row>

                    </ROWSET>'), 

                    version '1.0', 

                    standalone yes) 

as "XMLROOT" from dual;

UpdateXML ( Set value to a tag )

UPDATEXML takes as arguments an XMLType instance and an XPath-value pair and returns an XMLType instance with the updated value.

Syntax:
updatexml(Myxml, SearchString,NewValue)

SearchString => XMLPath

Example:

select empxml from
(select xmltype('
<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <empNo>1</empNo>
    <ename>Kiran</ename>
    <dept>10</dept>
    <salary> 1000 </salary>
 </ROW>
  <ROW>
    <empNo>2</empNo>
    <ename>Vishali</ename>
    <dept>10</dept>
    <salary> 1500 </salary>
  </ROW>
</ROWSET>') empxml from dual);


-- Update Salary on empNo=2 and dept =10 to 2000

select updatexml(empxml,'/ROWSET/ROW[empNo="2" and dept="10"]/salary/text()', '2000')
from
(select xmltype('<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <empNo>1</empNo>
  <ename>Kiran</ename>
  <dept>10</dept>
  <salary> 1000 </salary>
 </ROW>
 <ROW>
  <empNo>2</empNo>
  <ename>Vishali</ename>
  <dept>10</dept>
  <salary> 1500 </salary>
 </ROW>
</ROWSET>
') empxml from dual);

-- Update XML on Null Tag (empty tag : <tag/>)

Tag need to be replaced along with the value : '<tag> NewValue </tag>'

On non-empty tags we just replace text() with the NewValue.

select updatexml(empxml,'/ROWSET/ROW[empNo="3" and dept="20"]/salary', '<salary> 3000 </salary>')
from
(select xmltype('<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <empNo>1</empNo>
  <ename>Kiran</ename>
  <dept>10</dept>
  <salary> 1000 </salary>
 </ROW>
 <ROW>
  <empNo>2</empNo>
  <ename>Vishali</ename>
  <dept>10</dept>
  <salary> 1500 </salary>
 </ROW>
 <ROW>
  <empNo>3</empNo>
  <ename> KiVi </ename>
  <dept>20</dept>
  <salary/>
 </ROW>
</ROWSET>
') empxml from dual);

XQuery

XQuery was designed to query XML data.

XQuery is a language for finding and extracting elements and attributes from XML documents.

XQuery uses path expressions to navigate through elements in an XML document.

example: Xpath 
/bookstore/book/title

XQuery uses predicates to limit the extracted data from XML documents.

example:
/bookstore/book[price<30]

/bookstore/book[price>30]/title

The expression above will select all the title elements under the book elements that are under the bookstore element that have a price element with a value that is higher than 30.

FLWOR expression 

The following FLWOR expression will select exactly the same as the path expression above:

for $x in doc("books.xml")/bookstore/book
where $x/price>30
return $x/title

With FLWOR you can sort the result:

for $x in doc("books.xml")/bookstore/book
where $x/price>30
order by $x/title
return $x/title

The for clause selects all book elements under the bookstore element  into a variable called $x.
The where clause selects only book elements with a price element with a value greater than 30.
The order by clause defines the sort-order. Will be sort by the title element.
The return clause specifies what should be returned. Here it returns the title elements.


XQuery is case-sensitive and XQuery elements, attributes, and variables must be valid XML names.

An XQuery string value can be in single or double quotes

XQuery Conditional Expressions

If-Then-Else

for $x in doc("books.xml")/bookstore/book
return if ($x/@category="CHILDREN")
then <child>{data($x/title)}</child>
else <adult>{data($x/title)}</adult>


Comparisons

1. General comparisons: =, !=, <, <=, >, >=

2. Value comparisons: eq, ne, lt, le, gt, ge


The for Clause

The for clause binds a variable to each item returned by the in expression.
The for clause results in iteration. There can be multiple for clauses in the same FLWOR expression.

Example:

for $x in (1 to 5)
return <test>{$x}</test>

The at keyword can be used to count the iteration:

Example::

for $x at $i in doc("books.xml")/bookstore/book/title
return <book>{$i}. {data($x)}</book>

It is also allowed with more than one in expression in the for clause. Use comma to separate each in expression:

Example:

for $x in (10,20), $y in (100,200)
return <test>x={$x} and y={$y}</test>


The let Clause

The let clause allows variable assignments and it avoids repeating the same expression many times. The let clause does not result in iteration.

let $x := (1 to 5)
return <test>{$x}</test>

The where clause is used to specify one or more criteria for the result:

where $x/price>30 and $x/price<100

The order by clause is used to specify the sort order of the result.

for $x in doc("books.xml")/bookstore/book
order by $x/@category, $x/title
return $x/title

The return clause specifies what is to be returned.

for $x in doc("books.xml")/bookstore/book
return $x/title

DBMS_XMLGEN

The DBMS_XMLGEN package converts the results of a SQL query to a canonical XML format.

The package takes an arbitrary SQL query as input, converts it to XML format, and returns the result as a CLOB.

DBMS_XMLGEN.CTXHANDLE 

DBMS_XMLGEN.NEWCONTEXT
  Generates a new context handle from a query

DBMS_XMLGEN.CLOSECONTEXT
   This procedure closes a given context and releases all resources associated with it,

DBMS_XMLGEN.GETXML
  This function gets the XML document.
  Gets the XML document by fetching the maximum number of rows specified.
  It appends the XML document to the CLOB passed in

DBMS_XMLGEN.GETXMLTYPE

  Generates the XML document and returns it as a sys.XMLType

DBMS_XMLGEN.GETNUMROWSPROCESSED

   This function retrieves the number of SQL rows processed when generating the XML using the GETXML

DBMS_XMLGEN.RESTARTQUERY
  This procedure restarts the query and generates the XML from the first row.
  It can be used to start executing the query again, without having to create a new context.

DBMS_XMLGEN.SETCONVERTSPECIALCHARS
  This procedure sets whether or not special characters in the XML data must be converted into their escaped XML equivalent.
For example, the < sign is converted to &lt;.
The default is to perform conversions.
This function improves performance of XML processing when the input data cannot contain any special characters such as <, >, ",', which must be escaped.
It is expensive to scan the character data to replace the special characters,particularly if it involves a lot of data.

DBMS_XMLGEN.SETMAXROWS
  This procedure sets the maximum number of rows to fetch from the SQL query result for every invokation of the GETXML.It is used when generating paginated results.

DBMS_XMLGEN.SETNULLHANDLING
  This procedure sets NULL handling options, handled through the flag parameter setting.

  DROP_NULLS CONSTANT NUMBER:= 0; (Default) Leaves out the tag for NULL elements.
  NULL_ATTR CONSTANT NUMBER:= 1; Sets xsi:nil="true".
  EMPTY_TAG CONSTANT NUMBER:= 2; Sets, for example, <foo/>.

DBMS_XMLGEN.SETROWSETTAG
  This procedure sets the name of the root element of the document. The default name is ROWSET.

DBMS_XMLGEN.SETROWTAG
  This procedure sets the name of the element separating all the rows. The default name is ROW.

DBMS_XMLGEN.SETSKIPROWS
This procedure skips a given number of rows before generating the XML output for every call to the GETXML


Example:




declare
 sqlquery varchar2(200):= 'select ''kiran'' fname, ''kodavati'' lname from dual
                                       union all
                                       select ''vishali'' fname,''senivarapu'' lname from dual';

  xmlcontextHandle  dbms_xmlgen.CTXHANDLE;
  xmlclob_1 clob;
  numberofrowsfetched number;
  xmlclob_2 clob;
begin
  dbms_output.put_line(sqlquery);
  xmlcontextHandle:=DBMS_XMLGEN.NEWCONTEXT(sqlquery);
  xmlclob_1:=dbms_xmlgen.getXML(xmlcontextHandle);
  numberofrowsfetched:=dbms_xmlgen.getnumrowsprocessed(xmlcontextHandle);
  dbms_output.put_line(xmlclob_1);
  dbms_output.put_line('numberofrowsfetched:'||numberofrowsfetched);

  dbms_xmlgen.restartQuery(xmlcontextHandle);

  dbms_xmlgen.SETMAXROWS(xmlcontextHandle,1);
  xmlclob_2:=dbms_xmlgen.getXML(xmlcontextHandle);
  dbms_output.put_line(xmlclob_2);
  numberofrowsfetched:=dbms_xmlgen.getnumrowsprocessed(xmlcontextHandle);
  dbms_output.put_line('numberofrowsfetched:'||numberofrowsfetched);


  xmlclob_2:=dbms_xmlgen.getXML(xmlcontextHandle);
  dbms_output.put_line(xmlclob_2);

  numberofrowsfetched:=dbms_xmlgen.getnumrowsprocessed(xmlcontextHandle);
  dbms_output.put_line('numberofrowsfetched:'||numberofrowsfetched);

  dbms_xmlgen.restartQuery(xmlcontextHandle);
  dbms_xmlgen.SETMAXROWS(xmlcontextHandle,2);
  dbms_xmlgen.setRowSetTag(xmlcontextHandle,'DataSet');
  dbms_xmlgen.setRowTag(xmlcontextHandle,'DataRow');
  xmlclob_2:=dbms_xmlgen.getXML(xmlcontextHandle);
  dbms_output.put_line(xmlclob_2);

  dbms_xmlgen.closeContext(xmlcontextHandle);
end;

XMLType

XMLType is a system-defined type for handling XML data.

You can create columns of XMLType and insert XML documents into it.


Functions and Procedures of XMLType

XMLType(xmlData) -- Constructor

   The actual data in the form of a CLOB, REF cursor, VARCHAR2 or object type.


declare
xmlclob clob:=
'<?xml version="1.0"?>
  <Family>
    <name> kiran </name>
    <name> vishali </name>
  </Family>';
testxmltype xmltype:=xmltype(xmlclob);
begin
  dbms_output.put_line(testxmltype.getclobval());
end;




createXML()
   for creating and returning an XMLType instance.
   The string and clob parameters used to pass in the date must contain well-formed and valid XML documents



existsNode()
   Checks if the node exists.
   If the XPath string is NULL or the document is empty, then a value of 0 is returned, otherwise returns 1.

   Given an XPath expression, checks if the XPath applied over the document can return any valid nodes.

declare
xmlclob clob:=
'<?xml version="1.0"?>
  <Family>
    <name> kiran </name>
    <name> vishali </name>
  </Family>';
testxmltype xmltype:=xmltype(xmlclob);
begin
  dbms_output.put_line(testxmltype.getclobval());
 
  if testxmltype.existsNode('/Family/name')=1 then
    dbms_output.put_line('node exists');
  else
    dbms_output.put_line('node does not exists');
  end if;
 
  if testxmltype.existsNode('//name')=1 then
    dbms_output.put_line('node exists');
  else
    dbms_output.put_line('node does not exists');
  end if;

end;


extract()

   Extracts an XMLType fragment and returns an XMLType instance containing the result nodes.
   If the XPath does not result in any nodes, then returns NULL.


declare
xmlclob clob:=
'<?xml version="1.0"?>
  <Family>
    <name> kiran </name>
    <name> vishali </name>
  </Family>';
testxmltype xmltype:=xmltype(xmlclob);
newxmltype xmltype;
begin
  dbms_output.put_line(testxmltype.getclobval());
 
  newxmltype:=testxmltype.extract('//name');
 
  dbms_output.put_line(newxmltype.getclobval());
 
  newxmltype:=testxmltype.extract('//name/text()');
 
  dbms_output.put_line(newxmltype.getclobval());
 
end;


isFragment()
   Determines if the XMLType instance corresponds to a well-formed document, or a fragment.
   Returns 1 or 0 indicating if the XMLType instance contains a fragment or a well-formed document.
   A fragment is a XML instance, which has more than one root element.


getClobVal()
   Returns a CLOB containing the seralized XML representation

getNumVal()
   Returns a numeric value, formatted from the text value pointed to by the XMLType instance

getStringVal()
   Returns the document as a string.
   Returns s string containing the seralized XML representation, or in case of text nodes, the text itself.

getRootElement()
   Gets the root element of the XMLType instance. Returns NULL if the instance is a fragment.

XML Basics

XML

XML stands for eXtensible Markup Language.

XML is designed to transport and store data.

XML does not DO anything. XML was created to structure, store, and transport information.

XML Documents Form a Tree Structure

<root>
  <child>
    <subchild>.....</subchild>
  </child>
</root>

XML Tags are Case Sensitive

A "Well Formed" XML document has correct XML syntax.

• XML documents must have a root element
• XML elements must have a closing tag
• XML tags are case sensitive
• XML elements must be properly nested
• XML attribute values must be quoted

Sample xml:

<?xml version="1.0" encoding="UTF-16"?>

<note>
<to>kiran</to>
<from>vishali</from>
<heading>Reminder</heading>
<body>Don't forget breakfast this weekend!</body>
</note>

XML DOM

The XML DOM defines a standard way for accessing and manipulating XML documents.

The DOM presents an XML document as a tree-structure.

The XML DOM is a standard for how to get, change, add, or delete XML elements.


According to the DOM, everything in an XML document is a node.

The DOM says:

The entire document is a document node
Every XML element is an element node
The text in the XML elements are text nodes
Every attribute is an attribute node
Comments are comment nodes


A common error in DOM processing is to expect an element node to contain text.

However, the text of an element node is stored in a text node.

The XML DOM views an XML document as a tree-structure. The tree structure is called a node-tree.

All nodes can be accessed through the tree. Their contents can be modified or deleted, and new elements can be created.

The tree starts at the root node and branches out to the text nodes at the lowest level of the tree

The nodes in the node tree have a hierarchical relationship to each other.

The terms parent, child, and sibling are used to describe the relationships.

Parent nodes have children. Children on the same level are called siblings.

In a node tree, the top node is called the root
Every node, except the root, has exactly one parent node
A node can have any number of children
A leaf is a node with no children
Siblings are nodes with the same parent

The XML DOM contains methods (functions) to traverse XML trees, access, insert, and delete nodes.

However, before an XML document can be accessed and manipulated, it must be loaded into an XML DOM object.

The programming interface to the DOM is defined by a set standard properties and methods.

XML DOM Properties

These are some typical DOM properties:
x is a node object.
x.nodeName - the name of x
x.nodeValue - the value of x
x.parentNode - the parent node of x
x.childNodes - the child nodes of x
x.attributes - the attributes nodes of x


XML DOM Methods

x is a node object.
x.getElementsByTagName(name) - get all elements with a specified tag name
x.appendChild(node) - insert a child node to x
x.removeChild(node) - remove a child node from x


getElementsByTagName() returns all elements with a specified tag name.

The getElementsByTagName() method returns a node list. A node list is an array of nodes.

XPath 

XPath is a language for finding information in an XML document.

XPath uses path expressions to select nodes or node-sets in an XML document. These path expressions look very much like the expressions you see when you work with a traditional computer file system.

Selecting Nodes

XPath uses path expressions to select nodes in an XML document. The node is selected by following a path or steps. The most useful path expressions are listed below:

Expression	Description
nodename	Selects all nodes with the name "nodename"
/	Selects from the root node
//	Selects nodes in the document from the current node that match the selection no matter where they are
.	Selects the current node
..	Selects the parent of the current node
@	Selects attributes

In the table below we have listed some path expressions and the result of the expressions:

Path Expression	Result
bookstore	Selects all nodes with the name "bookstore"
/bookstore	Selects the root element bookstoreNote: If the path starts with a slash ( / ) it always represents an absolute path to an element!
bookstore/book	Selects all book elements that are children of bookstore
//book	Selects all book elements no matter where they are in the document
bookstore//book	Selects all book elements that are descendant of the bookstore element, no matter where they are under the bookstore element
//@lang	Selects all attributes that are named lang

---

Predicates

Predicates are used to find a specific node or a node that contains a specific value.

Predicates are always embedded in square brackets.

In the table below we have listed some path expressions with predicates and the result of the expressions:

Path Expression	Result
/bookstore/book[1]	Selects the first book element that is the child of the bookstore element.Note: IE5 and later has implemented that [0] should be the first node, but according to the W3C standard it should have been [1]!!
/bookstore/book[last()]	Selects the last book element that is the child of the bookstore element
/bookstore/book[last()-1]	Selects the last but one book element that is the child of the bookstore element
/bookstore/book[position()<3]	Selects the first two book elements that are children of the bookstore element
//title[@lang]	Selects all the title elements that have an attribute named lang
//title[@lang='eng']	Selects all the title elements that have an attribute named lang with a value of 'eng'
/bookstore/book[price>35.00]	Selects all the book elements of the bookstore element that have a price element with a value greater than 35.00
/bookstore/book[price>35.00]/title	Selects all the title elements of the book elements of the bookstore element that have a price element with a value greater than 35.00

---

Selecting Unknown Nodes

XPath wildcards can be used to select unknown XML elements.

Wildcard	Description
*	Matches any element node
@*	Matches any attribute node
node()	Matches any node of any kind

In the table below we have listed some path expressions and the result of the expressions:

Path Expression	Result
/bookstore/*	Selects all the child nodes of the bookstore element
//*	Selects all elements in the document
//title[@*]	Selects all title elements which have any attribute

---

Selecting Several Paths

By using the | operator in an XPath expression you can select several paths.

In the table below we have listed some path expressions and the result of the expressions:

Path Expression	Result
//book/title | //book/price	Selects all the title AND price elements of all book elements
//title | //price	Selects all the title AND price elements in the document
/bookstore/book/title | //price	Selects all the title elements of the book element of the bookstore element AND all the price elements in the document

DML Error Logging

By default, when a DML statement fails the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected.

 Adding the appropriate LOG ERRORS clause on to most INSERT, UPDATE, MERGE and DELETE statements enables the operations to complete, regardless of errors.

LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer|UNLIMITED]

The simple_expression is used to specify a tag that makes the errors easier to identify.

For parallel DML operations, the reject limit is applied to each parallel server.


 A log table must be created for every base table that requires the DML error logging functionality.

BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => '<tablename>');
END;
/

log table name matches the first 25 characters of the base table with the "ERR$_" prefix





begin
   dbms_errlog.create_error_log ( dml_table_name => 'destinationTab');
end;
/

Insert :


INSERT INTO destinationTab
SELECT *
FROM   sourceTab
LOG ERRORS  ('INSERT') REJECT LIMIT UNLIMITED;


Update :

Update destinationTab set col1=xxx,col2=yyyy,col3=zzzz where id between 1 to 1000
Log Errors ('UPDATE') Reject Limit Unlimited;



Merge :

Merge into destinationTab d
using SourceTab s
on (s.id=d.id)
When matched then 
  update set d.col1=s.col1,d.col2=s.col2
When not matched then
  insert (id,col1,col2) values (s.id,s.col1,s.col2)
Log Errors ('Merge') Reject Limit 10;


Delete :

Delete destinationTab where id between 1 and 1000
Log Errors ('Delete') Reject Limit 100;

driving_site hint

 In a true distributed query, the optimization is done on the sending site. 
 Because your local site may not have access to the CBO statistics on the remote site, Oracle "punts" and does a full-table scan, and this is likely the cause of your full scan.

The driving_site hint forces query execution to be done at a different site than the initiating instance.
This is done when the remote table is much larger than the local table and you want the work (join, sorting) done remotely to save the back-and-forth network traffic.

select /*+DRIVING_SITE(r)*/
   columnNames,.....
from
  localTable        l,
  huge_Remote_table@dblink_xyz r
where
   l.columnName_XX = r.columnName_XX;