Solved

Load Large XML doc into Oracle using Bfilename

Posted on 2014-11-07
15
323 Views
Last Modified: 2014-11-14
My overall goal is to be able to search for specific data in this xlm document probably using XLMQUERY.  Using Oracle 12c, 64bit, windows 2012r2 server, trying to load a 50meg XLM document and verify data correctly loaded.

Using,

CREATE XML_TABLE
(
  XML_DATA  XMLTYPE
);

INSERT INTO xml_table
  VALUES (XMLType(bfilename('XML_DIR', '50Meg_doc.xml'),
                  nls_charset_id('AL32UTF8')));
commit;

to check data,

select XMLQuery('//Navaid/NavaidID' PASSING XML_DATA  RETURNING CONTENT)
from xml_table;

nothing returns.

Sample of data.

<?xml version="1.0" encoding="UTF-8"?>
<TfmsData xmlns="http://tfm.faa.gov/tfms/TfmsData" xmlns:nxce="http://tfm.faa.gov/tfms/NasXCoreElements">
 <GeospatialData>
<Navaid><NavaidID>ZVR</NavaidID><ArtccID>CZV</ArtccID><NavaidType>NDB</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>...
0
Comment
Question by:talahi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40429177
I think the issue is in the select not the load.

What does this return:

select dbms_lob.getlength(x.XML_DATA.getclobval()) from xml_table x;
0
 

Author Comment

by:talahi
ID: 40429206
returns 74694362
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40429240
Then the XML loaded.

I believe the issue is with the namespaces in the XML.

I've never used XMLQuery so I'm trying to figure out how to use namespaces in it.

I'll post when I figure it out.

While we both look, what types of data will you be extracting?

I typically use XMLTABLE not XMLQUERY.

I have examples with XMLTABLE and multiple namespaces.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:talahi
ID: 40429260
Whatever works, XMLTABLE or XMLQUERY.  I'm trying to find something with an equivalent WHERE clause. Here are some of the unsuccessful attempts I've made following examples.  To start with I'm just trying to do a simple query to understand how the syntax works.

select NavaidID
from 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'
      ) xml_data
      ,xmltable(
             xmlnamespaces(default 'http://xxx.xxx.xxx/xxx/xxx',
                  'http://xxx.xxx.xxx/xxx/xxx' as "nxce"
            ),
            '/Navaid'
            passing xml_data --xmltab.Navaid
            columns NavaidID varchar2(10) path '//Navaid/NavaidID'
      );
 
SELECT xml_data, XMLQuery (
'for $i IN /Navaid
 where $i /NavaidID = "ZVR"
 return $i /State'
PASSING BY VALUE xml_data
RETURNING CONTENT) As output --xmldata
FROM xml_table;

ORA-19114: XPST0003 - error during parsing the XQuery expression:
LPX-00801: XQuery syntax error at 'IN'
1   for $i IN /Navaid
-         ^
19114. 00000 -  "error during parsing the XQuery expression: %s"
*Cause:    An error occurred during the parsing of the XQuery expression.
*Action:   Check the detailed error message for the possible causes.
Error at Line: 32 Column: 6

SELECT xml_data, XMLQuery (
'for $i IN /Navaid
 where $i /NavaidID = "ZVR"
 return $i /State'
PASSING BY VALUE xml_data
RETURNING CONTENT) As output --xmldata
FROM xml_table;

ORA-19114: XPST0003 - error during parsing the XQuery expression:
LPX-00801: XQuery syntax error at 'IN'
1   for $i IN /Navaid
-         ^
19114. 00000 -  "error during parsing the XQuery expression: %s"

select EXTRACTVALUE(VALUE(1),'//Navaid/NavaidID')
from xml_table,
   XMLTABLE('///Navaid/NavaidID'
                PASSING xml_data
               );
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40429317
extract and extractvalue have been deprecated.  Forget it even exists.


Here is an XMLTABLE example with only one NavaidID.

I'm still working on the XMLQuery example.

From your last post, looks like you might have repeating nodes in NavaidID.

If you can update my test case with more XML and expected results, I can modify my example.


drop table tab1 purge;
create table tab1(xml_data xmltype);


