Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

XMLTABLE where condition

kron777Nov 18 2013 — edited Nov 18 2013

11g  11.2.0.3.0

Newbie on XMLTABLE XML DB!

sample XML (person_data.person_data):

<CstmrPmtStsRpt>

  <GrpHdr>

    <MsgId>99</MsgId>

    <CreDtTm>2013-11-12T08:10:57</CreDtTm>

    <InitgPty>

      <Id>

        <OrgId>

          <Othr>

            <Id>BANK_ID</Id>

            <SchmeNm>

              <Cd>BANK</Cd>

            </SchmeNm>

          </Othr>

          <Othr>

            <Id>CUST_ID</Id>

            <SchmeNm>

              <Cd>CUST</Cd>

            </SchmeNm>

          </Othr>

        </OrgId>

      </Id>

    </InitgPty>

  </GrpHdr>

</CstmrPmtStsRpt>

Would like to extract the Id associated with the BANK (and only the bank Id):

Requested SQL output:
BANK_ID

So far I've come up with:

SELECT t.column_value

FROM person_data a,

  XMLTABLE (

    'for $root in /CstmrPmtStsRpt/GrpHdr/InitgPty/Id/OrgId/Othr

     where $root/SchmeNm/Cd = "BANK"

      return $root/Id'

  passing a.person_data) t;

Which returns

<Id>BANK_ID</Id>

Adding text() as below:

SELECT t.column_value

FROM person_data a,

  XMLTABLE (

    'for $root in /CstmrPmtStsRpt/GrpHdr/InitgPty/Id/OrgId/Othr

     where $root/SchmeNm/Cd = "BANK"

      return $root/Id/text()'

  passing a.person_data) t;

Returns <no rows returned>

I know I'm missing something fundamental here - just need a pointer. Thanks!

This post has been answered by odie_63 on Nov 18 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 16 2013
Added on Nov 18 2013
1 comment
2,272 views