{"id":25974,"date":"2024-10-21T22:58:47","date_gmt":"2024-10-21T22:58:47","guid":{"rendered":"https:\/\/sonra.io\/?p=25974"},"modified":"2024-12-12T16:35:58","modified_gmt":"2024-12-12T16:35:58","slug":"oracle-sql-xml-extract-extractvalue","status":"publish","type":"post","link":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/","title":{"rendered":"Oracle EXTRACT &amp; EXTRACTVALUE for XML (Guide &amp; Examples)"},"content":{"rendered":"\n<p>When you need to extract and\/or query an Oracle database for XML data, there are two SQL functions that come to mind, \u2018<a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/EXTRACT-XML.html\" target=\"_blank\" rel=\"noreferrer noopener\">EXTRACT<\/a>\u2019 and \u2018<a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/EXTRACTVALUE.html\" target=\"_blank\" rel=\"noreferrer noopener\">EXTRACTVALUE<\/a>\u2019. I\u2019m going to delve into these two functions and provide some examples. But first, a note of caution:<\/p>\n\n\n\n<p><em>Both EXTRACT and EXTRACTVALUE serve similar purposes, but they each have their own characteristics and are suited for different use cases.<\/em><\/p>\n\n\n\n<p>Let\u2019s take a look at how the EXTRACT and EXTRACTVALUE functions work, which will help you understand their subtle, but important differences. Then I\u2019ll dive deep into a few use cases and examples.<\/p>\n\n\n\n<p>Finally, I\u2019ll wrap up the blog post with alternative methods of converting XML to Oracle tables and SQL.<\/p>\n\n\n<div class=\"note-block\">\n\t<div class=\"note-block-icon\"><\/div>\n    \t<h4>Important note<\/h4>\n    \t<div class=\"note-block-content\"><p>Both EXTRACT and EXTRACTVALUE have been deprecated for newer versions than Oracle Database 11g Release 2 (11.2). I recommend users to transition to <a href=\"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/#recommended-alternatives-to-extract-and-extractvalue\" target=\"_blank href=\">alternative XML processing<\/a> methods covered at the end of this article. EXTRACT and EXTRACTVALUE should only be used for legacy implementations and purposes.<\/p>\n<\/div>\n<\/div>\n\n\n<script src=\"https:\/\/sonra.io\/wp-content\/my-rcrs\/pdpopup.js\" defer=\"\"><\/script>\n<div class=\"popup-container\">\n    <div class=\"pdpopup\">\n        <span id=\"cls-popup\">&#10005;<\/span>\n        <div class=\"popup-cnt\">\n            <div class=\"popup1st\">\n                <p>Use Flexter to turn <span style=\"color:#3D68F8;\">XML<\/span> and <span style=\"color:#3D68F8;\">JSON<\/span> into Valuable Insights<\/p>\n                <ul class=\"flextlist\"><li>100% Automation<\/li><li>0% Coding<\/li><\/ul>\n                <div class=\"ppbtns\">\n                    <a href=\"https:\/\/sonra.io\/flexter-product-page\/\" class=\"button-effect jmp-btn\" target=\"_blank\" rel=\"noopener\">Learn More<\/a><a href=\"https:\/\/sonra.io\/xml-to-csv-converter\/\" class=\"button-effect jmp-btn\" rel=\"noopener\" target=\"_blank\">Try For Free<\/a>\n                <\/div>\n            <\/div>         \n        <\/div>\n    <\/div>\n<\/div>\n\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-25974-_dyrl3ubswzgw\"><\/a>Understanding Oracle SQL Functions &#8211; EXTRACT and EXTRACTVALUE for XML<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">EXTRACT<\/h3>\n\n\n\n<p>Let\u2019s first have a look at EXTRACT<\/p>\n\n\n\n<p><strong>Purpose:<\/strong> EXTRACT retrieves XML fragments from an XMLType instance<\/p>\n\n\n\n<p>An XML fragment is just that, a fragment of an XML document with well-formed XML content in it, yet is not a complete document in itself. A complete XML document usually has a single root element, maybe a prolog, and follows a rigid structure. On the other hand a fragment can be a snippet of XML code representing part of a larger document.<\/p>\n\n\n\n<p>Look at this well formed XML document<\/p>\n\n\n<pre class=\"lang:default decode:true\">&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?&gt;\n&lt;books&gt;\n    &lt;book&gt;\n        &lt;title&gt;XML for Beginners&lt;\/title&gt;\n        &lt;author&gt;John Doe&lt;\/author&gt;\n    &lt;\/book&gt;\n&lt;\/books&gt;<\/pre>\n\n\n<p>An XML fragment based on this XML document could look like this<\/p>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n<pre class=\"lang:default decode:true\">    &lt;book&gt;\n        &lt;title&gt;XML for Beginners&lt;\/title&gt;\n        &lt;author&gt;John Doe&lt;\/author&gt;\n    &lt;\/book&gt;<\/pre>\n\n\n<p>This is an XML fragment because it shows only part of a bigger XML document.<\/p>\n\n\n\n<p><strong>Return type<\/strong>: XMLType<\/p>\n\n\n\n<p><strong>Syntax<\/strong>: EXTRACT(XMLType_instance, XPath_string [, namespace_string ])<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1999\" height=\"198\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-the-extract-function-syntax-showing-xm.png\" alt=\"Diagram of the EXTRACT function syntax, showing XMLType instance, XPath, and namespace.\" class=\"wp-image-25975\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-the-extract-function-syntax-showing-xm.png 1999w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-the-extract-function-syntax-showing-xm-300x30.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-the-extract-function-syntax-showing-xm-1024x101.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-the-extract-function-syntax-showing-xm-768x76.png 768w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-the-extract-function-syntax-showing-xm-1536x152.png 1536w\" sizes=\"(max-width: 1999px) 100vw, 1999px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Fig 1: <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/EXTRACT-XML.html\" target=\"_blank\" rel=\"noreferrer noopener\">XML Extraction Functions Overview<\/a><\/p>\n\n\n\n<p>The syntax structure diagram illustrates the process of retrieving data from XML documents in Oracle using the SQL function EXTRACT. It returns an XML fragment as an Oracle XML data type. <\/p>\n\n\n<div class=\"note-block\">\n\t<div class=\"note-block-icon\"><\/div>\n    \t<h4>Note<\/h4>\n    \t<div class=\"note-block-content\"><p>XMLType_instance, XPath_string both are mandatory in the Extract Function. Parameter namespace_string is optional<\/p>\n<\/div>\n<\/div>\n\n\n<p>Let\u2019s go through an example with some sample XML data<\/p>\n\n\n<pre class=\"lang:default decode:true \">&lt;students&gt;\n    &lt;student&gt;\n        &lt;name&gt;John Doe&lt;\/name&gt;\n        &lt;age&gt;20&lt;\/age&gt;\n        &lt;grade&gt;A&lt;\/grade&gt;\n    &lt;\/student&gt;\n    &lt;student&gt;\n        &lt;name&gt;Jane Smith&lt;\/name&gt;\n        &lt;age&gt;22&lt;\/age&gt;\n        &lt;grade&gt;B&lt;\/grade&gt;\n    &lt;\/student&gt;\n&lt;\/students&gt;<\/pre>\n\n\n<p><strong>Example query:<\/strong><\/p>\n\n\n<pre class=\"lang:default decode:true\">SELECT \n    EXTRACT(xml_data, '\/student\/name') AS student_name\nFROM \n    students_table;<\/pre>\n\n\n<p><strong>Output:<\/strong><\/p>\n\n\n<pre class=\"lang:default decode:true \">&lt;name&gt;John Doe&lt;\/name&gt;<\/pre>\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-25974-_q1o59cmmbxmq\"><\/a>EXTRACTVALUE<\/h3>\n\n\n\n<p><strong>Purpose<\/strong>: Retrieves the text value of an XML fragment<\/p>\n\n\n\n<p><strong>Return Type<\/strong>: VARCHAR2<\/p>\n\n\n\n<p><strong>Syntax<\/strong>: EXTRACTVALUE(XMLType_instance, XPath_string [, namespace_string ])<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1999\" height=\"192\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-the-extractvalue-function-syntax-showin.png\" alt=\"Diagram of the EXTRACTVALUE function syntax showing XMLType instance, XPath, and namespace.\" class=\"wp-image-25976\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-the-extractvalue-function-syntax-showin.png 1999w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-the-extractvalue-function-syntax-showin-300x29.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-the-extractvalue-function-syntax-showin-1024x98.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-the-extractvalue-function-syntax-showin-768x74.png 768w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-the-extractvalue-function-syntax-showin-1536x148.png 1536w\" sizes=\"(max-width: 1999px) 100vw, 1999px\" \/><\/span><\/figure>\n\n\n\n<p>Fig 2: <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/EXTRACTVALUE.html#GUID-20AB974B-7544-4F44-B539-787FB6145680\" target=\"_blank\" rel=\"noreferrer noopener\">XML Extraction Functions Overview<\/a><\/p>\n\n\n\n<p>The diagram above illustrates the process of retrieving data from XML documents in a database using SQL function EXTRACTVALUE and returns scalar values.<\/p>\n\n\n\n<p>Note: XMLType_instance and XPath_string are mandatory in the EXTRACTVALUE Function. The parameter namespace_string is optional, just as in EXTRACT.<\/p>\n\n\n\n<p><strong>Example<\/strong><\/p>\n\n\n<pre class=\"lang:default decode:true\">SELECT \n    EXTRACTVALUE(xml_data, '\/student\/name') AS student_name \nFROM \n    students_table;<\/pre>\n\n\n<p><strong>Output<\/strong><\/p>\n\n\n<pre class=\"lang:default decode:true \">John Doe<\/pre>\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-25974-_52pd699rtb9q\"><\/a>XPath and namespaces in EXTRACT and EXTRACTVALUE<\/h2>\n\n\n\n<p>Both the EXTRACT and EXTRACTVALUE functions accept an XPath and a namespace as parameters. Now, let&#8217;s take a look at Xpath and namespace in more detail.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-25974-_gwyp87n6oppj\"><\/a>XPath<\/h3>\n\n\n\n<p>XPath, (XML Path Language), is a query language that\u2019s used with EXTRACT and EXTRACTVALUE for extraction of certain specific information from an XML document. It works by identifying and selecting nodes (elements, attributes, text) that are based on the hierarchical structure of the document.<\/p>\n\n\n\n<p>EXTRACT and EXTRACTVALUE both support XPath 1.0, which is itself a subset of XPath. Therefore, some advanced XPath 1.0 features aren\u2019t fully supported. The latest version of XPath is 2.0.<\/p>\n\n\n\n<p>Supported Features:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Basic Path Expressions<\/strong>: You can select nodes using expressions like \/, \/\/, and specific node names.<\/li>\n\n\n\n<li><strong>Attribute Selection<\/strong>: Attributes can be selected using the @ symbol.<\/li>\n<\/ul>\n\n\n\n<p>XPath expressions are formulated using a combination of path expressions and node tests. The basic syntax includes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Relative XPath<\/strong>: Starts with double forward slashes \/\/, allowing searches anywhere in the document. For instance, \/\/name retrieves all name elements regardless of their position in the hierarchy.<\/li>\n\n\n\n<li><strong>Absolute XPath<\/strong>: Begins with a single forward slash \/, indicating the root node. For example, \/School\/class\/name selects the name element of all class nodes.<\/li>\n<\/ul>\n\n\n\n<p><strong>Example EXTRACT with relative XPath:<\/strong><\/p>\n\n\n<pre class=\"lang:default decode:true \">SELECT \n    EXTRACT(xml_data, '\/\/class\/name').getStringVal() AS class_names\nFROM \n    xml_table;<\/pre>\n\n\n<p>As you can see, this query uses the XPath expression \/\/class\/name to find all &lt;name&gt; elements that are children of &lt;class&gt; elements. The getStringVal() method converts the extracted XML nodes into string values.<\/p>\n\n\n\n<p>Output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1806\" height=\"142\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class_names-with-values-advanced-mat.jpeg\" alt=\"Table showing CLASS_NAMES with values Advanced Mathematics and World History\" class=\"wp-image-25977\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class_names-with-values-advanced-mat.jpeg 1806w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class_names-with-values-advanced-mat-300x24.jpeg 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class_names-with-values-advanced-mat-1024x81.jpeg 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class_names-with-values-advanced-mat-768x60.jpeg 768w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class_names-with-values-advanced-mat-1536x121.jpeg 1536w\" sizes=\"(max-width: 1806px) 100vw, 1806px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p><strong>Example: EXTRACTVALUE with absolute XPath:<\/strong><\/p>\n\n\n<pre class=\"lang:default decode:true \">SELECT \n    EXTRACTVALUE(xml_data, '(\/student\/name)[1]') AS student_name\nFROM \n    xml_table;<\/pre>\n\n\n<p>The XPath expression(\/student\/name)[1] is used to select only the first &lt;name&gt; element under &lt;student&gt;. This allows for precise extraction of information based on attributes.<\/p>\n\n\n\n<p>Output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"201\" height=\"84\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-cell-showing-student_name-with-the-value.png\" alt=\"Table cell showing STUDENT_NAME with the value\" class=\"wp-image-25978\"\/><\/span><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-25974-_1ajx1rwzrxq9\"><\/a>XML namespaces<\/h3>\n\n\n\n<p>To avoid the element name conflicts that can occur with XML documents, I use XML namespaces. This allows me the ability to use prefixes linked to specific URIs, Uniform Resource Identifiers), to create a unique identifier for each element and attribute.<\/p>\n\n\n\n<p>For example, when combining XML documents from different sources or apps, this can be very useful.It\u2019s not uncommon to have elements that have the same name but different structures or meanings, which can cause confusion.<\/p>\n\n\n\n<p>Namespaces are defined using the xmlns attribute, either with a prefix (e.g., xmlns:prefix=&#8221;URI&#8221;) for specific elements or as a default namespace (xmlns=&#8221;URI&#8221;) for all child elements.<\/p>\n\n\n\n<p>By using namespaces, developers can ensure that their XML documents remain unambiguous and can be correctly interpreted by parsers and applications.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-25974-_zhfy32u49a2g\"><\/a>Comparison of EXTRACT and EXTRACTVALUE<\/h3>\n\n\n\n<p>You should use EXTRACT when you need the full context of the data within its XML structure. For instance, if you&#8217;re working with complex XML documents where relationships between elements are important.<\/p>\n\n\n\n<p>You should use EXTRACTVALUE for queries where you only need specific values. For example, extracting a single value like a product price or order number without needing additional context.<\/p>\n\n\n\n<p>Here is a comparison table that shows the differences between EXTRACT and EXTRACTVALUE<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>&nbsp;<\/th><th><strong>EXTRACT<\/strong><\/th><th><strong>EXTRACTVALUE<\/strong><\/th><\/tr><tr><td>\n<p><strong>Return Data Type<\/strong><\/p>\n<\/td><td>\n<p>XMLType<\/p>\n<\/td><td>\n<p>VARCHAR2<\/p>\n<\/td><\/tr><tr><td>\n<p><strong>Input<\/strong><\/p>\n<\/td><td>\n<p>XML document(XML data)<\/p>\n<\/td><td>\n<p>XML document(XML data)<\/p>\n<\/td><\/tr><tr><td>\n<p><strong>Output<\/strong><\/p>\n<\/td><td>\n<p>XML fragment with tags<\/p>\n<\/td><td>\n<p>Text content only<\/p>\n<\/td><\/tr><tr><td>\n<p><strong>Use Case<\/strong><\/p>\n<\/td><td>\n<p>When XML structure is needed<\/p>\n<\/td><td>\n<p>When only text value is needed<\/p>\n<\/td><\/tr><tr><td>\n<p><strong>Data Handling<\/strong><\/p>\n<\/td><td>\n<p>It operates on a single column<\/p>\n<\/td><td>\n<p>It can navigate complex XML structures.<\/p>\n<\/td><\/tr><tr><td>\n<p><strong>Syntax<\/strong><\/p>\n<\/td><td>\n<p>EXTRACT(XMLType_instance, XPath_string [, namespace_string ])<\/p>\n<\/td><td>\n<p>EXTRACTVALUE(XMLType_instance, XPath_string [, namespace_string ])<\/p>\n<\/td><\/tr><\/thead><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-25974-_a7h20maaagpm\"><\/a>Oracle EXTRACT and EXTRACTVALUE Examples for XML<\/h2>\n\n\n\n<p>In this section I\u2019ve compiled some common scenarios, use cases, and examples for using EXTRACT and EXTRACTVALUES<\/p>\n\n\n\n<p>I\u2019ve created two XML sample files. One sample without namespaces and a second example that contains namespaces.<\/p>\n\n\n\n<p><strong>Sample XML 1 without namespaces:<\/strong><\/p>\n\n\n\n<p>Step 1: Creating a table for XML data named xml_table_sample1.<\/p>\n\n\n<pre class=\"lang:default decode:true \">CREATE TABLE xml_table_sample1 (\n    id NUMBER PRIMARY KEY,\n    xml_data XMLType\n);<\/pre>\n\n\n<p>Step 2: Inserting data into xml_table_sample1<\/p>\n\n\n<pre class=\"lang:default decode:true \">INSERT INTO  xml_table_sample1 (id, xml_data) \nVALUES (1, XMLType('\n&lt;school&gt;\n    &lt;classes&gt;\n        &lt;class id=\"C001\"&gt;\n            &lt;name&gt;Advanced Mathematics&lt;\/name&gt;\n            &lt;teacher&gt;Prof. Einstein&lt;\/teacher&gt;\n            &lt;students&gt;\n                &lt;student id=\"S001\"&gt;\n                    &lt;name&gt;Rick Grimes&lt;\/name&gt;\n                    &lt;age&gt;35&lt;\/age&gt;\n                    &lt;subjects&gt;\n                        &lt;subject&gt;Calculus&lt;\/subject&gt;\n                        &lt;subject&gt;Linear Algebra&lt;\/subject&gt;\n                    &lt;\/subjects&gt;\n                    &lt;info&gt;\n                        &lt;gender&gt;Male&lt;\/gender&gt;\n                        &lt;hobby&gt;Leadership&lt;\/hobby&gt;\n                    &lt;\/info&gt;\n                &lt;\/student&gt;\n                &lt;student id=\"S002\"&gt;\n                    &lt;name&gt;Michonne Hawthorne&lt;\/name&gt;\n                    &lt;age&gt;30&lt;\/age&gt;\n                    &lt;subjects&gt;\n                        &lt;subject&gt;Statistics&lt;\/subject&gt;\n                        &lt;subject&gt;Number Theory&lt;\/subject&gt;\n                    &lt;\/subjects&gt;\n                    &lt;info&gt;\n                        &lt;gender&gt;Female&lt;\/gender&gt;\n                        &lt;hobby&gt;Sword Fighting&lt;\/hobby&gt;\n                    &lt;\/info&gt;\n                &lt;\/student&gt;\n            &lt;\/students&gt;\n        &lt;\/class&gt;\n        &lt;class id=\"C002\"&gt;\n            &lt;name&gt;World History&lt;\/name&gt;\n            &lt;teacher&gt;Dr. Who&lt;\/teacher&gt;\n            &lt;students&gt;\n                &lt;student id=\"S003\"&gt;\n                    &lt;name&gt;Daryl Dixon&lt;\/name&gt;\n                    &lt;age&gt;33&lt;\/age&gt;\n                    &lt;subjects&gt;\n                        &lt;subject&gt;Ancient Civilizations&lt;\/subject&gt;\n                        &lt;subject&gt;Modern Warfare&lt;\/subject&gt;\n                    &lt;\/subjects&gt;\n                    &lt;info&gt;\n                        &lt;gender&gt;Male&lt;\/gender&gt;\n                        &lt;hobby&gt;Archery&lt;\/hobby&gt;\n                    &lt;\/info&gt;\n                &lt;\/student&gt;\n            &lt;\/students&gt;\n        &lt;\/class&gt;\n    &lt;\/classes&gt;\n&lt;\/school&gt;'\n));<\/pre>\n\n\n<p><strong>Sample XML 2 with namespaces:<\/strong><\/p>\n\n\n\n<p>Step 1: Creating a table for XML data named xml_table_sample2.<\/p>\n\n\n<pre class=\"lang:default decode:true \">CREATE TABLE xml_table_sample2 (\n\tid NUMBER,\n\txml_data XMLTYPE\n);<\/pre>\n\n\n<p>Step 2: Inserting Data into xml_table_sample2:<\/p>\n\n\n<pre class=\"lang:default decode:true \">INSERT INTO xml_table_sample2 (id, xml_data) \nVALUES (1, XMLType('\n&lt;school xmlns=\"http:\/\/teresian.com\/school\" \nxmlns:extra=\"http:\/\/teresian.com\/extra\"&gt;\n    &lt;classes&gt;\n        &lt;class id=\"C001\"&gt;\n            &lt;name&gt;Advanced Mathematics&lt;\/name&gt;\n            &lt;teacher&gt;Prof. Einstein&lt;\/teacher&gt;\n            &lt;students&gt;\n                &lt;student id=\"S001\"&gt;\n                    &lt;name&gt;Rick Grimes&lt;\/name&gt;\n                    &lt;age&gt;35&lt;\/age&gt;\n                    &lt;subjects&gt;\n                        &lt;subject&gt;Calculus&lt;\/subject&gt;\n                        &lt;subject&gt;Linear Algebra&lt;\/subject&gt;\n                    &lt;\/subjects&gt;\n                    &lt;extra:info&gt;\n                        &lt;extra:gender&gt;Male&lt;\/extra:gender&gt;\n                        &lt;extra:hobby&gt;Leadership&lt;\/extra:hobby&gt;\n                    &lt;\/extra:info&gt;\n                &lt;\/student&gt;\n                &lt;student id=\"S002\"&gt;\n                    &lt;name&gt;Michonne Hawthorne&lt;\/name&gt;\n                    &lt;age&gt;30&lt;\/age&gt;\n                    &lt;subjects&gt;\n                        &lt;subject&gt;Statistics&lt;\/subject&gt;\n                        &lt;subject&gt;Number Theory&lt;\/subject&gt;\n                    &lt;\/subjects&gt;\n                    &lt;extra:info&gt;\n                        &lt;extra:gender&gt;Female&lt;\/extra:gender&gt;\n                        &lt;extra:hobby&gt;Sword Fighting&lt;\/extra:hobby&gt;\n                    &lt;\/extra:info&gt;\n                &lt;\/student&gt;\n            &lt;\/students&gt;\n        &lt;\/class&gt;\n        &lt;class id=\"C002\"&gt;\n            &lt;name&gt;World History&lt;\/name&gt;\n            &lt;teacher&gt;Dr. Who&lt;\/teacher&gt;\n            &lt;students&gt;\n                &lt;student id=\"S003\"&gt;\n                    &lt;name&gt;Daryl Dixon&lt;\/name&gt;\n                    &lt;age&gt;33&lt;\/age&gt;\n                    &lt;subjects&gt;\n                        &lt;subject&gt;Ancient Civilizations&lt;\/subject&gt;\n                        &lt;subject&gt;Modern Warfare&lt;\/subject&gt;\n                    &lt;\/subjects&gt;\n                    &lt;extra:info&gt;\n                        &lt;extra:gender&gt;Male&lt;\/extra:gender&gt;\n                        &lt;extra:hobby&gt;Archery&lt;\/extra:hobby&gt;\n                    &lt;\/extra:info&gt;\n                &lt;\/student&gt;\n            &lt;\/students&gt;\n        &lt;\/class&gt;\n    &lt;\/classes&gt;\n&lt;\/school&gt;\n'));<\/pre>\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-25974-_pkma4nub7yha\"><\/a>Example 1: EXTRACTVALUE Tag<\/h3>\n\n\n\n<p>Extracting the value of a tag is the simplest, most basic use of the EXTRACTVALUE function.<\/p>\n\n\n\n<p>Tags (or elements) are components of XML that define the document\u2019s structure. They can hold text or other tags (or sometimes both). Every tag has an opening tag (e.g., &lt;person&gt;) as well as a closing tag (e.g., &lt;\/person&gt;).<\/p>\n\n\n\n<p><strong>Example code<\/strong>: &#8211; Extracting the Teacher Name for the class Id &#8211; \u201cC001\u201d<\/p>\n\n\n<pre class=\"lang:default decode:true \">SELECT \n    EXTRACTVALUE( xml_data,   '\/school\/classes\/class[1]\/students\/student[1]\/name') \n     AS student_name\nFROM \n    xml_table_sample1;<\/pre>\n\n\n<ul class=\"wp-block-list\">\n<li>This string, &#8216;\/school\/classes\/class[1]\/students\/student[1]\/name&#8217; for example, is an XPath expression that will specify the correct path to the specific element in the XML document you\u2019re looking for.<\/li>\n\n\n\n<li>\/school: This indicates the start of the root element &lt;school&gt;.<\/li>\n\n\n\n<li>\/classes: This navigates down to the &lt;classes&gt; child element within &lt;school&gt;.<\/li>\n\n\n\n<li>\/class[1]: This selects the <strong>first<\/strong> &lt;class&gt; element (<strong>indexing starts at 1<\/strong>).<\/li>\n\n\n\n<li>\/students: This navigates to the &lt;students&gt; child element of that class.<\/li>\n\n\n\n<li>\/student[1]: This selects the <strong>first<\/strong> &lt;student&gt; element within that class.<\/li>\n\n\n\n<li>\/name: Finally, this retrieves the content of the &lt;name&gt; element nested within that specific student.<\/li>\n<\/ul>\n\n\n\n<p>Output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"235\" height=\"63\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-student_name-rick-grimes.png\" alt=\"Table showing STUDENT_NAME Rick Grimes\" class=\"wp-image-25979\"\/><\/span><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-25974-_2gsvs5vuruww\"><\/a>Example 2: EXTRACTVALUE Attribute<\/h3>\n\n\n\n<p>Attributes are pieces of additional information which provide specific details about elements and are always a name-value pair. The name identifies the attribute&#8217;s, and the value is the data or information that goes with that name. Take a look at this example.<\/p>\n\n\n\n<p><strong>Example code<\/strong>: &#8211; Extracting the First Student&#8217;s id Attribute<\/p>\n\n\n<pre class=\"lang:default decode:true \">SELECT \n    EXTRACTVALUE( xml_data,   '\/school\/classes\/class[@id=\"C001\"]\/@id' ) \n      AS class_id\nFROM \n    xml_table_sample1;<\/pre>\n\n\n<ul class=\"wp-block-list\">\n<li>The string &#8216;\/school\/classes\/class[@id=&#8221;C001&#8243;]\/@id&#8217; is an XPath expression that specifies how to locate a specific attribute in the XML document.<\/li>\n\n\n\n<li>\/school: This indicates the start of the root element &lt;school&gt;.<\/li>\n\n\n\n<li>\/classes: This navigates down to the &lt;classes&gt; child element within &lt;school&gt;.<\/li>\n\n\n\n<li>\/class[@id=&#8221;C001&#8243;]: This selects a specific &lt;class&gt; element that has an attribute id with the value &#8220;C001&#8221;. The @ symbol denotes that we are referring to an attribute rather than a child element.<\/li>\n\n\n\n<li>\/@id: Finally, this retrieves the value of the id attribute of that specific class..<\/li>\n<\/ul>\n\n\n\n<p>Output: <\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"181\" height=\"66\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Table-cell-showing-CLASS_ID-with-the-value-C001.png\" alt=\"Table cell showing CLASS_ID with the value C001\" class=\"wp-image-25998\"\/><\/span><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-25974-_w4bqokxl2wg3\"><\/a>Example 3: EXTRACTVALUE from file<\/h3>\n\n\n\n<p>I\u2019ve created an <a href=\"https:\/\/drive.google.com\/drive\/folders\/1edCyDaRImI6xsHBQ__oHT9OHCDobGNuU?usp=sharing\" target=\"_blank\" rel=\"noreferrer noopener\">XML file<\/a> and used SQL*Loader to upload this file to Oracle. I\u2019ll then use EXTRACTVALUE in this example.<\/p>\n\n\n\n<p>Step 1. Create a table with XMLType data type to store XML data.<\/p>\n\n\n<pre class=\"lang:default decode:true \">CREATE TABLE xml_table_sample3(\nFILENAME VARCHAR2(255),\nxml_data XMLTYPE\n)<\/pre>\n\n\n<p>Step 2. Prepare a control file (.ctl) for the SQL*Loader<\/p>\n\n\n<pre class=\"lang:default decode:true \">LOAD DATA\nINFILE *\nREPLACE\nINTO TABLE xml_table_sample3\nFIELDS TERMINATED BY ',' TRAILING NULLCOLS\n(\n    FILENAME,\n    xml_data  LOBFILE(FILENAME) TERMINATED BY EOF\n)\nBEGINDATA\n\/home\/oracle\/Downloads\/DUMPS\/SampleXML1withoutnamespaces.xml<\/pre>\n\n\n<p>Step 3. Run below SQL*Loader command to load sample.xml file into the xml_tab1 table.<\/p>\n\n\n<pre class=\"lang:default decode:true\">$ sqlldr system\/oracle@orcl control=control_file.ctl log=loader_log.log<\/pre>\n<pre class=\"lang:default decode:true \">SELECT * FROM xml_table_sample3;<\/pre>\n\n\n<p>Example:<\/p>\n\n\n<pre class=\"lang:default decode:true \">SELECT \n    EXTRACTVALUE(\n        xml_data, \n        '\/school\/classes\/class[1]\/name'\n    ) AS class_name\nFROM \n    xml_table_sample3;<\/pre>\n\n\n<p>Output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"298\" height=\"87\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-cell-showing-class_name-with-the-value-advan.png\" alt=\"Table cell showing CLASS_NAME with the value Advanced Mathematics.\" class=\"wp-image-25981\"\/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-25974-_75ah5yvbkd1d\"><\/a>Example 4: EXTRACTVALUE Namespace<\/h3>\n\n\n\n<p>As I\u2019ve mentioned, namespaces are used in XML to prevent naming conflicts and to define a scope for element and attribute names. When an XML document uses namespaces its elements are often prefixed or associated with a URI.<\/p>\n\n\n\n<p>In the XML data above, the default namespace is http:\/\/teresian.com\/school, and there is an additional namespace for elements prefixed with \u201cextra\u201d.<\/p>\n\n\n\n<p>Example code: To extract gender and hobby of the first student:<\/p>\n\n\n<pre class=\"lang:default decode:true \">SELECT\n   EXTRACTVALUE(\n       xml_data,\n       '(\/s:school\/s:classes\/s:class\/s:students\/s:student)[1]\/e:info\/e:gender',\n       'xmlns:s=\"http:\/\/teresian.com\/school\" xmlns:e=\"http:\/\/teresian.com\/extra\"'\n   ) AS first_student_gender,\n   EXTRACTVALUE(\n       xml_data,\n       '(\/s:school\/s:classes\/s:class\/s:students\/s:student)[1]\/e:info\/e:hobby',\n       'xmlns:s=\"http:\/\/teresian.com\/school\" xmlns:e=\"http:\/\/teresian.com\/extra\"'\n   ) AS first_student_hobby\nFROM xml_table_sample2<\/pre>\n\n\n<p>The following query extracts the gender and hobby of the first student in the first class. The first EXTRACTVALUE call retrieves the gender and the second EXTRACTVALUE call retrieves the hobby of the first student and both XPath expressions include a namespace declaration: (xmlns=&#8221;<a href=\"http:\/\/teresian.com\/school\" target=\"_blank\" rel=\"noreferrer noopener\">http:\/\/teresian.com\/school<\/a>&#8220;) (xmlns:extra=&#8221;http:\/\/teresian.com\/extra&#8221;&#8216;) of the first student:<\/p>\n\n\n\n<p>Output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"564\" height=\"66\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-first_student_gender-as-male-and-fir.png\" alt=\"Table showing FIRST_STUDENT_GENDER as Male and FIRST_STUDENT_HOBBY as Leadership\" class=\"wp-image-25982\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-first_student_gender-as-male-and-fir.png 564w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-first_student_gender-as-male-and-fir-300x35.png 300w\" sizes=\"(max-width: 564px) 100vw, 564px\" \/><\/span><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-25974-_7t6glez3anv5\"><\/a>Example 5: EXTRACTVALUE CLOB<\/h3>\n\n\n\n<p>A data type used in Oracle to store large amounts of character data, (up to 4 GB) is known as a CLOB (Character Large Object). Basically it\u2019s a container for text data, anything from XML content to long documents. While XML is a data format, CLOB is a storage type. A CLOB can contain XML, as well as other types of text data.<\/p>\n\n\n<div class=\"note-block\">\n\t<div class=\"note-block-icon\"><\/div>\n    \t<h4>Pro tip<\/h4>\n    \t<div class=\"note-block-content\"><p>When working with Oracle, use XMLType data type instead of a CLOB to store XML data.<\/p>\n<\/div>\n<\/div>\n\n<pre class=\"lang:default decode:true \">SELECT\n   EXTRACTVALUE(\n       XMLType(xml_clob),\n       '\/school\/classes\/class[@id=\"C001\"]\/name'\n   ) AS class_name,\n   EXTRACTVALUE(\n       XMLType(xml_clob),\n       '\/school\/classes\/class\/students\/student\/@id'\n   ) AS student_id,\n   EXTRACTVALUE(\n       XMLType(xml_clob),\n       '\/school\/classes\/class\/students\/student\/info\/hobby'\n   ) AS student_hobby\nFROM clob_table;<\/pre>\n\n\n<p>The above example query returns a single row with three columns: the class name with id &#8220;C001&#8221;, the ID and the hobby of the first student in the XML. Note that &#8220;first student&#8221; in this example means the first one in the XML structure, not necessarily the student with id &#8220;C001&#8221;.<\/p>\n\n\n\n<p>Output:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-25974-_p39bi3lr4q34\"><\/a><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"610\" height=\"69\" class=\"wp-image-25983\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class_name-advanced-mathematics-stud.png\" alt=\"Table showing CLASS_NAME Advanced Mathematics STUDENT_ID S001 STUDENT_HOBBY Leadership\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class_name-advanced-mathematics-stud.png 610w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class_name-advanced-mathematics-stud-300x34.png 300w\" sizes=\"(max-width: 610px) 100vw, 610px\" \/><\/span><\/h3>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-25974-_1uj29vfwu0rp\"><\/a>Example 6: EXTRACTVALUE multiple nodes<\/h3>\n\n\n\n<p>In this example, EXTRACTVALUE is used to pull values from multiple nodes in the XML data stored in the xml_table.<\/p>\n\n\n<pre class=\"lang:default decode:true \">SELECT \n    EXTRACTVALUE(xml_data, '\/school\/classes\/class[1]\/name') AS class_name,\n    EXTRACTVALUE(xml_data, '\/school\/classes\/class[1]\/teacher') AS teacher_name,\n    EXTRACTVALUE(xml_data, '\/school\/classes\/class[1]\/students\/student[1]\/name') AS student_name,\n    EXTRACTVALUE(xml_data, '\/school\/classes\/class[1]\/students\/student[1]\/age') AS student_age\nFROM \n    xml_table_sample1;<\/pre>\n\n\n<p>This example query will return a single row with six columns, each containing specific values extracted from the XML data.<\/p>\n\n\n\n<p>The class_name column will hold the name of the first class in the &lt;classes&gt; section, while the teacher_name column will display the name of the teacher for that class.<\/p>\n\n\n\n<p>The student_name column will show the name of the first student in the first class, and the student_age column will provide the age of that student.<\/p>\n\n\n\n<p>Additionally, the student_gender column will display the gender of the first student, and the student_hobby column will contain the hobby of the same student.<\/p>\n\n\n\n<p>Output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"882\" height=\"85\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class-name-advanced-mathematics-teac.png\" alt=\"Table showing class name Advanced Mathematics teacher Prof. Einstein student Rick Grimes age 35.\" class=\"wp-image-25984\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class-name-advanced-mathematics-teac.png 882w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class-name-advanced-mathematics-teac-300x29.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class-name-advanced-mathematics-teac-768x74.png 768w\" sizes=\"(max-width: 882px) 100vw, 882px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-25974-_iu8cobfxq684\"><\/a>Limitations of EXTRACT and EXTRACTVALUE<\/h2>\n\n\n\n<p>As I mentioned at the beginning of this article, EXTRACT and EXTRACTVALUE are legacy functions for working with XML in Oracle. They have some severe limitations because of this.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>XPath support<\/strong>: Both EXTRACT and EXTRACTVALUE functions support a subset of XPath 1.0 only. Because of this, more advanced XPath features (like XPath 2.0 functions, expressions, and constructs) aren\u2019t available. For example, complex queries that involve functions like concat(), contains(), or expressions that use advanced operators (for, some, every, etc.) cannot be used with these functions.<\/li>\n\n\n\n<li><strong>Performance Bottlenecks<\/strong>: Both functions can be slow, particularly when dealing with large XML documents or large datasets with many rows. Since these functions are not designed for high-performance querying, using them on large-scale XML data can result in significant overhead and slower queries.<\/li>\n\n\n\n<li><strong>Repeated Parsing<\/strong>: Every time you call EXTRACT or EXTRACTVALUE, Oracle parses the XML document to retrieve the result. This repeated parsing degrades performance when querying the same document multiple times, especially in larger datasets.<\/li>\n\n\n\n<li><strong>Legacy<\/strong>: As of Oracle 12c, EXTRACTVALUE has been officially deprecated. Oracle now recommends using XMLQuery and XMLTable for XML processing because they offer better performance and more flexibility.<\/li>\n\n\n\n<li><strong>Handling of Large Data<\/strong>: Both EXTRACT and EXTRACTVALUE have limitations when dealing with very large XML data. When extracting data, especially scalar values using EXTRACTVALUE, the size of the returned value is limited by the maximum length of Oracle&#8217;s VARCHAR2 (up to 4000 bytes in SQL and 32767 bytes in PL\/SQL).<\/li>\n\n\n\n<li><strong>Limited XML Functionality<\/strong>: These functions are mainly designed for simple data retrieval, making them less suitable for complex XML transformations or handling large hierarchical XML structures.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-25974-_lubouxiacn9r\"><\/a>Recommended alternatives to EXTRACT and EXTRACTVALUE<\/h2>\n\n\n\n<p>While they still have their uses in certain situations, I\u2019ve recommended some alternatives for you due to limitations you might encounter when using EXTRACT and EXTRACTVALUE.<\/p>\n\n\n\n<p>They offer more robust and efficient ways to handle XML data compared to the deprecated functions EXTRACT and EXTRACTVALUE.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-25974-_uuwgsatbdfly\"><\/a>Recommended Oracle Approaches:<\/h3>\n\n\n\n<p>I have written a separate blog post about parsing and <a href=\"https:\/\/sonra.io\/load-convert-xml-data-to-oracle-table\/\" target=\"_blank\" rel=\"noreferrer noopener\">converting XML to Oracle tables<\/a> where I discuss the recommended Oracle way of working with XML in detail.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-25974-_2ds769kcav5l\"><\/a>XMLTable<\/h4>\n\n\n\n<p><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/XMLTABLE.html#GUID-C4A32C58-33E5-4CF1-A1FE-039550D3ECFA\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>XMLTable<\/strong><\/a> is an SQL\/XML function that allows for querying XML data using SQL syntax. It converts XML data into a relational format, making it easier to query and manipulate.<\/p>\n\n\n\n<p><strong>Example code<\/strong>: &#8211; It retrieve the names and ages of all students, along with their class name<\/p>\n\n\n<pre class=\"lang:default decode:true \">SELECT c.class_name, s.student_name, s.student_age\nFROM xml_table,\n     XMLTABLE(\n       XMLNAMESPACES(DEFAULT 'http:\/\/teresian.com\/school'),\n       '\/school\/classes\/class'\n       PASSING xml_data\n       COLUMNS\n         class_name VARCHAR2(100) PATH 'name',\n         students XMLTYPE PATH 'students'\n     ) c,\n     XMLTABLE(\n       XMLNAMESPACES(DEFAULT 'http:\/\/teresian.com\/school'),\n       '\/students\/student'\n       PASSING c.students\n       COLUMNS\n         student_name VARCHAR2(100) PATH 'name',\n         student_age NUMBER PATH 'age'\n     ) s\nWHERE xml_table.id = 1;<\/pre>\n\n\n<p><strong>Output<\/strong>:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"610\" height=\"120\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class_name-advanced-mathematics-and.png\" alt=\"Table showing CLASS_NAME Advanced Mathematics and World History with student names and ages\" class=\"wp-image-25985\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class_name-advanced-mathematics-and.png 610w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-class_name-advanced-mathematics-and-300x59.png 300w\" sizes=\"(max-width: 610px) 100vw, 610px\" \/><\/span><\/figure>\n\n\n\n<p>For a more detailed explanation of XMLTable refer to my other blog post where I cover even more <a href=\"https:\/\/sonra.io\/load-convert-xml-data-to-oracle-table\/#examples-of-using-xmltable-to-query-oracle-xml\" target=\"_blank\" rel=\"noreferrer noopener\">XMLTable examples<\/a>.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-25974-_rfz7yfz3rh2u\"><\/a>XMLQUERY<\/h4>\n\n\n\n<p><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/XMLQUERY.html#GUID-9E8D3220-2CF5-4C63-BDC2-0526D57B9CDB\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>XMLQUERY<\/strong><\/a> embeds XQuery expressions in SQL statements, replacing the deprecated EXTRACT function and allowing querying of XML data with results returned as XML.<\/p>\n\n\n\n<p>Example Code:<\/p>\n\n\n<pre class=\"lang:default decode:true\">SELECT XMLQUERY(\n 'declare default element namespace \"http:\/\/teresian.com\/school\";\n  for $s in \/school\/classes\/class\/students\/student\n  return &lt;student xmlns=\"http:\/\/teresian.com\/school\"&gt;{$s\/name\/text()}&lt;\/student&gt;'\n PASSING xml_data\n RETURNING CONTENT\n) AS student_names\nFROM xml_table\nWHERE id = 1;<\/pre>\n\n\n<p>Output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"249\" height=\"67\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-cell-showing-student_names-with-the-value-xm.png\" alt=\"Table cell showing STUDENT_NAMES with the value XML\" class=\"wp-image-25986\"\/><\/span><\/figure>\n\n\n\n<p>Output Value:<\/p>\n\n\n<pre class=\"lang:default decode:true \">&lt;student xmlns=\"http:\/\/teresian.com\/school\"&gt;Rick Grimes&lt;\/student&gt;&lt;student xmlns=\"http:\/\/teresian.com\/school\"&gt;Michonne Hawthorne&lt;\/student&gt;&lt;student xmlns=\"http:\/\/teresian.com\/school\"&gt;Daryl Dixon&lt;\/student&gt;<\/pre>\n<\/p>\n\n\n<p><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/XMLCAST.html#GUID-06563B93-1247-4F0C-B6BE-42DB3B1DB069\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>XMLCAST<\/strong><\/a> used with XMLQUERY casts XML data to SQL data types such as VARCHAR2.<\/p>\n\n\n\n<p><strong>Example code<\/strong>: It extract the age of a specific student, such as &#8220;Rick Grimes&#8221; and casts to VARCHAR2(100)<\/p>\n\n\n<pre class=\"lang:default decode:true \">SELECT XMLCAST(\n XMLQUERY('declare default element namespace \"http:\/\/teresian.com\/school\";\n           (\/school\/classes\/class)[1]\/name\/text()'\n   PASSING xml_data\n   RETURNING CONTENT\n ) AS VARCHAR2(100)\n) AS first_class_name\nFROM xml_table\nWHERE id = 1;<\/pre>\n\n\n<p><strong>Output:<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"262\" height=\"63\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/table-showing-first_class_name-advanced-mathematic.png\" alt=\"Table showing FIRST_CLASS_NAME Advanced Mathematics\" class=\"wp-image-25987\"\/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-25974-_v84x6fj53o\"><\/a>XML converters<\/h3>\n\n\n\n<p>For complex XML conversion tasks, using an XML converter tool such as <a href=\"https:\/\/sonra.io\/flexter-product-page\/\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>Flexter<\/strong><\/a> can be a smart choice over manual coding with Oracle&#8217;s native features.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/sonra.io\/flexter-product-page\/\" target=\"_blank\" rel=\"noreferrer noopener\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1024\" height=\"538\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/02\/woman-in-a-suit-pointing-with-data-graphics-in-a-purple-background-1024x538.png\" alt=\"\" class=\"wp-image-23589\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/02\/woman-in-a-suit-pointing-with-data-graphics-in-a-purple-background-1024x538.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/02\/woman-in-a-suit-pointing-with-data-graphics-in-a-purple-background-300x158.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/02\/woman-in-a-suit-pointing-with-data-graphics-in-a-purple-background-768x403.png 768w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/02\/woman-in-a-suit-pointing-with-data-graphics-in-a-purple-background-1536x806.png 1536w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/02\/woman-in-a-suit-pointing-with-data-graphics-in-a-purple-background.png 1600w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/span><\/a><\/figure>\n\n\n\n<p>XML conversion tools save you time and effort by automating the process, reducing project risks, and speeding up the conversion. This means you can make your data available to decision-makers much faster.<\/p>\n\n\n\n<p>Plus, they handle large data sets more efficiently, making the process smoother and easier to scale as your needs grow. Overall, <a href=\"https:\/\/sonra.io\/xml-converters-by-use-case-bidirectional\/\">an XML converter<\/a> simplifies your project and gets results quicker.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/sonra.io\/flexter-product-page\/\" target=\"_blank\" rel=\"noreferrer noopener\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1024\" height=\"538\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/09\/XML-data-conversion-for-efficient-advanced-analysis-and-insights.png\" alt=\"\" class=\"wp-image-25216\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/09\/XML-data-conversion-for-efficient-advanced-analysis-and-insights.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/09\/XML-data-conversion-for-efficient-advanced-analysis-and-insights-300x158.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/09\/XML-data-conversion-for-efficient-advanced-analysis-and-insights-768x404.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/span><\/a><\/figure>\n\n\n\n<p>Sonra\u2019s Flexter automates converting complex XML and JSON into easy-to-use formats. It works on-premise or in the cloud (AWS, GCP, Azure) and supports various databases like Oracle, Snowflake, BigQuery, and more.<\/p>\n\n\n\n<p>Flexter handles everything automatically, including <a href=\"https:\/\/sonra.io\/snowflake-data-lineage-guide\/\">data lineage<\/a> and models, optimizing the data for clarity. It can be set up quickly, helping you go live in just hours or days instead of weeks.<\/p>\n\n\n\n<p><br><a href=\"https:\/\/sonra.io\/contact-sonra\/\" target=\"_blank\" rel=\"noreferrer noopener\">Talk to one of our XML conversion experts<\/a> to discuss your XML conversion use case and get personalised advice.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-25974-_65t32ukf24iy\"><\/a>Further reading<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><a href=\"https:\/\/sonra.io\/data-sheet\/\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>Flexter<\/strong><\/a><\/h3>\n\n\n\n<p><a href=\"https:\/\/sonra.io\/flexter-product-page\/\" target=\"_blank\" rel=\"noreferrer noopener\">Flexter page<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/sonra.io\/data-sheet\/\" target=\"_blank\" rel=\"noreferrer noopener\">Flexter data sheet<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/sonra.io\/xml-to-csv-converter\/\" target=\"_blank\" rel=\"noreferrer noopener\">XML Conversion<\/a><\/p>\n\n\n\n<p><strong>EXTRACT &amp; EXTRACTVALUE<\/strong><\/p>\n\n\n\n<p><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/EXTRACT-XML.html\" target=\"_blank\" rel=\"noreferrer noopener\">EXTRACT (XML)<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/EXTRACTVALUE.html\" target=\"_blank\" rel=\"noreferrer noopener\">EXTRACTVALUE<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/docs.oracle.com\/javadb\/10.10.1.2\/ref\/rrefclob.html\" target=\"_blank\" rel=\"noreferrer noopener\">CLOB data type<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/docs.oracle.com\/cd\/B10500_01\/appdev.920\/a96616\/arxml24.htm#1011195\" target=\"_blank\" rel=\"noreferrer noopener\">getStringVal()<\/a><\/p>\n\n\n\n<p><strong>XPath &amp; namespaces<\/strong><\/p>\n\n\n\n<p><a href=\"https:\/\/www.geeksforgeeks.org\/introduction-to-xpath\" target=\"_blank\" rel=\"noreferrer noopener\">Introduction to XPath<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.w3schools.com\/xml\/xpath_intro.asp\" target=\"_blank\" rel=\"noreferrer noopener\">XPath Tutorial<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.geeksforgeeks.org\/xml-namespaces\" target=\"_blank\" rel=\"noreferrer noopener\">XML Namespaces<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.w3schools.com\/xml\/xml_namespaces.asp\" target=\"_blank\" rel=\"noreferrer noopener\">XML Namespaces<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.geeksforgeeks.org\/difference-between-relative-and-absolute-xpath-in-selenium\" target=\"_blank\" rel=\"noreferrer noopener\">Difference between Relative and Absolute XPath in Selenium<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>When you need to extract and\/or query an Oracle database for XML data, there are two SQL functions that come to mind, \u2018EXTRACT\u2019 and \u2018EXTRACTVALUE\u2019. I\u2019m going to delve into these two functions and provide some examples. But first, a note of caution: Both EXTRACT and EXTRACTVALUE serve similar purposes, but they each have their &#8230;<\/p>\n","protected":false},"author":29,"featured_media":26069,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[391,6],"tags":[],"class_list":["post-25974","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","category-xml"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Oracle EXTRACT &amp; EXTRACTVALUE for XML (Guide &amp; Examples)<\/title>\n<meta name=\"description\" content=\"Learn Oracle SQL functions EXTRACT &amp; EXTRACTVALUE for XML extraction with examples. A complete guide for XML querying &amp; data retrieval.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle EXTRACT &amp; EXTRACTVALUE for XML (Guide &amp; Examples)\" \/>\n<meta property=\"og:description\" content=\"Learn Oracle SQL functions EXTRACT &amp; EXTRACTVALUE for XML extraction with examples. A complete guide for XML querying &amp; data retrieval.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/\" \/>\n<meta property=\"og:site_name\" content=\"Sonra\" \/>\n<meta property=\"article:published_time\" content=\"2024-10-21T22:58:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-12-12T16:35:58+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Oracle-EXTRACT-EXTRACTVALUE-for-XML-Guide-Examples.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"675\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Maciek\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Maciek\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"14 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sonra.io\\\/oracle-sql-xml-extract-extractvalue\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/oracle-sql-xml-extract-extractvalue\\\/\"},\"author\":{\"name\":\"Maciek\",\"@id\":\"https:\\\/\\\/sonra.io\\\/#\\\/schema\\\/person\\\/f6961e781666bffd0142c5ccc300f219\"},\"headline\":\"Oracle EXTRACT &amp; EXTRACTVALUE for XML (Guide &amp; Examples)\",\"datePublished\":\"2024-10-21T22:58:47+00:00\",\"dateModified\":\"2024-12-12T16:35:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/oracle-sql-xml-extract-extractvalue\\\/\"},\"wordCount\":2552,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/oracle-sql-xml-extract-extractvalue\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sonra.io\\\/wp-content\\\/uploads\\\/2024\\\/10\\\/Oracle-EXTRACT-EXTRACTVALUE-for-XML-Guide-Examples.jpg\",\"articleSection\":[\"SQL\",\"XML\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sonra.io\\\/oracle-sql-xml-extract-extractvalue\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sonra.io\\\/oracle-sql-xml-extract-extractvalue\\\/\",\"url\":\"https:\\\/\\\/sonra.io\\\/oracle-sql-xml-extract-extractvalue\\\/\",\"name\":\"Oracle EXTRACT &amp; EXTRACTVALUE for XML (Guide &amp; Examples)\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/oracle-sql-xml-extract-extractvalue\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/oracle-sql-xml-extract-extractvalue\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sonra.io\\\/wp-content\\\/uploads\\\/2024\\\/10\\\/Oracle-EXTRACT-EXTRACTVALUE-for-XML-Guide-Examples.jpg\",\"datePublished\":\"2024-10-21T22:58:47+00:00\",\"dateModified\":\"2024-12-12T16:35:58+00:00\",\"description\":\"Learn Oracle SQL functions EXTRACT & EXTRACTVALUE for XML extraction with examples. A complete guide for XML querying & data retrieval.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/oracle-sql-xml-extract-extractvalue\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sonra.io\\\/oracle-sql-xml-extract-extractvalue\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sonra.io\\\/oracle-sql-xml-extract-extractvalue\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sonra.io\\\/wp-content\\\/uploads\\\/2024\\\/10\\\/Oracle-EXTRACT-EXTRACTVALUE-for-XML-Guide-Examples.jpg\",\"contentUrl\":\"https:\\\/\\\/sonra.io\\\/wp-content\\\/uploads\\\/2024\\\/10\\\/Oracle-EXTRACT-EXTRACTVALUE-for-XML-Guide-Examples.jpg\",\"width\":1200,\"height\":675},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sonra.io\\\/oracle-sql-xml-extract-extractvalue\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sonra.io\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"XML\",\"item\":\"https:\\\/\\\/sonra.io\\\/category\\\/xml\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Oracle EXTRACT &amp; EXTRACTVALUE for XML (Guide &amp; Examples)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sonra.io\\\/#website\",\"url\":\"https:\\\/\\\/sonra.io\\\/\",\"name\":\"Sonra\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sonra.io\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/sonra.io\\\/#organization\",\"name\":\"Sonra\",\"alternateName\":\"Sonra.io\",\"url\":\"https:\\\/\\\/sonra.io\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sonra.io\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/sonra.io\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/sonra-logo-circle.png\",\"contentUrl\":\"https:\\\/\\\/sonra.io\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/sonra-logo-circle.png\",\"width\":600,\"height\":600,\"caption\":\"Sonra\"},\"image\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sonra.io\\\/#\\\/schema\\\/person\\\/f6961e781666bffd0142c5ccc300f219\",\"name\":\"Maciek\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f1f4c86b9824a4e747832130d9194894903c6c4d8171ae528624afefcabea1b1?s=96&d=https%3A%2F%2Fsonra.io%2Fwp-content%2Fuploads%2F2023%2F04%2FScreenshot_15-removebg-preview.png&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f1f4c86b9824a4e747832130d9194894903c6c4d8171ae528624afefcabea1b1?s=96&d=https%3A%2F%2Fsonra.io%2Fwp-content%2Fuploads%2F2023%2F04%2FScreenshot_15-removebg-preview.png&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f1f4c86b9824a4e747832130d9194894903c6c4d8171ae528624afefcabea1b1?s=96&d=https%3A%2F%2Fsonra.io%2Fwp-content%2Fuploads%2F2023%2F04%2FScreenshot_15-removebg-preview.png&r=g\",\"caption\":\"Maciek\"},\"url\":\"https:\\\/\\\/sonra.io\\\/author\\\/maciek\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Oracle EXTRACT &amp; EXTRACTVALUE for XML (Guide &amp; Examples)","description":"Learn Oracle SQL functions EXTRACT & EXTRACTVALUE for XML extraction with examples. A complete guide for XML querying & data retrieval.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/","og_locale":"en_US","og_type":"article","og_title":"Oracle EXTRACT &amp; EXTRACTVALUE for XML (Guide &amp; Examples)","og_description":"Learn Oracle SQL functions EXTRACT & EXTRACTVALUE for XML extraction with examples. A complete guide for XML querying & data retrieval.","og_url":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/","og_site_name":"Sonra","article_published_time":"2024-10-21T22:58:47+00:00","article_modified_time":"2024-12-12T16:35:58+00:00","og_image":[{"width":1200,"height":675,"url":"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Oracle-EXTRACT-EXTRACTVALUE-for-XML-Guide-Examples.jpg","type":"image\/jpeg"}],"author":"Maciek","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Maciek","Est. reading time":"14 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/#article","isPartOf":{"@id":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/"},"author":{"name":"Maciek","@id":"https:\/\/sonra.io\/#\/schema\/person\/f6961e781666bffd0142c5ccc300f219"},"headline":"Oracle EXTRACT &amp; EXTRACTVALUE for XML (Guide &amp; Examples)","datePublished":"2024-10-21T22:58:47+00:00","dateModified":"2024-12-12T16:35:58+00:00","mainEntityOfPage":{"@id":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/"},"wordCount":2552,"commentCount":0,"publisher":{"@id":"https:\/\/sonra.io\/#organization"},"image":{"@id":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/#primaryimage"},"thumbnailUrl":"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Oracle-EXTRACT-EXTRACTVALUE-for-XML-Guide-Examples.jpg","articleSection":["SQL","XML"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/","url":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/","name":"Oracle EXTRACT &amp; EXTRACTVALUE for XML (Guide &amp; Examples)","isPartOf":{"@id":"https:\/\/sonra.io\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/#primaryimage"},"image":{"@id":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/#primaryimage"},"thumbnailUrl":"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Oracle-EXTRACT-EXTRACTVALUE-for-XML-Guide-Examples.jpg","datePublished":"2024-10-21T22:58:47+00:00","dateModified":"2024-12-12T16:35:58+00:00","description":"Learn Oracle SQL functions EXTRACT & EXTRACTVALUE for XML extraction with examples. A complete guide for XML querying & data retrieval.","breadcrumb":{"@id":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/#primaryimage","url":"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Oracle-EXTRACT-EXTRACTVALUE-for-XML-Guide-Examples.jpg","contentUrl":"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Oracle-EXTRACT-EXTRACTVALUE-for-XML-Guide-Examples.jpg","width":1200,"height":675},{"@type":"BreadcrumbList","@id":"https:\/\/sonra.io\/oracle-sql-xml-extract-extractvalue\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sonra.io\/"},{"@type":"ListItem","position":2,"name":"XML","item":"https:\/\/sonra.io\/category\/xml\/"},{"@type":"ListItem","position":3,"name":"Oracle EXTRACT &amp; EXTRACTVALUE for XML (Guide &amp; Examples)"}]},{"@type":"WebSite","@id":"https:\/\/sonra.io\/#website","url":"https:\/\/sonra.io\/","name":"Sonra","description":"","publisher":{"@id":"https:\/\/sonra.io\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sonra.io\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/sonra.io\/#organization","name":"Sonra","alternateName":"Sonra.io","url":"https:\/\/sonra.io\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sonra.io\/#\/schema\/logo\/image\/","url":"https:\/\/sonra.io\/wp-content\/uploads\/2015\/02\/sonra-logo-circle.png","contentUrl":"https:\/\/sonra.io\/wp-content\/uploads\/2015\/02\/sonra-logo-circle.png","width":600,"height":600,"caption":"Sonra"},"image":{"@id":"https:\/\/sonra.io\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/sonra.io\/#\/schema\/person\/f6961e781666bffd0142c5ccc300f219","name":"Maciek","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f1f4c86b9824a4e747832130d9194894903c6c4d8171ae528624afefcabea1b1?s=96&d=https%3A%2F%2Fsonra.io%2Fwp-content%2Fuploads%2F2023%2F04%2FScreenshot_15-removebg-preview.png&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f1f4c86b9824a4e747832130d9194894903c6c4d8171ae528624afefcabea1b1?s=96&d=https%3A%2F%2Fsonra.io%2Fwp-content%2Fuploads%2F2023%2F04%2FScreenshot_15-removebg-preview.png&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f1f4c86b9824a4e747832130d9194894903c6c4d8171ae528624afefcabea1b1?s=96&d=https%3A%2F%2Fsonra.io%2Fwp-content%2Fuploads%2F2023%2F04%2FScreenshot_15-removebg-preview.png&r=g","caption":"Maciek"},"url":"https:\/\/sonra.io\/author\/maciek\/"}]}},"_links":{"self":[{"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/posts\/25974","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/comments?post=25974"}],"version-history":[{"count":56,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/posts\/25974\/revisions"}],"predecessor-version":[{"id":27495,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/posts\/25974\/revisions\/27495"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/media\/26069"}],"wp:attachment":[{"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/media?parent=25974"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/categories?post=25974"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/tags?post=25974"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}