insert into tab1 values('
<TfmsData xmlns="http://tfm.faa.gov/tfms/TfmsData" xmlns:nxce="http://tfm.faa.gov/tfms/NasXCoreElements">
  <GeospatialData>
 	<Navaid>
 		<NavaidID>ZVR</NavaidID>
 	</Navaid>
  </GeospatialData>
</TfmsData>
');

commit;

select NavaidID
from tab1,
	xmltable(
		 xmlnamespaces(default 'http://tfm.faa.gov/tfms/TfmsData',
			'http://tfm.faa.gov/tfms/NasXCoreElements' as "nxce"
		),
	'/TfmsData/GeospatialData'
	passing xml_data
	columns
		NavaidID varchar2(50) path '/GeospatialData/Navaid/NavaidID'
	) xmltab
/

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40429328
XMLQuery that works for navaidid:

select XMLQuery(
	'declare default element namespace "http://tfm.faa.gov/tfms/TfmsData"; (: :)
	//Navaid/NavaidID/text()'
	PASSING XML_DATA  RETURNING CONTENT) 
from tab1;

Open in new window


Still not sure how to provide multiple namespaces but I'll figure you'll need them eventually.

If I figure it out, I'll post back.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40429348
XMLQuery for both namespaces:
select XMLQuery(
	'declare default element namespace "http://tfm.faa.gov/tfms/TfmsData"; (: :) declare namespace nxce="http://tfm.faa.gov/tfms/NasXCoreElements"; (: :)
	//Navaid/NavaidID/text()'
	PASSING XML_DATA  RETURNING CONTENT) 
from tab1;

Open in new window

0
 

Author Closing Comment

by:talahi
ID: 40429365
Ok thanks, as before you've helped Hugely.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40429378
Based on what you posted, I would probably go with XMLTable.

Sounds like you want to treat the data as 'normal' relational data.

From the few minutes I was reading about XMLQuery, it returns XML.

If you wanted two values from your XML, not sure XMLQuery would be the path to go down.
0
 

Author Comment

by:talahi
ID: 40429389
The following works to return all values of NavaidID.  The xml data structure is organized below, so if I wanted the ARTCCID value at the same 'level' as NAVAIDID where NAVAIDID = 'ZVR' how would I modify your query below?

select XMLQuery(
      'declare default element namespace "http://tfm.faa.gov/tfms/TfmsData"; (: :) declare namespace nxce="http://tfm.faa.gov/tfms/NasXCoreElements"; (: :)
      //Navaid/NavaidID/text()'
      PASSING XML_DATA  RETURNING CONTENT)
from tab1;

data structure,
<TfmsData xmlns="http://tfm.faa.gov/tfms/TfmsData" xmlns:nxce="http://tfm.faa.gov/tfms/NasXCoreElements">
 <GeospatialData><Navaid><NavaidID>ZVR</NavaidID>
 <ArtccID>CZV</ArtccID>
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40429396
This is why I would use XMLTable over XMLQuery.

drop table tab1 purge;
create table tab1(xml_data xmltype);

insert into tab1 values('
<TfmsData xmlns="http://tfm.faa.gov/tfms/TfmsData" xmlns:nxce="http://tfm.faa.gov/tfms/NasXCoreElements">
  <GeospatialData>
 	<Navaid>
 		<NavaidID>ZVR</NavaidID>
  		<ArtccID>CZV</ArtccID>
 	</Navaid>
  </GeospatialData>
</TfmsData>
');

commit;

select NavaidID,ArtccID
from tab1,
	xmltable(
		 xmlnamespaces(default 'http://tfm.faa.gov/tfms/TfmsData',
			'http://tfm.faa.gov/tfms/NasXCoreElements' as "nxce"
		),
	'/TfmsData/GeospatialData'
	passing xml_data
	columns
		NavaidID	varchar2(50) path '/GeospatialData/Navaid/NavaidID',
		ArtccID		varchar2(50) path '/GeospatialData/Navaid/ArtccID'
	) xmltab
/

Open in new window

0
 

Author Comment

by:talahi
ID: 40429411
Ok lesson learned and thanks again.
0
 

Author Comment

by:talahi
ID: 40432785
Playing around with the xmltable query you provided I was able to determine the 'where clause' I was looking for as follows,

select ArtccID
from xml_table,
      xmltable(
             xmlnamespaces(default 'http://tfm.faa.gov/tfms/TfmsData',
                  'http://tfm.faa.gov/tfms/NasXCoreElements' as "nxce"
            ),
      '/TfmsData/GeospatialData/Navaid[NavaidID="ZWG"]'
      passing xml_data
      columns
            artccID varchar2(50) path '//Navaid/ArtccID'
      ) xml_data --xmltab
/
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40433987
Or just add a where clause.  Since XMLTable returns relational data, it is subject to the same constraints.

select ArtccID
...
where NavaidID = 'ZWG'
0
 

Author Comment

by:talahi
ID: 40443216
No the 'where clause' above did not work after I tried numerous variations.  Finally got some time to get back to this and will probably be posting additional questions about expanding this 'where clause' with an equivalent AND to extract specific data.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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
This video shows how to recover a database from a user managed backup
Suggested Courses

617 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