XMLELEMENT function in PL/SQL

The XMLELEMENT function allows developers to read values from Oracle tables and present them in XML format along with wrapping the values in descriptive XML tags. The general format for XMLELEMENT is: SELECT XMLELEMENT("tag_name", TABLE.field_name1), XMLELEMENT("tag_name", TABLE.field_name2) FROM TableName; The XMLELEMENT function creates an XML element in XMLType by taking the tag_name and the information […]
Continue reading…

Enjoyed this post? Share it!

 
 

XMLFOREST function in PL/SQL

The XMLFOREST function allows you to read values from Oracle tables and present them in XML format. The general format for the XMLFOREST function is: SELECT XMLFOREST(TABLE.field_name1, TABLE.field_name2, …) "alias" FROM TableName; This function converts each of its argument parameters (field names from tables) to XML and returns an XML fragment that is a concatenation […]
Continue reading…

Enjoyed this post? Share it!

 
 

Querying XML type data using SQL – TABLE(XMLSEQUENCE)

The table(xmlsequence) can be used to return values of multiple nodes from an XML. XMLSequence returns a VARRAY of XMLType. It splits multi-value results from XMLTYPE queries into multiple rows. The TABLE function, can be used to query this VARRAY. Consider the table DEPT and the column name of XMLType be lib_info Sample Input XML […]
Continue reading…

Enjoyed this post? Share it!

 
 

Modifying XML type data using SQL – updateXML

The SQL function updateXML replaces a single XML node or multiple XML nodes in an XML instance. Syntax: UPDATEXML(<XMLType_Instance>, <XPath_String>, <value_expr>) Sample Input XML: <department> <library> <book> <title>A</title> <author>abc</author> <description>AAA</description> </book> <book> <title>D</title> <author>def</author> </book> </library> </department> Example Code: UPDATE xml_table SET input_xml = UPDATEXML(input_xml, ‘department/library/book/title/text()’, ‘Z’) WHERE EXTRACTVALUE(input_xml, ‘/department/library/book/title’) = ‘A’ Output XML: <department> […]
Continue reading…

Enjoyed this post? Share it!

 
 

Modifying XML type data using SQL – deleteXML

The SQL function deleteXML removes nodes of any kind given in the XPath expression from an XML instance. Syntax: deleteXML(<XMLType_Instance>, <XPath_String>) Sample Input XML: <department> <library> <book> <title>A</title> <author>abc</author> <description>AAA</description> </book> <book> <title>D</title> <author>def</author> <description>DDD</description> </book> </library> </department> Example Code: UPDATE xml_table SET input_xml = deleteXML(input_xml, ‘department/library/book’) WHERE EXTRACTVALUE(input_xml, ‘/department/library/book/title’) = ‘A’ Output XML: <department> […]
Continue reading…

Enjoyed this post? Share it!

 
 

Modifying XML type data using SQL – insertXMLafter

The SQL function insertXMLafter inserts elements of any kind to an XML instance immediately after the node specified in the Xpath expression. Syntax: insertXMLafter(<XMLType_Instance>, <XPath_String>, <value_expr>) Sample Input XML: <department> <library> <book> <title>A</title> <author>abc</author> <description>AAA</description> </book> </library> </department> Example Code: UPDATE xml_table SET input_xml = insertXMLafter(input_xml, ‘department/library’,XMLType('<book><title>L</title>’)) WHERE EXTRACTVALUE(input_xml, ‘/department/library/book/title’) = ‘A’ Output XML: <department> […]
Continue reading…

Enjoyed this post? Share it!

 
 

Modifying XML type data using SQL – insertXMLbefore

The SQL function insertXMLbefore inserts elements of any kind to an XML instance just before the node specified in the Xpath expression. Syntax: insertXMLbefore(<XMLType_Instance>, <XPath_String>, <value_expr>) Sample Input XML: <department> <library> <book> <title>A</title> <author>abc</author> <description>AAA</description> </book> <book> <title>D</title> <author>def</author> <description>DDD</description> </book> <book> <title>L</title> <author>lmn</author> <description>LLL</description> </book> </library> </department> Example Code: UPDATE xml_table SET input_xml = […]
Continue reading…

Enjoyed this post? Share it!

 
 

Modifying XML type data using SQL – insertchildXML

The SQL function insertchildXML inserts one or more child XML elements or a single attribute to an XML instance at the node specified in the Xpath expression. Syntax: insertchildXML(<XMLType_Instance>, <XPath_String>, <child_expr>, <value_expr>) Sample Input XML: <department> <library> <book> <title>A</title> <author>abc</author> <description>AAA</description> </book> <book> <title>D</title> <author>def</author> <description>DDD</description> </book> <book> <title>L</title> <author>lmn</author> <description>LLL</description> </book> </library> </department> Example […]
Continue reading…

Enjoyed this post? Share it!

 
 

Querying XML type data using SQL – XMLExists

The SQL function XMLExists is similar to existsNode function except that it accepts a XML query expression and checks if it returns a non-empty sequence. The function returns a boolean, TRUE if the node is present, else it returns FALSE. Syntax: XMLExists(<XQuery_expression>) Sample Input XML: <department> <library> <book> <title>A</title> <author>abc</author> <description>AAA</description> </book> <book> <title>D</title> <author>def</author> […]
Continue reading…

Enjoyed this post? Share it!

 
 

Querying XML type data using SQL – complete query

The following example illustrates querying XML type data using SQL by utilizing functions such as extract, extractvalue and existsnode. Sample Input XML: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 <department> <library> <book> <title>A</title> <author>abc</author> <description>AAA</description> </book> <book> <title>D</title> <author>def</author> <description>DDD</description> </book> <book> <title>L</title> […]
Continue reading…

Enjoyed this post? Share it!

 
 

Querying XML type data using SQL – extract

The SQL function EXTRACT is similar to EXTRACTVALUE except that it extracts the node or a set of nodes from the XML document. It does not return the scalar value of a node. It returns an XML fragment. Syntax: 1 EXTRACT(<XMLType_Instance>, <XPath_string>, <namespace_string>) is optional. Sample Input XML: 1 2 3 4 5 6 7 […]
Continue reading…

Enjoyed this post? Share it!

 
 

Querying XML type data using SQL – extractValue

The SQL function extractValue determines value of a single resultant node. It extracts the value without any XML element tags. Syntax: 1 EXTRACTVALUE(<XMLType_Instance>, <XPath_string>, <namespace_string>) Sample Input XML: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 <department> <library> <book> <title>A</title> <author>abc</author> <description>AAA</description> </book> <book> […]
Continue reading…

Enjoyed this post? Share it!

 
 

Querying XML type data using SQL – existsNode

The SQL function existsNode determines whether the given XPath path references to any XML element node. The function returns 1 if the node is present, else it returns 0. Syntax: 1 EXISTSNODE(<XMLType_Instance>, <XPath_string>, <namespace_string>) Sample Input XML: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 […]
Continue reading…

Enjoyed this post? Share it!