Appendix: Creating the Sample Data


We used the following methods to insert the XML data into the XML column named XMLCOL using the example data shipped with DataDirect XQuery®.

DB2

CREATE TABLE HOLDINGSXML (userid varchar(50) not null primary key, 
 XMLCOL DB2XML.XMLCLOB NOT LOGGED)

INSERT INTO HOLDINGSXML
 (SELECT 
 h.USERID, 
 xml2clob(XMLELEMENT(name "HOLDINGS", 
 XMLAGG(
 XMLELEMENT(name "SHARE", 
 XMLATTRIBUTES(s.COMPANYNAME AS COMPANY,h.USERID AS USERID),
 h.SHARES)))) 
 FROM HOLDINGS h, STATISTICAL s 
 WHERE h.STOCKTICKER = s.TICKER 
 GROUP BY h.USERID)

Oracle

CREATE TABLE HOLDINGSXML(userid varchar2(50) primary key, XMLCOL XMLTYPE)

INSERT INTO HOLDINGSXML
 (SELECT
 h.USERID, 
 XMLELEMENT("HOLDINGS",
 XMLAGG(
 XMLELEMENT("SHARE", 
 XMLATTRIBUTES(s.COMPANYNAME AS COMPANY,h.USERID AS USERID),
 h.SHARES)))
 FROM HOLDINGS h, STATISTICAL s
 WHERE h.STOCKTICKER = s.TICKER
 GROUP BY h.USERID)