w3mentor
Learning web technologies simplified!
 
Home / PL/SQL / Archive by category 'PL/SQL - XML'

PL/SQL – XML

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 from the specified field of a table as arguments. The tag_name is used to create an XML tag.

For example, in the following query we will read the same two attributes, stno and sname, from the Student table and present the query in XML format, wrapped in descriptive XML tags:

SELECT XMLELEMENT("Student_Number", stno),
XMLELEMENT("Name", sname)
FROM Student;

output:

XMLELEMENT("STUDENT_NUMBER",STNO)
--------------------------------------------------------------------
XMLELEMENT("NAME",SNAME)
--------------------------------------------------------------------
<Student_Number>2</Student_Number>
<Name>ab</Name>
 
<Student_Number>3</Student_Number>
<Name>Mani</Name>

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 of each of the arguments (field names) converted to XML. The “alias” is a required part of the XMLFOREST function.

Example:

SELECT XMLFOREST(s.stno, s.sname) "Student name and number"
FROM Student s;

Output:

Student name and number
--------------------------------------------------------------------
<STNO>2</STNO>
<SNAME>kaka</SNAME>
 
<STNO>3</STNO>
<SNAME>bunny</SNAME>

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 in the column lib_info:

<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 1:

SELECT EXTRACTVALUE (VALUE (b), '*/description') Book_Description
  FROM Dept,
       TABLE (XMLSEQUENCE (EXTRACT (lib_info, '*/library/book'))) b

Output:
BOOK_DESCRIPTION
—————–
AAA
DDD
LLL

Example Code 2:

SELECT EXTRACTVALUE (VALUE (b), '*/title') Book_title
  FROM Dept, TABLE (XMLSEQUENCE (EXTRACT (lib_info, '*/library/book'))) b
 WHERE EXTRACTVALUE (VALUE (b), '*/author') = 'abc'

Output:

BOOK_TITLE
————-
A

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>
  <library>
    <book>
       <title>Z</title>
       <author>abc</author>
       <description>AAA</description>
    </book>
    <book>
       <title>D</title>
       <author>def</author>
    </book>
    </library>
</department>

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>
  <library>
    <book>
       <title>D</title>
       <author>def</author>
       <description>DDD</description>
    </book>
   </library>
</department>

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>
  <library>
    <book>
       <title>A</title>
       <author>abc</author>
       <description>AAA</description>
    </book>
    <book>
       <title>L</title>
     </book>
   </library>
</department>

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 = insertXMLbefore(input_xml, 'department/library', XMLType('<book><title>L</title>'))
WHERE EXTRACTVALUE(input_xml, '/department/library/book/title') = 'A'

Output XML:

<department>
  <library>
    <book>
       <title>L</title>
     </book>
    <book>
       <title>A</title>
       <author>abc</author>
       <description>AAA</description>
    </book>
   </library>
</department>

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 Code:

UPDATE xml_table
SET input_xml = INSERTCHILDXML(input_xml, 'department/library','book', XMLType('<book><title>L</title><description>LLL</description></book>'))

Output XML:

<department>
  <library>
    <book>
       <title>A</title>
       <author>abc</author>
       <description>AAA</description>
    </book>
    <book>
       <title>L</title>
       <description>LLL</description>
    </book>
  </library>
</department>

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>
       <description>DDD</description>
    </book>
    <book>
       <title>L</title>
       <author>lmn</author>
       <description>LLL</description>
    </book>
  </library>
</department>

Example Code:

SELECT input_xml
FROM xml_table
WHERE XMLExists('/library/book[author="abc"]' PASSING input_xml)

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>
       <author>lmn</author>
       <description>LLL</description>
    </book>
  </library>
</department>

Example Code:

1
2
3
4

SELECT  EXTRACT (input_xml, '/department/library/book').getStringVal()
FROM  xml_table
WHERE  EXISTSNODE (input_xml, '/department/library') = 1
AND  EXTRACTVALUE(input_xml, '/department/library/book/author') = 'def'

This query would return the following result:

1
2
3
4
5

   <book>
       <title>D</title>
       <author>def</author>
       <description>DDD</description>
    </book>

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
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>
       <author>lmn</author>
       <description>LLL</description>
    </book>
  </library>
</department>

Example Code:

1

EXTRACT (input_xml, '/department/library/book’).getStringVal()

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>
       <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:

1

EXTRACTVALUE(input_xml, '/department/library/book/author') = 'def'

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
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>
       <author>lmn</author>
       <description>LLL</description>
    </book>
  </library>
</department>

Example Code:

1

EXISTSNODE (input_xml, '/department/library/book') = 0

Latest Tutorials & Code Snippets

Reference Guides