Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Load Large XML doc into Oracle using Bfilename
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_DI
nls_charset_id('AL32UTF8')
commit;
to check data,
select XMLQuery('//Navaid/NavaidI
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</Nav
Using,
CREATE XML_TABLE
(
XML_DATA XMLTYPE
);
INSERT INTO xml_table
VALUES (XMLType(bfilename('XML_DI
nls_charset_id('AL32UTF8')
commit;
to check data,
select XMLQuery('//Navaid/NavaidI
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</Nav
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I think the issue is in the select not the load.
What does this return:
select dbms_lob.getlength(x.XML_D
What does this return:
select dbms_lob.getlength(x.XML_D
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.
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.
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/
) 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),'//N
from xml_table,
XMLTABLE('///Navaid/Navaid
PASSING xml_data
);
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/
) 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),'//N
from xml_table,
XMLTABLE('///Navaid/Navaid
PASSING xml_data
);
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.
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
/
XMLQuery that works for navaidid:
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.
select XMLQuery(
'declare default element namespace "http://tfm.faa.gov/tfms/TfmsData"; (: :)
//Navaid/NavaidID/text()'
PASSING XML_DATA RETURNING CONTENT)
from tab1;
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.
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;
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.
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.
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><N
<ArtccID>CZV</ArtccID>
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><N
<ArtccID>CZV</ArtccID>
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
/
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/
passing xml_data
columns
artccID varchar2(50) path '//Navaid/ArtccID'
) xml_data --xmltab
/
select ArtccID
from xml_table,
xmltable(
xmlnamespaces(default 'http://tfm.faa.gov/tfms/TfmsData',
'http://tfm.faa.gov/tfms/NasXCoreElements' as "nxce"
),
'/TfmsData/GeospatialData/
passing xml_data
columns
artccID varchar2(50) path '//Navaid/ArtccID'
) xml_data --xmltab
/
Premium Content
You need an Expert Office subscription to comment.Start Free Trial