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