oracle 如何从多个同名节点中检查一个值，然后使用

hgb9j2n6 于 2023-08-04 发布在 Oracle

关注(0)|答案(2)|浏览(116)

我正在尝试从this XML table中提取值
我需要首先检查FreeCode节点中的ID是否为pTRAFC50。如果它确实是pTRAFC50，那么我将从FreeCode内的Value子节点中提取值。
我试着用[7]，但它不起作用

case when EXTRACTVALUE(XmlType(o.Xml), '/Equity/FreeCode[7]/@ID[7]') = 'pTRAFC50'
case when EXTRACTVALUE(XmlType(o.Xml), '/Equity/FreeCode[7]/@ID[7]') = 'pTRAFC50'

字符串
如果节点是“pTRAFC50”，我希望能够将NA拉到我的提取中。

oracle

来源：https://stackoverflow.com/questions/76638707/how-to-check-a-value-from-multiple-nodes-with-the-same-name-and-then-use-the-chi

2条答案

按热度按时间

2jcobegt1#

with data(xml) as(
    select xmltype(
q'~<Equity ID="TRCL000000007000" Side="1" Ccy="USD" OrderType="5" TimeInForce="2" AggKey="-886312559405477673" AvgPx="10" CumQty="400" LeavesQty="400" Stat="1" TxnTm="0001-01-01T00:00:00" AutoAllocate="true" PmOrderType="5" PlacedQty="800" LastUpdateTm="2023-04-24T10:44:52.8099417Z" CreatedTm="2023-04-24T10:41:52.8472202Z" OrdFlags="88" Trader="371" Owner="371" LastFillTm="2023-04-24T10:44:52.488" DealDeskID="SP-EQUITY" DimDeskID="SP-EQUITY" PortfolioMasterID="2" PortCcy="SGD" FirstPortArriveTm="2023-04-24T10:38:24.2358771Z" FirstPlaceTm="2023-04-24T10:42:35.3101154Z" FirstPortAcceptTm="2023-04-24T10:41:52.8472202Z" LastTxt="" LastPortMgrComment="" MultiplePortMgrComments="false" MultipleComments="false" PortGrp="INDAH-LPF" PortAcct="INDAH-LPSEAC" PortMgr="EQ - Tan Lee Tee" PortName="LP SPORE EQUITY ACTIVE FN" ModPor="EQ_SPORE" ModPorName="EQ - Singapore Equity" InvestorID="" CustodyIK="0" PmBrokerHasMultipleValues="false" PmCptyHasMultipleValues="false" RevID="000">
  <Hdr ObjectVersion="1" />
  <Instrmt DimSecID="V US" SecIK="20227" MIC="XNYS" AstClass="EQU" Sym="V US" ID="B2PZN04" Src="2" SecTyp="CS" Exch="NYS" Desc="VISA INC-CLASS A SHARES" CFICode="ESXXXX" BrdLots="100" SecSubTyp="COM_STK" Acronym="XNYS" CountryName="United States of America (the)" QuotFac="1" QuotType="1" QuoteType="1" Country="US" ExcIK="672" DimInstrumentType="5" MarketISOCountryCode="US" AdjustEffectiveDate="false" AdjustTerminationDate="false" AdjustPeriodEndDates="false">
    <AID AltID="US92826C8394" AltIDSrc="4" />
    <AID AltID="92826C839" AltIDSrc="1" />
    <AID AltID="B2PZN04" AltIDSrc="2" />
    <AID AltID="V US Equity" AltIDSrc="A" />
  </Instrmt>
  <OrdQty Qty="800" />
  <Pty R="11" ID="EQ - Tan Lee Tee" />
  <Portfolios />
  <FreeCode ID="pSECFC121" Value="BBG000PSKYX7" />
  <FreeCode ID="pORDERFC2" Value="" />
  <FreeCode ID="pORDERFC9" Value="TEST" />
  <FreeCode ID="pTRAFC42" Value="" />
  <FreeCode ID="pORDERFC1" Value="" />
  <FreeCode ID="pTRAFC4" Value="" />
  <FreeCode ID="pTRAFC50" Value="NA" />
  <FreeCode ID="pORDERFC19" Value="SUJIT" />
  <MatchingRules NettingRuleHasMultipleValues="false" BrokerNettingRuleHasMultipleValues="false" />
  <DkText />
</Equity>~') from dual
)
select xmlquery(
    '/Equity/FreeCode[@ID="pTRAFC50"]/@Value'
    passing xml
    returning content
).getStringVal() as res_query,
extractvalue(xml, '/Equity/FreeCode[@ID="pTRAFC50"]/@Value') as res_extract
from data;

RES_QUERY RES_EXTRACT
-------------------------
NA  NA

字符串

赞(0）回复(0）举报 2023-08-04

vuktfyat2#

试试这个模式：

/Equity/FreeCode[@ID = 'pTRAFC50']/@Value

字符串

赞(0）回复(0）举报 2023-08-04

我来回答

oracle 如何从多个同名节点中检查一个值，然后使用

2条答案

相关问题

热门标签

最新问答