By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE tablename (data) AS
SELECT '<a>
<b bNumber="1234567">
<bDetails LoadingStatus="loaded">
<TotalMass>45180</TotalMass>
</bDetails>
</b>
<b bNumber="12345">
<bDetails LoadingStatus="loaded">
<TotalMass>45180</TotalMass>
</bDetails>
</b>
</a>' FROM DUAL;
1 rows affected
SELECT xt.*
FROM /*DATABASENAME.*/TABLENAME x,
XMLTABLE(
'/a/b[@bNumber]'
PASSING XMLTYPE(x.DATA)
COLUMNS
bNumber VARCHAR2(20) PATH 'text()'
) xt
| BNUMBER |
|---|
| null |
| null |
SELECT xt.*
FROM /*DATABASENAME.*/TABLENAME x
CROSS JOIN XMLTABLE(
'/a/b'
PASSING XMLTYPE(x.DATA)
COLUMNS
bNumber VARCHAR2(20) PATH '@bNumber'
) xt
| BNUMBER |
|---|
| 1234567 |
| 12345 |
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
| BNUMBER | LOADINGSTATUS | TOTALMASS |
|---|---|---|
| 1234567 | loaded | 45180 |
| 12345 | loaded | 45180 |