sqlxmloracle-databaseshred

Select Data out of XML from a column in ORACLE database

I am trying to select various data out of XML that I have in an oracle Database. Then i can insert those items into another table for normal SQL queries (i.e. shreding the XML). So I have the XML data below in a column that is a Clob column in my oracle database. I used this example to do that.

XML Data 

<?xml version="1.0" encoding="utf-8"?>
<CarParkDataImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.transportdirect.info/carparking B:/CODE/carparks/CarParking.xsd" xmlns="http://www.transportdirect.info/carparking">
<CarPark>
    <CarParkRef>3</CarParkRef>
    <CarParkName>Nunnery Lane</CarParkName>
    <Location>York</Location>
    <Address>Nunnery Lane--York--North Yorkshire</Address>
    <Postcode>YO23 1AA</Postcode>
    <Telephone>01904551309</Telephone>
    <MinCostPence>200</MinCostPence>
    <IsParkAndRide>false</IsParkAndRide>
    <StayType>Short</StayType>
  </CarPark>
</CarParkDataImport>

I started with this query 

select car.ref as car_ref
  , car.loc as car_loc
 from XML_DOCUMENTS
    , xmltable('/CarParkDataImport/CarPark'  
           passing XML_DOCUMENTS.XMLDOC
           columns 
              "ref"  varchar2(30) path 'CarParkRef'
              , "loc"  varchar2(30) path 'Location'
          ) car

But i get the error message,

 ORA-00932: inconsistent datatypes: expected - got CLOB
 00932. 00000 -  "inconsistent datatypes: expected %s got %s"
 *Cause:    
 *Action:
 Error at Line: 5 Column: 24

So its expecting that column to be a XMLTYPE column, now i tried creating a table with this column and inserting the XML into that, but all the column said was (XMLTYPE), which I guess I have to register a schema for this to work, but the register schema complained about not being valid xml, so i gave up on that went back to the CLOB.

The second query using extract value seems to work, BUT brings back nulls 

SELECT EXTRACTVALUE(xmltype(xmldoc), '/CarParkDataImport/CarPark/Location')
FROM xml_documents;

I have 9 rows of in the XMLDOC column and it states 9 rows found, but all null. Then i tried another solution suggested as below

with src as (select xmltype(to_clob(XMLDOC)) /*)*/ AS messagetext FROM   xml_documents)
select car.REF as car_ref
   , car.LOC as car_loc
from src s
  , xmltable('/CarParkDataImport/CarPark'  
           passing S.messagetext
           columns 
              "REF"  varchar2(30) path 'CarParkRef'
              , "LOC"  varchar2(30) path 'Location'
          ) car
    ;

This appears to have the same effect where it runs and but brings nothing back.

What am I missing? Do I have to declare namespace and how? Is my paths mucked up, or should I try going back to loading into a XMLTYPE column?

Solution

  • xmltabe expects a xmltype data. So, convert your clob to xmltype. Also you have to declare xml namespace.

    SQL> create table xml_documents(
xmldoc clob
);

Table created.

SQL> insert into xml_documents values(
'<?xml version="1.0" encoding="utf-8"?>
<CarParkDataImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.transportdirect.info/carparking B:/CODE/carparks/CarParking.xsd" xmlns="http://www.transportdirect.info/carparking">
<CarPark>
    <CarParkRef>3</CarParkRef>
    <CarParkName>Nunnery Lane</CarParkName>
    <Location>York</Location>
    <Address>Nunnery Lane--York--North Yorkshire</Address>
    <Postcode>YO23 1AA</Postcode>
    <Telephone>01904551309</Telephone>
    <MinCostPence>200</MinCostPence>
    <IsParkAndRide>false</IsParkAndRide>
    <StayType>Short</StayType>
  </CarPark>
</CarParkDataImport>'
);

commit;

1 row created.
Commit complete.

SQL> select car.ref as car_ref
  , car.loc as car_loc
 from xml_documents
    , xmltable(xmlnamespaces(default 'http://www.transportdirect.info/carparking'),'CarParkDataImport/CarPark'  
           passing xmltype(xml_documents.xmldoc)
           columns 
               ref  varchar2(30) path 'CarParkRef'
              ,loc  varchar2(30) path 'Location'
          ) car;

CAR_REF                        CAR_LOC
------------------------------ ------------------------------
3                              York