By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH multivalue (ID, CURR_ASSET_TYPE, OPEN_BALANCE, DEBIT_MVMT, CREDIT_MVMT) AS(
VALUES
('0023216531', 'CREDITýCREDIT', '315246ý', 'ý-315246', 'ý315246'),
('0023216540', 'CREDITý50000ý50000ýCREDITý50000', '2173118.69ý432ýýý', 'ýýýý-461.69', 'ýý14.85ý461.69ý14.84'),
('0023216558', 'DEBITýDEBIT', '-5000ý', 'ý', 'ý5000'),
('0023216477', 'CREDITý50000ý50000ýCREDITý50000', '121667.58ý25.14ýýý', 'ýýýý-26.8', 'ýý0.83ý26.8ý0.83')
)
SELECT
m.id
, c1.seq
, c1.tok as CURR_ASSET_TYPE
, coalesce(c2.tok, 0) as OPEN_BALANCE
, coalesce(c3.tok, 0) as DEBIT_MVMT
, coalesce(c4.tok, 0) as CREDIT_MVMT
FROM
multivalue m
, xmltable
(
'for $id in tokenize($s, "ý") return <i>{string($id)}</i>'
passing
m.CURR_ASSET_TYPE as "s"
columns
seq for ordinality
, tok varchar(20) path '.'
) c1
, xmltable
(
'for $id in tokenize($s, "ý") return <i>{string($id)}</i>'
passing
m.OPEN_BALANCE as "s"
columns
seq for ordinality
, tok decfloat path 'if (. castable as xs:decimal) then xs:decimal(.) else ()'
) c2
, xmltable
(
| ID | SEQ | CURR_ASSET_TYPE | OPEN_BALANCE | DEBIT_MVMT | CREDIT_MVMT |
|---|---|---|---|---|---|
| 0023216477 | 1 | CREDIT | 121667.58 | 0 | 0 |
| 0023216477 | 2 | 50000 | 25.14 | 0 | 0 |
| 0023216477 | 3 | 50000 | 0 | 0 | 0.83 |
| 0023216477 | 4 | CREDIT | 0 | 0 | 26.8 |
| 0023216477 | 5 | 50000 | 0 | -26.8 | 0.83 |
| 0023216531 | 1 | CREDIT | 315246 | 0 | 0 |
| 0023216531 | 2 | CREDIT | 0 | -315246 | 315246 |
| 0023216540 | 1 | CREDIT | 2173118.69 | 0 | 0 |
| 0023216540 | 2 | 50000 | 432 | 0 | 0 |
| 0023216540 | 3 | 50000 | 0 | 0 | 14.85 |
| 0023216540 | 4 | CREDIT | 0 | 0 | 461.69 |
| 0023216540 | 5 | 50000 | 0 | -461.69 | 14.84 |
| 0023216558 | 1 | DEBIT | -5000 | 0 | 0 |
| 0023216558 | 2 | DEBIT | 0 | 0 | 5000 |