{"id":8085,"date":"2019-04-18T14:35:41","date_gmt":"2019-04-18T14:35:41","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/parse-xml-in-pl-sql\/"},"modified":"2026-11-03T19:29:16","modified_gmt":"2026-11-03T19:29:16","slug":"parse-xml-in-pl-sql","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/parse-xml-in-pl-sql\/","title":{"rendered":"Parse XML in PL\/SQL"},"content":{"rendered":"<p><strong>Introduction<\/strong><\/p>\n<p>This blog explores a few methods for handling XML data in Oracle&nbsp;\u00a9&nbsp;PL\/SQL. Learn more about our <a href=\"https:\/\/www.rackspace.com\/data\/databases\">database services<\/a>.<\/p>\n<p>If you want to convert XML data from an XML file into Oracle PL\/SQL rows and columns,&nbsp;the following options are available:<\/p>\n<ul>\n<li>Load the XML file into an XML table and then parse it.<\/li>\n<li>Directly parse the XML file without loading into an XML table.<\/li>\n<\/ul>\n<p>To load the XML data into an Oracle table, use options such as <em>SQLLOADER<\/em>,&nbsp;<em>utl_file<\/em>, or <em>XML CLOB<\/em>. After you load the data into a table, you need to&nbsp;extract values from each XML tag. To extract the XML data, use the built-in&nbsp;functions provided by Oracle, such as <em>XMLELEMENT<\/em>, <em>XMLAGG<\/em>, <em>XMLTABLE<\/em>,&nbsp;<em>XMLSEQUENCE<\/em>, and <em>EXTRACTVALUE.<\/em><\/p>\n<p>\nThe main built-in function used is <em>EXTRACT, <\/em>which is shown in the following image:.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26596-body-Picture1_8.png\" alt=\"xml pic 1\" \/><\/p>\n<p><a href=\"https:\/\/docs.oracle.com\/cd\/B19306_01\/server.102\/b14200\/img\/extract_xml.gif\"><em>Image Source<\/em><\/a>&nbsp;<\/p>\n<p><strong>Example file<\/strong><\/p>\n<p>To explore these options in detail, the example in this post uses the&nbsp;file, <strong>Test.xml<\/strong>. To access the file in the database, use the <strong>DBA<\/strong>&nbsp;directory,&nbsp;which is defined in Oracle. The examples use <strong>XX\\_UTL\\_DIR<\/strong>&nbsp;as the reference&nbsp;directory. You can use your own directory of choice instead.<\/p>\n<p><strong>Test.xml<\/strong>&nbsp;has the following contents::<\/p>\n<p>&nbsp;&lt;?xml version = &#8216;1.0&#8217; encoding = &#8216;UTF-8&#8217;?&gt;<br \/>\n&nbsp; &nbsp; &lt;UANotification xmlns=&#8221;https:\/\/www.test.com\/UANotification&#8221;&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &lt;NotificationHeader&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &lt;Property name=&#8221;ErrorMessage&#8221; value=&#8221;User Data Invalid&#8221;\/&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;Property name=&#8221;SPSDocumentKey&#8221; value=&#8221;11111111111&#8243;\/&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;Property name=&#8221;AppKey&#8221; value=&#8221;22222222&#8243;\/&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;Property name=&#8221;FileName&#8221; value=&#8221;SH201701181418.61W&#8221;\/&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;Property name=&#8221;SenderName&#8221; value=&#8221;Test&#8221;\/&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;Property name=&#8221;ReceiverName&#8221; value=&#8221;Integrated Supply Network&#8221;\/&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;Property name=&#8221;DocumentType&#8221; value=&#8221;856&#8243;\/&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;Property name=&#8221;SourceDataType&#8221; value=&#8221;XML&#8221;\/&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;Property name=&#8221;DestinationDataType&#8221; value=&#8221;FEDS&#8221;\/&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;Property name=&#8221;XtencilNet&#8221; value=&#8221;shFedsWrite&#8221;\/&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;Property name=&#8221;PreviousMaps&#8221; value=&#8221;shFedsWrite]&#8221;\/&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &lt;\/NotificationHeader&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &lt;FINotification xmlns=&#8221;https:\/\/www.test.com\/fileIntegration&#8221;&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;ServiceResult&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;DataError&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;Message&gt;Invalid data test 1&lt;\/Message&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;\/DataError&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;DataError&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;Message&gt;Invalid data test 2&lt;\/Message&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;\/DataError&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;\/ServiceResult&gt;<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &lt;\/FINotification&gt;<br \/>\n&nbsp; &nbsp; &lt;\/UANotification&gt;<\/p>\n<p><strong>First approach: &nbsp;Load the XML file into an XML table and then parse it<\/strong><\/p>\n<p>First, create a table in Oracle that includes a column with data type <strong>XMLTYPE<\/strong>.<\/p>\n<p>For example, use the following code to create the table:<\/p>\n<p><em>&nbsp; &nbsp; CREATE TABLE xml_tab (<br \/>\n&nbsp; &nbsp; &nbsp; File_name &nbsp;varchar2(100),<br \/>\n&nbsp; &nbsp; &nbsp; xml_data &nbsp;XMLTYPE<br \/>\n&nbsp; &nbsp; );<\/em><\/p>\n<p>Next, insert data from <strong>Test.xml<\/strong>&nbsp;into <strong>xml.tab<\/strong>&nbsp;by using the following&nbsp;command:.<\/p>\n<p><em>&nbsp; &nbsp; INSERT INTO xml_tab<br \/>\n&nbsp; &nbsp; VALUES ( \u2018Test.xml\u2019,<br \/>\n&nbsp; &nbsp; XMLTYPE (BFILENAME (&#8216;XX_UTL_DIR&#8217;, \u2018Test.xml\u2019),<br \/>\n&nbsp; &nbsp; NLS_CHARSET_ID (&#8216;AL32UTF8&#8217;)<br \/>\n&nbsp; &nbsp; ));<\/em><br \/>\nThe preceding INSERT statement inserts the data of file <strong>Test.xml&nbsp;<\/strong>into the field <em>xml_data<\/em>&nbsp;of the table <strong>xml\\_tab<\/strong>. After the INSERT completes, the XML&nbsp;data is available in table <strong>xml\\_tab<\/strong>.<\/p>\n<p>To read the data in a SELECT query, use&nbsp;the SELECT statements described next.<\/p>\n<p>The preceding INSERT statement inserts the data of file <strong>Test.xml&nbsp;<\/strong>into the field <em>xml_data<\/em>&nbsp;of the table <strong>xml\\_tab<\/strong>. After the INSERT completes, the XML&nbsp;data is available in table <strong>xml\\_tab.<\/strong>.<\/p>\n<p>To read the data in a SELECT query, use&nbsp;the SELECT statements described next.<\/p>\n<p><em>&nbsp;SELECT EXTRACT (VALUE (a1),<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#8216;\/DataError\/Message\/text()&#8217;,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#8216;xmlns=&#8221;https:\/\/www.test.com\/fileIntegration&#8217;)<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; msg<br \/>\n&nbsp; &nbsp; &nbsp;FROM xml_tab,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp;TABLE (<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; XMLSEQUENCE (<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EXTRACT (<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xml_data,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#8216;\/UANotification\/ns2:FINotification\/ns2:ServiceResult\/ns2:DataError&#8217;,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#8216;xmlns=&#8221;https:\/\/www.test.com\/UANotification&#8221; xmlns:ns2=&#8221;https:\/\/www.test.com\/fileIntegration&#8221;&#8216;))) a1<br \/>\n&nbsp; &nbsp; WHERE file_name = &#8216;Test.xml&#8217;;<\/em><br \/>\nTo read <em>property name<\/em>&nbsp;and its value, use the following SQL command:<\/p>\n<p><em>&nbsp;SELECT EXTRACTVALUE (VALUE (a1),<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&#8216;\/Property\/@name&#8217;,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&#8216;xmlns=&#8221;https:\/\/www.test.com\/UANotification&#8217;)<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; attribute,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp;EXTRACTVALUE (VALUE (a1),<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&#8216;\/Property\/@value&#8217;,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&#8216;xmlns=&#8221;https:\/\/www.test.com\/UANotification&#8217;)<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUE<br \/>\n&nbsp; &nbsp; &nbsp;FROM xml_tab,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp;TABLE (<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; XMLSEQUENCE (<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EXTRACT (<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xml_data,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#8216;\/UANotification\/NotificationHeader\/Property&#8217;,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#8216;xmlns=&#8221;https:\/\/www.test.com\/UANotification&#8221; xmlns:ns2=&#8221;https:\/\/www.test.com\/fileIntegration&#8221;&#8216;))) a1<br \/>\n&nbsp; &nbsp; &nbsp;WHERE file_name = &#8216;Test.xml&#8217;;<\/em><\/p>\n<p><strong>Second approach: Directly parse the XML file without loading it into an XML table<\/strong><\/p>\n<p>If you want to directly parse <strong>Test.xml<\/strong>&nbsp;without loading it into the Oracle&nbsp;table, then you might use the following SELECT statement:<\/p>\n<p>&nbsp;<em>SELECT EXTRACTvalue (VALUE (a1),<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#8216;\/Property\/@name&#8217;,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#8216;xmlns=&#8221;https:\/\/www.test.com\/UANotification&#8217;) attribute,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EXTRACTvalue (VALUE (a1),<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#8216;\/Property\/@value&#8217;,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#8216;xmlns=&#8221;https:\/\/www.test.com\/UANotification&#8217;) value<br \/>\n&nbsp; &nbsp; &nbsp;FROM<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp;TABLE (<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; XMLSEQUENCE (<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EXTRACT (<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xmltype(BFILENAME (&#8216;XX_UTL_DIR&#8217;, &#8216;Test.xml&#8217;),NLS_CHARSET_ID (&#8216;AL32UTF8&#8217;)),<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#8216;\/UANotification\/NotificationHeader\/Property&#8217;,<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#8216;xmlns=&#8221;https:\/\/www.test.com\/UANotification&#8221; xmlns:ns2=&#8221;https:\/\/www.test.com\/fileIntegration&#8221;&#8216;))) a1<\/em><\/p>\n<p><strong>Wrap up<\/strong><\/p>\n<p>Both of the methods to parse XML data described in this post give you the same final<br \/>\noutput. The first approach is a three-step process, which requires the following<br \/>\npieces of code:<\/p>\n<p>1. &nbsp;Create the Oracle table.<br \/>\n2. &nbsp;Insert the data of the XML file into the created table.<br \/>\n3. &nbsp;Write a SELECT statement to extract values from the table.<\/p>\n<p>The second option is a single-step process where you write a SELECT statement<br \/>\nand get the desired result.<\/p>\n<p><strong>Conclusion<\/strong><\/p>\n<p>Either option works, but if you need to store the XML file in Oracle for&nbsp;future reference, then you should use the first approach because the data persists&nbsp;in the table for future reference and you can access it any time.<\/p>\n<p>By choosing the second approach, you can directly parse the data. However, with&nbsp;this option, the original XML file data can\u2019t be accessed in the future because this&nbsp;option never stores the content of XML file in Oracle.<br \/>\n&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Parse XML in PL\/SQL<\/p>\n","protected":false},"author":2,"featured_media":8087,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26596","content_type":"blog","created_by":"10130","authored_on":"2024-02-09T15:35:41+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Rackspace Technology Staff","seo_description":"Learn different methodologies for handling XML data using Oracle PL\/SQL, a procedural language extension for SQL.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26596-insights_image-oracle-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Oracle","product":"","publisheddate":"2019-04-18T14:35:41","rxt_solutions":"Applications, Application Management & Operations","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Parsing XML in PL\/SQL - A Step-by-Step Tutorial | Rackspace Technology","field_topics":"","field_slug":"parse-xml-in-pl-sql","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-8085","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/8085","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=8085"}],"version-history":[{"count":3,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/8085\/revisions"}],"predecessor-version":[{"id":9868,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/8085\/revisions\/9868"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/8087"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=8085"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=8085"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=8085"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}