Problem SQL Querying of XML data stored in  an Oracle table of XMLType?

Posted on 2014-08-08
Last Modified: 2014-08-08
My testdb is an Oracle 10.2 Win32 db and I'm using schema/user XDB.

I've followed this example below to try and understand how to query xml data.  Each method I try indicated an output has been found 'Row 1 of 1 total rows' but the output on my screen is blank.  Also wondered if it mattered if these URLs (xmlns="http://...") were no longer active links?  

Below is just a sample of data from a 50 MB file I will need to load and search on monthly so I'm also asking if there is an Oracle XML method to load and query this data or if I sound concentrate on writing PLSQL to use regular expression functions to find, grab and save strings of data?  I did load the entire 50MB file using BFILENAME but had the same problem querying data.

create table example_xml_table
xml_data xmltype

insert into example_xml_table values(
XMLtype ('<?xml version="1.0" encoding="UTF-8" ?>
 <TfmsData xmlns="" xmlns:nxce="">


These 2 queries return the entire file as 'wellformed' xml.
select xml_data from example_xml_table;
select x.xml_data.getStringVal() from example_xml_table x;

These 3 queries below return 'row 1 of 1 total rows' but no data is displayed.

select extract(t.xml_data,'/TfmsData/GeospatialData/Navaid/NavaidID/text()').getStringVal()
from example_xml_table t;

select extractValue(x.xml_data, '/TfmsData/GeospatialData/Navaid/NavaidID').getStringVal() from example_xml_table


select XMLQuery('/TfmsData/GeospatialData/Navaid/NavaidID/' PASSING xml_data RETURNING CONTENT)
from example_xml_table;
Question by:talahi
    LVL 76

    Accepted Solution

    extract and extractvalue are deprecated.

    Best to start learning the XMLTABLE syntax.

    To extract the two NavaidID's try this:
    select NavaidID
    from example_xml_table,
    		 xmlnamespaces(default '',
    			'' as "nxce"
    	passing xml_data
    		Navaid xmltype path '/TfmsData/GeospatialData/Navaid'
    	) xmltab
    		 xmlnamespaces(default '',
    			'' as "nxce"
    		passing xmltab.Navaid
    		columns NavaidID varchar2(10) path '/Navaid/NavaidID'

    Open in new window

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    I cannot check right now...
    But I would try 2 things
    Use // instead of / in the beginning  of thexpathh expression
    Use the second parameter of extract function to specify the namespace ('xmnsl=*****')

    Author Closing Comment

    It returned one NavaidID (ZVR) so thanks.  Can you point me in the direction on how to use a WHERE clause with something like this?  Not sure what I was trying to do didn't work or why this needs to be as complex.  I assume 'xmltab' and 'xmltable' are aliases?
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    >>I assume 'xmltab' and 'xmltable' are aliases?

    xmltab is an alias.  xmltable is a function.

    >>Not sure what I was trying to do didn't work or why this needs to be as complex.  

    Likely the namespace issue Guy mentioned above.  EXTRACT allows a second parameter for the namespaces.  I didn't debug that since those functions are going away.

    It really only looks complex.  Once you get used to looking at it, it's really pretty easy.

    The real power comes when you want to extract more than one node value from the XML.

    Using EXTRACT it would have to manipulate the XML once for every EXTRACT call.  Using XMLTABLE, you can just keep adding COLUMNS.

    >>It returned one NavaidID (ZVR) so thanks.

    Your sample XML had two.  If you will only ever have one value, you may not need the nested XMLTABLES.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now