[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-08
4
Medium Priority
?
502 Views
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="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;
0
Comment
Question by:talahi
  • 2
4 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40249343
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40249349
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
 

Author Closing Comment

by:talahi
ID: 40249497
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40249549
>>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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month20 days, 4 hours left to enroll

872 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