im using oracle 12.1.0.2 database, i have two table in each one i have xmltype column
first table is ACCOUNT table it has two fields:
- recid number(10), -- account number
- xmlrecord sys.xmltype -- stores accounts detailed information, storage type is binary
bills table:
- recid varchar2(30); -- bill id
- xmlrecord sys.xmltype; -- stores bills detailed information, storage type is binary
the bill xmlrecord i have a tag there holding the bill account that the bill is attached to.
now simply i want to create a relational view to join the two tables to get simply the following
- bill id (from bill table)
- bill amount (from bill table)
- bill date (from bill table)
- bill account (from bill table)
- account category (from account table)
- account balance (from account table)
- account currency (from account table)
i tried to use XMLTABLE to map xml nodes from both tables to columns but i couldnt, i didn something like:
CREATE OR REPLACE VIEW BILL_DATA_V2_VW AS
SELECT P.RECID,po.ARRANGEMENT, li.customer
FROM FBNK_AA_BILL_DETAILS p,
XMLTable('/row' PASSING p.xmlrecord
COLUMNS
ARRANGEMENT VARCHAR2(30) PATH 'c1[position()=1]',
tot_amt NUMBER (16, 6) PATH 'c6[position()=1]',
currency VARCHAR2 (5) PATH 'c5[position()=1]',
account XMLType PATH 'FBNK_ACCOUNT/row') po,
XMLTable('FBNK_ACCOUNT/row181[position()=1]' PASSING po.account
COLUMNS
customer NUMBER(10) PATH 'c1[position()=1]',
category NUMBER(10) PATH 'c2[position()=1]',
PR NUMBER(10) PATH 'c13[position()=1]',
inactive_marker varchar2(5) PATH 'c22[position()=1]',
working_balance NUMBER(16,6) PATH 'c27[position()=1]',
working_balance NUMBER(16,6) PATH 'c155[position()=1li;
plz be patient with me cuz i new to xml
BR