Home » Developer & Programmer » JDeveloper, Java & XML » Load XML file to Oracle (Oracle 10g R2)
Load XML file to Oracle [message #391639] Thu, 12 March 2009 15:00 Go to next message
sherry_US_OHIO
Messages: 3
Registered: March 2009
Location: US
Junior Member
Here is what i did:
1. create a Object directory xmldir
SQL>create or replace directory xmlfir as '/file_location/file.xml';
2. create xmltype table
SQL>create table xmlfile_tbl of XMLTYPE;
3. insert xmlfile to the table
SQL>insert into xmlfile_tbl
values (XMLTYPE(bfilename('XMLDIR','fle.xml','nls_charset_id('AL32UTF8')));

Then I try to Query it:

SQL>select object_value
from xmlfile_tbl;

Return complete file.xml

But use extractValue() or extract() ALWAYS RETURN NULL
Can anyone help?
Thanks!
Re: Load XML file to Oracle [message #391642 is a reply to message #391639] Thu, 12 March 2009 15:08 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hi,

you'll have to post what you've tried using EXTRACT or EXTRACTVALUE for us to see what you're doing wrong, plus a short example of the XML itself.

Regards
Re: Load XML file to Oracle [message #391643 is a reply to message #391642] Thu, 12 March 2009 15:41 Go to previous messageGo to next message
sherry_US_OHIO
Messages: 3
Registered: March 2009
Location: US
Junior Member
Here is part of file:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<CustomerList>
<customers>
<customer webLocatorName="HOXIE CO." isGoKartDlr="false" isSportBoatDlr="false" isSideBySideDlr="true" isGolfCarDlr="false" isOutboardServiceOnlyDlr="false" isOutboardPackageDlr="false" isOutboardFullLineDlr="false" isIndustrialGeneratorDlr="false" isConsumerGeneratorDlr="true" isServiceGeneratorDlr="true" isWaverunnerDlr="false" isSnowmobileDlr="false" isATVDlr="true" isScooterDlr="false" isMotorcycleDlr="true" isOutdoorSpecialist="false" isSportSpecialist="false" isStarSpecialist="false" isProYamaha="false" createDate="1969-07-01T00:00:00-07:00" isMasterTechnician="false" url="HTTP://WWW.HOXI.COM" email="HOXI@RURALTEL.NET" isDisplayOnWeb="true" phone="675-3201" zipCode="12345" stateCode="KS" city="HOXI" address="933 OAK AVENUE" dealerType="DLR" dealerStatus="A" dealerName="HOXIE CO., INC." dealerNumber="169250">
<productLines>
<productLine status="A" description="Parts" code="1010"/>
<productLine status="A" description="Motorcycle" code="1110"/>

=====================
Here is what I did:

select extract(object_value, '/customers/customer/@webLocatorName')
from xmlfile_tbl;

select extract(object_value, '/customers/customer/productLines/product/@status')
from xmlfile_tbl

Return null;
Re: Load XML file to Oracle [message #391647 is a reply to message #391639] Thu, 12 March 2009 15:56 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hi,

you're missing the /CustomerList from your xpath expression. Note, though, that if you've got multiple customers in a customer list, then you'll have to extract them as a table using XMLSEQUENCE. Have a look at http://www.oratechinfo.co.uk/sqlxml.html#xmlsequence for more info.
Re: Load XML file to Oracle [message #391831 is a reply to message #391647] Fri, 13 March 2009 15:16 Go to previous messageGo to next message
sherry_US_OHIO
Messages: 3
Registered: March 2009
Location: US
Junior Member
Thanks Mchaddr,

I tried:

select extractValue(value(prdl),'/CustomerList/customers/productLines')
from XMLFILE_TBL i,
table(XMLSequence(
extract(i.object_value, '/CustomerList/customers/productLines'))) prdl
where existsNode(i.object_value, '/customerList/customers/productLines/productLine[@status="A"]')=1
/

no row return
Re: Load XML file to Oracle [message #391844 is a reply to message #391639] Fri, 13 March 2009 19:54 Go to previous message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello,

firstly, be very careful that you get your case correct in your xpath, XML / Xpath is case-sensitive so /customerlist is not the same as /CustomerList. Secondly, understand your XML hierarchy,
you have :

/CustomerList/customers/customer/productLines/productLine

So, your xpath in the EXTRACT should be :

/CustomerList/customers/customer/productLines/productLine

the EXTRACTVALUE should be :

/productLine/@status (or whatever attribute you require)

plus some case problems in the EXISTSNODE (customerList should be CustomerList (upper-case "C"), which you don't technically need, by the way, since you can put the filter on @status="A" in the EXTRACT.

Regards
Previous Topic: XML query with union
Next Topic: How to attach Change control document in the JDeveloper
Goto Forum:
  


Current Time: Mon Sep 01 21:51:36 CDT 2014

Total time taken to generate the page: 0.09683 seconds