Home:Learn XQuery:XQuery Tutorial:Querying XML Columns :Appendix

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)