By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH VIRTUAL_TABLE_1 (BILL_TYPE, BILL_DATE, BILL_STATUS) AS (
VALUES
('EXPECTED]PAYMENT]PAYMENT^PAYMENT'
, '20230901]20230908]20230915^20230915'
, 'SETTLED]CAPITALISE]CAPITALISE^SETTLED'))
SELECT B.BILL_TYPE_NEW, C.BILL_DATE_NEW, D.BILL_STATUS_NEW
FROM VIRTUAL_TABLE_1 A
, XMLTABLE('$doc/items/item'
PASSING XMLPARSE(DOCUMENT CAST('<items><item><value>'||REPLACE(A.BILL_TYPE,']','</value></item><item><value>')||'</value></item></items>' as CLOB)) as "doc"
COLUMNS
BILL_TYPE_NEW VARCHAR(255) PATH 'value'
) B
, XMLTABLE('$doc/items/item'
PASSING XMLPARSE(DOCUMENT CAST('<items><item><value>'||REPLACE(A.BILL_DATE,']','</value></item><item><value>')||'</value></item></items>' as CLOB)) as "doc"
COLUMNS
BILL_DATE_NEW VARCHAR(255) PATH 'value'
) C
, XMLTABLE('$doc/items/item'
PASSING XMLPARSE(DOCUMENT CAST('<items><item><value>'||REPLACE(A.BILL_STATUS,']','</value></item><item><value>')||'</value></item></items>' as CLOB)) as "doc"
COLUMNS
BILL_STATUS_NEW VARCHAR(255) PATH 'value'
) D
| BILL_TYPE_NEW | BILL_DATE_NEW | BILL_STATUS_NEW |
|---|---|---|
| EXPECTED | 20230901 | SETTLED |
| EXPECTED | 20230901 | CAPITALISE |
| EXPECTED | 20230901 | CAPITALISE^SETTLED |
| EXPECTED | 20230908 | SETTLED |
| EXPECTED | 20230908 | CAPITALISE |
| EXPECTED | 20230908 | CAPITALISE^SETTLED |
| EXPECTED | 20230915^20230915 | SETTLED |
| EXPECTED | 20230915^20230915 | CAPITALISE |
| EXPECTED | 20230915^20230915 | CAPITALISE^SETTLED |
| PAYMENT | 20230901 | SETTLED |
| PAYMENT | 20230901 | CAPITALISE |
| PAYMENT | 20230901 | CAPITALISE^SETTLED |
| PAYMENT | 20230908 | SETTLED |
| PAYMENT | 20230908 | CAPITALISE |
| PAYMENT | 20230908 | CAPITALISE^SETTLED |
| PAYMENT | 20230915^20230915 | SETTLED |
| PAYMENT | 20230915^20230915 | CAPITALISE |
| PAYMENT | 20230915^20230915 | CAPITALISE^SETTLED |
| PAYMENT^PAYMENT | 20230901 | SETTLED |
| PAYMENT^PAYMENT | 20230901 | CAPITALISE |
| PAYMENT^PAYMENT | 20230901 | CAPITALISE^SETTLED |
| PAYMENT^PAYMENT | 20230908 | SETTLED |
| PAYMENT^PAYMENT | 20230908 | CAPITALISE |
| PAYMENT^PAYMENT | 20230908 | CAPITALISE^SETTLED |
| PAYMENT^PAYMENT | 20230915^20230915 | SETTLED |
| PAYMENT^PAYMENT | 20230915^20230915 | CAPITALISE |
| PAYMENT^PAYMENT | 20230915^20230915 | CAPITALISE^SETTLED |
WITH SEARCH_WEEK (TGL) AS (
VALUES
(DATE('2021-12-25')),
(DATE('2021-12-26')),
(DATE('2021-12-27')),
(DATE('2021-12-28')),
(DATE('2021-12-29')),
(DATE('2021-12-30')),
(DATE('2021-12-31')),
(DATE('2022-01-01')),
(DATE('2022-01-02')),
(DATE('2022-01-03')),
(DATE('2022-01-04')),
(DATE('2022-01-05')),
(DATE('2022-01-06')),
(DATE('2022-01-07')),
(DATE('2022-01-08')),
(DATE('2022-01-09')),
(DATE('2022-01-10')),
(DATE('2022-01-11')),
(DATE('2022-01-12')),
(DATE('2022-01-13')),
(DATE('2022-01-14')),
(DATE('2022-01-15'))
)
SELECT * FROM SEARCH_WEEK
| TGL |
|---|
| 2021-12-25 |
| 2021-12-26 |
| 2021-12-27 |
| 2021-12-28 |
| 2021-12-29 |
| 2021-12-30 |
| 2021-12-31 |
| 2022-01-01 |
| 2022-01-02 |
| 2022-01-03 |
| 2022-01-04 |
| 2022-01-05 |
| 2022-01-06 |
| 2022-01-07 |
| 2022-01-08 |
| 2022-01-09 |
| 2022-01-10 |
| 2022-01-11 |
| 2022-01-12 |
| 2022-01-13 |
| 2022-01-14 |
| 2022-01-15 |