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

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="http://xxx.xxx.xxx/xxx/xxx" xmlns:nxce="http://xxx.xxx.xxx/xxx/xxx">
 <GeospatialData>
 <Navaid>
  <NavaidID>FCP</NavaidID>
  <ArtccID>RRR</ArtccID>
  <NavaidType>ZZZ</NavaidType>
 <GeoPosition>
 <nxce:latitude>
  <nxce:latitudeRadians>.8585565537</nxce:latitudeRadians>
  </nxce:latitude>
 <nxce:longitude>
  <nxce:longitudeRadians>-2.1505947044</nxce:longitudeRadians>
  </nxce:longitude>
  </GeoPosition>
  <State>BC</State>
  <MagVar>19</MagVar>
  <MonitoredInd>0</MonitoredInd>
  <DistributedInd>0</DistributedInd>
 <FpamReference>
  <BaselineSectorName>CCCRT11</BaselineSectorName>
  <FpaNumber>0000</FpaNumber>
  <ModuleID>00</ModuleID>
  </FpamReference>
 <FpamReference>
  <BaselineSectorName>CCCCT13</BaselineSectorName>
  <FpaNumber>0000</FpaNumber>
  <ModuleID>00</ModuleID>
  </FpamReference>
 <FpamReference>
  <BaselineSectorName>CXXRT14</BaselineSectorName>
  <FpaNumber>0000</FpaNumber>
  <ModuleID>00</ModuleID>
  </FpamReference>
  </Navaid>
</GeospatialData>
<GeospatialData>
 <Navaid>
  <NavaidID>ABC</NavaidID>
  <ArtccID>DEH</ArtccID>
  <NavaidType>QZM</NavaidType>
</TfmsData>')
);

commit;


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

x;
 

select XMLQuery('/TfmsData/GeospatialData/Navaid/NavaidID/' PASSING xml_data RETURNING CONTENT)
from example_xml_table;
talahiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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,
	xmltable(
		 xmlnamespaces(default 'http://xxx.xxx.xxx/xxx/xxx',
			'http://xxx.xxx.xxx/xxx/xxx' as "nxce"
		),
	'/TfmsData'
	passing xml_data
	columns
		Navaid xmltype path '/TfmsData/GeospatialData/Navaid'
	) xmltab
	,xmltable(
		 xmlnamespaces(default 'http://xxx.xxx.xxx/xxx/xxx',
			'http://xxx.xxx.xxx/xxx/xxx' as "nxce"
		),
		'/Navaid'
		passing xmltab.Navaid
		columns NavaidID varchar2(10) path '/Navaid/NavaidID'
	)
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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=*****')
0
talahiAuthor Commented:
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?
0
slightwv (䄆 Netminder) Commented:
>>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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.