Re: Thoughts/experiences with JSON/XML on DB2

If I take a raw table like this

CREATE TABLE ROB.DELETEME (DELETEMEDATA CHAR (80 ) NOT NULL WITH
DEFAULT) rcdfmt deletemer

With a couple of values like this:
INSERT INTO ROB.DELETEME VALUES('H0100000011234567')
INSERT INTO ROB.DELETEME VALUES('D01000000100001ABCDEFG')

And create a few views like
CREATE VIEW ROB.DELETEMEH01 AS
SELECT CAST(SUBSTR(DELETEMEDATA, 1, 3) AS CHAR(3)) AS RECORDTYPE,
CAST(SUBSTR(DELETEMEDATA, 4, 7) AS DEC(7, 0)) AS ORDERNBR,
CAST(SUBSTR(DELETEMEDATA, 11, 7) AS DEC(7, 0)) AS CUSTNBR
FROM ROB.DELETEME
WHERE SUBSTR(DELETEMEDATA, 1, 3) = 'H01'

and
CREATE VIEW ROB.DELETEMED01 AS
SELECT CAST(SUBSTR(DELETEMEDATA, 1, 3) AS CHAR(3)) AS RECORDTYPE,
CAST(SUBSTR(DELETEMEDATA, 4, 7) AS DEC(7, 0)) AS ORDERNBR,
CAST(SUBSTR(DELETEMEDATA, 11, 5) AS DEC(5, 0)) AS LINENBR,
CAST(SUBSTR(DELETEMEDATA, 16, 15) AS CHAR(15)) AS ITEMNBR
FROM ROB.DELETEME
WHERE SUBSTR(DELETEMEDATA, 1, 3) = 'D01'

I can do stuff like
SELECT h.ordernbr, h.custnbr, d.linenbr, d.itemnbr
from rob.deletemeh01 h join rob.deletemed01 d
on h.ordernbr=d.ordernbr
order by h.ordernbr, d.linenbr

....+....1....+....2....+....3....+....4.
ORDERNBR CUSTNBR LINENBR ITEMNBR
1 1,234,567 1 ABCDEFG
******** End of data ********

This leaves your original table unaltered.

Rob Berendt