CODEWITHSUNDEEP
sqloracle19cxmltable
cs_zineb
cs_zineb

Reputation: 33

oracle xmltable extract value

I have the xml :

<a>
     <b bNumber="1234567">
          <bDetails LoadingStatus="loaded">
             <TotalMass>45180</TotalMass>
          </bDetails>
     </b>
     <b bNumber="12345">
          <bDetails LoadingStatus="loaded">
             <TotalMass>45180</TotalMass>
          </bDetails>
     </b>
</a>

I need to extract the bNumber but I don't know How. I did this but I had : SQL Error [904] [42000]: ORA-00904 : identificateur non valide

SELECT xt.*
FROM DATABASENAME.TABLENAME x,
       XMLTABLE('/a/b[@bNumber]'
         PASSING XMLTYPE(x.DATA)
         COLUMNS 
           bNumber VARCHAR2(20)  PATH 'text()'
         ) xt

Upvotes: 0

Views: 46

Answers (1)

MT0
MT0

Reputation: 168470

The XPath /a/b[@bNumber] will find an element /a/b that has a bNumber attribute and then text() will find the text of the b element (and not the bNumber attribute).

If you want the attribute value then use:

SELECT xt.*
FROM   DATABASENAME.TABLENAME x
       CROSS JOIN XMLTABLE(
         '/a/b'
         PASSING XMLTYPE(x.DATA)
         COLUMNS 
           bNumber VARCHAR2(20)  PATH '@bNumber'
       ) xt

Which outputs:

BNUMBER
1234567
12345

Note: If you only want to check b elements with the bNumber attribute then you could use /a/b[@bNumber] but if the b elements all contain bNumber attributes then the extra filter appears to be redundant.

If you want all the attributes and values:

SELECT xt.*
FROM   DATABASENAME.TABLENAME x
       CROSS JOIN XMLTABLE(
         '/a/b'
         PASSING XMLTYPE(x.DATA)
         COLUMNS 
           bNumber       NUMBER       PATH '@bNumber',
           loadingstatus VARCHAR2(20) PATH 'bDetails/@LoadingStatus',
           totalmass     NUMBER       PATH 'bDetails/TotalMass'
       ) xt

Which outputs:

BNUMBER LOADINGSTATUS TOTALMASS
1234567 loaded 45180
12345 loaded 45180

fiddle

Upvotes: 0

Related Questions