无法从Oracle CBLOB中提取XML值 [英] Unable to Extract XML value from Oracle CBLOB

问题描述

我试图选择一个特定的xml值作为存储为XML的Oracle 11G表中的列-巨大的CLOB，但无法.请帮助

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

XML内容如下

<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>

以下语句返回空值

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

下面的语句返回错误

ORA-00932:数据类型不一致:预期-得到- 00932.00000-数据类型不一致:预期的％s得到了％s" *原因:
*操作:第83行的错误:第8列

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

推荐答案

已弃用extract()函数.最好使用 XMLQuery() .

您需要声明一个默认名称空间以匹配XML文档中的名称空间:

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<>小提琴显示原始查询以及这两个查询，使用CTE提供样本CLOB值.

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

这篇关于无法从Oracle CBLOB中提取XML值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！