Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009
    Posts
    9

    Unanswered: simple xml extraction

    I want to publish some data from DB2 to XML to view as a report in a web browser. I want to create a simple table (with header and caption)
    This code repeats header and caption for every row , how can I change it to
    repeat the caption and header only once .

    SELECT
    XML2CLOB(
    XMLELEMENT (NAME "TABLE",
    XMLATTRIBUTES ('1' AS "BORDER"),
    XMLELEMENT(NAME CAPTION,'DEPARTMENT-EMPLOYEE TABLE'),
    XMLFOREST('DEPT NO' AS TH,'DEPARTMENT'AS TH,'EMP NO' AS TH),
    XMLELEMENT (NAME TR),
    XMLELEMENT (NAME TD, e.ACCT_NO),
    XMLELEMENT (NAME TD, e.REC_NO),
    XMLELEMENT (NAME TD,e.TRAN_DATE)
    )
    )
    FROM UTILDB.TBLOAD E FETCH FIRST 10 ROWS ONLY

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example.

    Note(1): Tested on DB2 9.7 for LUW. So, some modifications may be necessary to run on earlier DB2 version.
    Note(2): Add WHERE clause or use subquery, to select first 10 rows only.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     tbload(acct_no, rec_no, tran_date) AS (
    VALUES
     (  1, 1, '2009-10-01')
    ,(  1, 2, '2009-10-02')
    ,(  1, 3, '2009-10-05')
    ,(  1, 4, '2009-10-10')
    ,(  1, 5, '2009-10-13')
    ,(  2, 1, '2009-09-11')
    ,(  2, 3, '2009-09-21')
    ,(  2, 5, '2009-09-29')
    ,(100, 2, '2009-11-22')
    ,(100, 4, '2009-11-23')
    ,(100, 6, '2009-11-24')
    ,(100, 7, '2009-11-25')
    )
    SELECT
           XML2CLOB(
           XMLELEMENT (NAME "TABLE",
              XMLATTRIBUTES ('1' AS "BORDER"),
              XMLELEMENT(NAME CAPTION,'DEPARTMENT-EMPLOYEE TABLE'),
              XMLELEMENT(NAME tr,
                 XMLFOREST( 'DEPT NO'     AS TH
                          , 'DEPARTMENT'  AS TH
                          , 'EMP NO'      AS TH)
              ),
              XMLAGG(
                 XMLELEMENT(NAME tr,
                    XMLFOREST( e.ACCT_NO   AS td
                             , e.REC_NO    AS td
                             , e.TRAN_DATE AS td)
                 )
                 ORDER BY
                       e.ACCT_NO
                     , e.REC_NO
              )
           )
           )
      FROM TBLOAD E
    -- GROUP BY
    --       GROUPING SETS (())
    ;
    ------------------------------------------------------------------------------
    
    1          
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    <TABLE BORDER="1"><CAPTION>DEPARTMENT-EMPLOYEE TABLE</CAPTION><TR><TH>DEPT NO</TH><TH>DEPARTMENT</TH><TH>EMP NO</TH></TR><TR><TD>1</TD><TD>1</TD><TD>2009-10-01</TD></TR><TR><TD>1</TD><TD>2</TD><TD>2009-10-02</TD></TR><TR><TD>1</TD><TD>3</TD><TD>2009-10-05</TD></TR><TR><TD>1</TD><TD>4</TD><TD>2009-10-10</TD></TR><TR><TD>1</TD><TD>5</TD><TD>2009-10-13</TD></TR><TR><TD>2</TD><TD>1</TD><TD>2009-09-11</TD></TR><TR><TD>2</TD><TD>3</TD><TD>2009-09-21</TD></TR><TR><TD>2</TD><TD>5</TD><TD>2009-09-29</TD></TR><TR><TD>100</TD><TD>2</TD><TD>2009-11-22</TD></TR><TR><TD>100</TD><TD>4</TD><TD>2009-11-23</TD></TR><TR><TD>100</TD><TD>6</TD><TD>2009-11-24</TD></TR><TR><TD>100</TD><TD>7</TD><TD>2009-11-25</TD></TR></TABLE>
    
      1 record(s) selected.
    Formatted result:
    <TABLE BORDER="1">
    <CAPTION>DEPARTMENT-EMPLOYEE TABLE</CAPTION>
    <TR><TH>DEPT NO</TH><TH>DEPARTMENT</TH><TH>EMP NO</TH></TR>
    <TR><TD>1</TD><TD>1</TD><TD>2009-10-01</TD></TR>
    <TR><TD>1</TD><TD>2</TD><TD>2009-10-02</TD></TR>
    <TR><TD>1</TD><TD>3</TD><TD>2009-10-05</TD></TR>
    <TR><TD>1</TD><TD>4</TD><TD>2009-10-10</TD></TR>
    <TR><TD>1</TD><TD>5</TD><TD>2009-10-13</TD></TR>
    <TR><TD>2</TD><TD>1</TD><TD>2009-09-11</TD></TR>
    <TR><TD>2</TD><TD>3</TD><TD>2009-09-21</TD></TR>
    <TR><TD>2</TD><TD>5</TD><TD>2009-09-29</TD></TR>
    <TR><TD>100</TD><TD>2</TD><TD>2009-11-22</TD></TR>
    <TR><TD>100</TD><TD>4</TD><TD>2009-11-23</TD></TR>
    <TR><TD>100</TD><TD>6</TD><TD>2009-11-24</TD></TR>
    <TR><TD>100</TD><TD>7</TD><TD>2009-11-25</TD></TR>
    </TABLE>
    Last edited by tonkuma; 11-08-09 at 06:34.

  3. #3
    Join Date
    Nov 2009
    Posts
    9
    Thanks a lot, it really helped. Do you know a book or document that has detailed information on these functions. I have found a couple of IBM books but they all mention this section briefly.
    Oh, and thanks again for your time

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Do you know a book or document that has detailed information on these functions.
    I only read manuals "DB2 SQL Reference Volume 1" of each DB2 version/release.
    (Of course I did repeated trial and error. It was good practice for me.)
    And, I used my basic knowledge about html.

    I also want to read other books or documents describing practical use of XML functions.
    Last edited by tonkuma; 11-08-09 at 03:23.

  5. #5
    Join Date
    Nov 2009
    Posts
    9
    Thanks again, I looked at a lot of xml books , but it didn't occur to me to look at the SQL refrence )

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126

    simple XML extraction: a reference

    There have been several very instructive presentations on this topic at the past IDUG conferences; see e.g. the following one by Mattias Nicola which I find very useful: http://www.idug.org/conferences/NA2006/data/NA06D01.pdf

    If you are looking for a similarly instructive presentation on extracting a relational "view" from an XML document (e.g. stored in a DB2 XML column), see http://www.idug.org/conferences/EU2008/data/EU08E14.pdf (also by Matthias Nicola).
    Last edited by Peter.Vanroose; 11-10-09 at 15:38.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •