simple xml extraction

Navid2000 · #1 (**permalink**) 11-07-09, 03:05

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

tonkuma · #2 (**permalink**) 11-07-09, 06:36

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>

Navid2000 · #3 (**permalink**) 11-08-09, 02:45

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

tonkuma · #4 (**permalink**) 11-08-09, 03:06

Quote:

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.

Navid2000 · #5 (**permalink**) 11-10-09, 02:55

Thanks again, I looked at a lot of xml books , but it didn't occur to me to look at the SQL refrence

)

Peter.Vanroose · #6 (**permalink**) 11-10-09, 15:34

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).

Internet Services

Web Development

Digital Marketing

Consumer Tech