Topic
  • 1 reply
  • Latest Post - ‏2012-04-26T19:17:52Z by girhu01
girhu01
girhu01
2 Posts

Pinned topic Possible to build XML document from mulitiple rows of a normal DB2 tbl?

‏2012-04-26T19:12:08Z |
If I have this character string in two columns of another table can they be combined to SET one XML document?

ROW-1
'<sandwich>'
'<bread toasted="yes" type="French" />'
'<meat>Ham</meat>'

ROW-2
'<filling>Cheese, lettuce, tomato</filling>'
'</sandwich>'

Example I've tried:

DROP TABLE CHARTB;
COMMIT;
CREATE TABLE CHARTB(SEQNO INTEGER, CHARCOL CHAR(200) );
INSERT INTO CHARTB VALUES(1, 'INITTEST');
INSERT INTO CHARTB VALUES(2, 'INITTEST');
COMMIT;

UPDATE CHARTB
SET CHARCOL =
'<sandwich>' CONCAT
'<meat>Ham</meat>'
WHERE SEQNO = 1
;
UPDATE CHARTB
SET CHARCOL =
'<filling>Cheese, lettuce, tomato</filling>' CONCAT
'</sandwich>'
WHERE SEQNO = 2
;
COMMIT;
SELECT CHARCOL FROM CHARTB ORDER BY SEQNO;
  • CHARCOL
  • <sandwich><meat>Ham</meat>
  • <filling>Cheese, lettuce, tomato</filling></sandwich>
DROP TABLE XMLTB;
COMMIT;
CREATE TABLE XMLTB (XMLCOL XML );
INSERT INTO XMLTB VALUES('<INITTEST/>');
COMMIT;
UPDATE XMLTB
SET XMLCOL =
(SELECT CHARCOL FROM CHARTB ORDER BY SEQNO)
;
COMMIT;
--DSNT408I SQLCODE = -20345, ERROR: THE XML VALUE IS NOT A WELL-FORMED
  • DOCUMENT WITH A SINGLE ROOT ELEMENT

Scott Fauque
Updated on 2012-04-26T19:17:52Z at 2012-04-26T19:17:52Z by girhu01
  • girhu01
    girhu01
    2 Posts

    Re: Possible to build XML document from mulitiple rows of a normal DB2 tbl?

    ‏2012-04-26T19:17:52Z  
    Sorry ... forgot the environment info.

    z/OS DB2 9 and above.

    Scott Fauque