Unable to Extract XML value from Oracle CBLOB

Question

I am trying to select a specific xml value as column in a Oracle 11G table which is stored as XML - Huge CLOB, but unable to. Kindly help

Contents of XML as Below

<Bid xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/LCC.Crew.FAReserves.wsvc.Entities.FAReserves">
  <AggressiveBidType></AggressiveBidType>
  <BidCriteria>
    <BidCriteria i:type="RapBidCriteria">
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">BAC</Value>
    </BidCriteria>
  </BidCriteria>
  <BidItem>RAP</BidItem>
  <BidName>BAC</BidName>
  <BidType>Standing</BidType>
  <CatsId>10023</CatsId>
  <EmployeeBidId>10620</EmployeeBidId>
  <EmployeeId>135289</EmployeeId>
  <EndDate>2015-03-29T00:00:00Z</EndDate>
  <IsAggressive>false</IsAggressive>
  <IsLodo>false</IsLodo>
  <IsOnPremiseReserve>false</IsOnPremiseReserve>
  <OperatingDate>2015-02-25T00:00:00Z</OperatingDate>
  <Priority>0</Priority>
</Bid>

Below Statement return null

SELECT extract(XMLTYPE(XMLBIDCONTENT),'/Bid/BidName/text()') "REFERENCE"
  FROM  AOCREWBIDDING.EMPLOYEEBIDS
   Where EmployeeBidID = 100

Below statement returns error

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

SELECT extract(XMLBIDCONTENT,'/Bid/BidName/text()').getStringVal() "REFERENCE"
  FROM  AOCREWBIDDING.EMPLOYEEBIDS
   Where EmployeeBidID = 100

Is it stored as XMLTYPE or CLOB?

Wernfried Domscheit
– Wernfried Domscheit

2018-07-05 15:45:06 +00:00
Commented Jul 5, 2018 at 15:45 — Wernfried Domscheit
– Wernfried Domscheit, Commented Jul 5, 2018 at 15:45

Alex Poole · Accepted Answer · 2018-07-05 16:01:56Z

The extract() function is deprecated. It's better to use XMLQuery().

You need to either declare a default namespace to match the one in the XML document:

select XMLQuery('
    declare default element namespace 
      "http://schemas.datacontract.org/2004/07/LCC.Crew.FAReserves.wsvc.Entities.FAReserves"; (: :)
    /Bid/BidName/text()'
  passing XMLType(xmlbidcontent)
  returning content) as BidName
from employeebids
where EmployeeBidID = 100;

BIDNAME                                                                         
--------------------------------------------------------------------------------
BAC

or (simpler but less robust) use a wildcard:

select XMLQuery('/*:Bid/*:BidName/text()'
  passing XMLType(xmlbidcontent)
  returning content) as BidName
from employeebids
where EmployeeBidID = 100;

BIDNAME                                                                         
--------------------------------------------------------------------------------
BAC

db<>fiddle showing your original queries and both of these, using a CTE to provide the sample CLOB value.

Collectives™ on Stack Overflow

Unable to Extract XML value from Oracle CBLOB

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related