We have a table named XML_TEST which contains XML as XMLTYPE.
XML sample and its structure is as below,
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<invoice>
<AR_ITEMS elem="2">
<SERVICE_OBJ>0.0.0.1 /service/telco/gsm/telephony 44745735656 0</SERVICE_OBJ>
<ITEM_NO>B1-730869289,5</ITEM_NO>
<NAME>Cycle forward</NAME>
<ITEM_TOTAL>637.5</ITEM_TOTAL>
<DUE_T>1676408400</DUE_T>
<DUE>637.5</DUE>
<DISPUTED>0</DISPUTED>
<EVENT_POID_LIST></EVENT_POID_LIST>
<ACCOUNT_OBJ>0.0.0.1 /account 44709279566 22</ACCOUNT_OBJ>
<BILLINFO_OBJ>0.0.0.1 /billinfo 44709278926 0</BILLINFO_OBJ>
<AR_BILLINFO_OBJ>0.0.0.1 /billinfo 44709278926 0</AR_BILLINFO_OBJ>
<BILL_OBJ>0.0.0.1 /bill 48486806027 0</BILL_OBJ>
<EFFECTIVE_T>1673730000</EFFECTIVE_T>
<CREATED_T>1673732390</CREATED_T>
<ITEM_OBJ>0.0.0.1 /item/cycle_forward 340795877581277892 1</ITEM_OBJ>
<DEVICE_ID>97455066626</DEVICE_ID>
<PRIMARY_MSID></PRIMARY_MSID>
<AAC_ACCESS></AAC_ACCESS>
<EVENTS elem="110">
<END_T>1673730000</END_T>
<START_T>1673730000</START_T>
<CREATED_T>1673732391</CREATED_T>
<NET_QUANTITY>1</NET_QUANTITY>
<SYS_DESCR>Cycle Forward Fees (srvc): Business Unlimited VIP</SYS_DESCR>
<RERATE_OBJ>0.0.0.0 0 0</RERATE_OBJ>
<SESSION_OBJ>0.0.0.1 /event/session 0 0</SESSION_OBJ>
<TIMEZONE_ADJ_END_T>1673732391</TIMEZONE_ADJ_END_T>
<RATED_TIMEZONE_ID>Asia/Qatar</RATED_TIMEZONE_ID>
<ITEM_OBJ>0.0.0.1 /item/cycle_forward 340795877581277892 0</ITEM_OBJ>
<EVENT_OBJ>0.0.0.1 /event/billing/product/fee/cycle/cycle_forward_monthly 340795877581278916 0</EVENT_OBJ>
<IMPACT_CATEGORY>Rate 1</IMPACT_CATEGORY>
<ACCOUNT_OBJ>0.0.0.1 /account 44709279566 0</ACCOUNT_OBJ>
</EVENTS>
<EVENTS elem="111">
<END_T>1673730000</END_T>
<START_T>1673730000</START_T>
<CREATED_T>1673732391</CREATED_T>
<NET_QUANTITY>1</NET_QUANTITY>
<SYS_DESCR>Cycle Forward Fees (srvc): Business Unlimited VIP</SYS_DESCR>
<RERATE_OBJ>0.0.0.0 0 0</RERATE_OBJ>
<SESSION_OBJ>0.0.0.1 /event/session 0 0</SESSION_OBJ>
<TIMEZONE_ADJ_END_T>1673732391</TIMEZONE_ADJ_END_T>
<RATED_TIMEZONE_ID>Asia/Qatar</RATED_TIMEZONE_ID>
<ITEM_OBJ>0.0.0.1 /item/cycle_forward 340795877581277892 0</ITEM_OBJ>
<EVENT_OBJ>0.0.0.1 /event/billing/product/fee/cycle/cycle_forward_monthly 340795877581278916 0</EVENT_OBJ>
<IMPACT_CATEGORY>Rate 1</IMPACT_CATEGORY>
<ACCOUNT_OBJ>0.0.0.1 /account 44709279566 0</ACCOUNT_OBJ>
</EVENTS>
</AR_ITEMS>
</invoice>
I have written one query as below,
select AR_ITEMS_EVENTS.* from XWDDSB.XML_TEST,
XMLTABLE(
'/invoice/*[self::SUB_ITEMS or self::AR_ITEMS or self::OTHER_ITEMS]/EVENTS'
PASSING XML_DATA
COLUMNS
ITEMTYPE varchar2(30) path 'name()',
"EVENT_OBJ" NVARCHAR2(255) PATH 'EVENT_OBJ',
"IMPACT_CATEGORY" NVARCHAR2(255) PATH 'IMPACT_CATEGORY'
)AR_ITEMS_EVENTS;
The above query returns ITEMTYPE as EVENTS as in the below image. But I do need ITEMTYPE as EVENTS node's parent node name. So the above query should return ITEMTYPE as AR_ITEMS.
Can anyone help me write a query using XMLTABLE so that I get ITEMTYPE as parent node name for all the child nodes in the result set.
Please note that there will be multiple EVENTS nodes inside the parent AR_ITEMS node. And we dont want to change XMLTYPE data type to something else like CLOB to achieve this.
We are using Oracle 19c
Maybe start as the AR_EVENT level to get your ITEMTYPE, and then drill-down into the EVENTS to pick up the rest, eg