Db2 Developer-C 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TEST (ID VARCHAR(16), PROFILE VARCHAR(4096))
INSERT INTO TEST (ID, PROFILE)
VALUES
('BOB',  'BM        BS        DM        FM        IC        IC6       IL        IM        JN101     MM        XC        XM        XR        '),
('BILL', 'ZZ        XY        ')
SELECT sd.ID, si.item as PROFILE
  from TEST sd
      ,XMLTABLE('$doc/items/item'
          PASSING XMLPARSE(DOCUMENT CAST(   '<items><item>'
                                         || replace(sd.PROFILE , ' ' , '</item><item>')
                                         || '</item></items>' as CLOB
                                        )
                           ) as "doc"
          COLUMNS
          ITEM VARCHAR(255) PATH '.'
      ) si 
WHERE si.item <> ''
      


ID
PROFILE


BOB
BM

BOB
BS

BOB
DM

BOB
FM

BOB
IC

BOB
IC6

BOB
IL

BOB
IM

BOB
JN101

BOB
MM

BOB
XC

BOB
XM

BOB
XR

BILL
ZZ

BILL
XY

ID	PROFILE
BOB	BM
BOB	BS
BOB	DM
BOB	FM
BOB	IC
BOB	IC6
BOB	IL
BOB	IM
BOB	JN101
BOB	MM
BOB	XC
BOB	XM
BOB	XR
BILL	ZZ
BILL	XY