| Load XML file to Oracle [message #391639] |
Thu, 12 March 2009 15:00  |
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  |
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 |
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 #391831 is a reply to message #391647] |
Fri, 13 March 2009 15:16 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
