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

    Unanswered: XML extraction and record size limit

    I am trying to run a simple XML extraction on a large DB just want to create a table with one column and a lot of data (to view in a browser).
    I use SPUFI to run my query but I assume it puts all the data in one record so I am limited by the record size and cant get to the bottom of the results this way.
    Is there any other way to do this? or should I just increase record size?
    SELECT
    XML2CLOB(
    XMLELEMENT (NAME "TABLE",
    XMLATTRIBUTES ('1' AS "BORDER",'width: 70%; height: 70px' AS "style",'center' AS "align" ),
    XMLELEMENT(NAME CAPTION,'DEPARTMENT-EMPLOYEE TABLE'),
    XMLELEMENT(NAME tr,
    XMLFOREST( 'DEPARTMENT' AS TH
    )
    ),
    XMLAGG(
    XMLELEMENT(NAME tr,
    XMLFOREST( E.ACCOUNT_NUMBER AS td
    )
    )
    )
    )
    )
    FROM UTILDB.DAILY_GLDM_BALANCE E where BRANCH='1144'
    ;

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm not sure I made not misunderstand your requirement.

    I thought your required output is like:
    <TABLE BORDER=1 style="width: 70&#37;; height: 70px" align=center>
    <CAPTION>DEPARTMENT-EMPLOYEE TABLE</CAPTION>
    <TR><TH>DEPARTMENT</TH></TR>
    <TR><TD>account_number 1</TD></TR>
    <TR><TD>account_number 2</TD></TR>
    .....
    <TR><TD>account_number n</TD></TR>
    </TABLE>

    So, if numbering to each lines as following:
    <!-- 01: 00001 --> <TABLE BORDER=1 style="width: 70%; height: 70px" alighn=center>
    <!-- 01: 00002 --> <CAPTION>DEPARTMENT-EMPLOYEE TABLE</CAPTION>
    <!-- 01: 00003 --> <TR><TH>DEPARTMENT</TH></TR>
    <!-- 10: 00001 --> <TR><TD>account_number 1</TD></TR>
    <!-- 10: 00002 --> <TR><TD>account_number 2</TD></TR>
    .....
    <!-- 10: nnnnn --> <TR><TD>account_number n</TD></TR>
    <!-- 90: 00001 --> </TABLE>

    query might be something like:
    Code:
    (
    SELECT '<!-- 01: 00001 -->' AS sequence
         , '<TABLE BORDER=1 style="width: 70%; height: 70px" alighn=center>' AS data
      FROM sysibm.sysdummy1
    UNION ALL
    SELECT '<!-- 01: 00002 -->' AS sequence
         , '<CAPTION>DEPARTMENT-EMPLOYEE TABLE</CAPTION>' AS data
      FROM sysibm.sysdummy1
    UNION ALL
    SELECT '<!-- 01: 00003 -->' AS sequence
         , '<TR><TH>DEPARTMENT</TH></TR>' AS data
      FROM sysibm.sysdummy1
    UNION ALL
    SELECT '<!-- 90: 00001 -->' AS sequence
         , '</TABLE>' AS data
      FROM sysibm.sysdummy1
    UNION ALL
    SELECT '<!-- 10: ' || E.ACCOUNT_NUMBER || ' -->' AS sequence
         , '<TR><TD>' || E.ACCOUNT_NUMBER || '</TD></TR>' AS data
      FROM UTILDB.DAILY_GLDM_BALANCE E where BRANCH='1144' 
    )
    ORDER BY sequence
    Or
    SELECT data
    FROM (my previous full-select using UNION ALL) q
    ORDER BY sequence
    Last edited by tonkuma; 11-17-09 at 15:10.

  3. #3
    Join Date
    Nov 2009
    Posts
    9
    Thanks a lot. I am trying to improve in this and your examples are really helpful.

Posting Permissions

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