Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    31

    Unanswered: Converting DB2 data to XML file

    Hi, I am trying to get the following XML Format using DB2 data. Tonkuma helped me get two levels but now I am adding one more level Advisor_Account, thinking to use the same logic but it did not work. I have about 6 levels to add. I am new to XML

    - <Division>
    - <Division_Advisor>
    <AdvisorNumber>BC09</AdvisorNumber>
    - <Advisor_Account>
    <AdvisorAccount>123456<AdvisorAccount>
    - <InvestorTransaction>
    <TransactionType>Sell</TransactionType>
    </InvestorTransaction>
    - <InvestorTransaction>
    <TransactionType>Buy</TransactionType>
    </InvestorTransaction>
    </Advisor_Account>

    </Division_Advisor>

    - <Division_Advisor>
    <AdvisorNumber>ABC10</AdvisorNumber>
    - <Advisor_Account>
    - <InvestorTransaction>
    <TransactionType>Cancelled</TransactionType>
    </InvestorTransaction>
    - <InvestorTransaction>
    <TransactionType>Confirmed</TransactionType>
    </InvestorTransaction>
    </Advisor_Account>
    </Division_Advisor>
    ..........
    </Division>


    and I am using the logic but I am getting error messages

    WITH
    adv_trans( company_cd , acct_rr_cd ) AS (
    VALUES
    ('DSC', 'BC09'),
    ('DSC', 'ABC10')
    ),
    transaction( company_cd , acct_rr_cd , tran_cd ) AS (
    VALUES
    ('DSC', 'BC09', 'Sell'),
    ('DSC', 'BC09', 'Buy'),
    ('DSC', 'ABC10', 'Cancelled'),
    ('DSC', 'ABC10', 'Confirmed')
    ),

    account( company_cd, acct_rr_cd, acct_id) as (
    values
    ('DSC', 'BC09', '1111'),
    ('DSC', 'BC09', '2222'),
    ('DSC', 'ABC10', '8888'),
    ('DSC', 'ABC10', '9999')
    )

    SELECT xml2clob(
    xmlelement(name "Division",
    xmlattributes(t01.company_cd as "name"),
    xmlagg(
    xmlelement(name "Division_Advisor",
    xmlelement(name "AdvisorNumber", t01.acct_rr_cd),
    (SELECT xmlagg(
    xmlelement(name "InvestorTransaction",
    xmlelement(name "TransactionType", tran_cd)
    )
    order by tran_cd
    ) AS tran_cd_list
    FROM transaction AS t02
    WHERE t02.company_cd = t01.company_cd
    AND t02.acct_rr_cd = t01.acct_rr_cd
    GROUP BY
    company_cd
    , acct_rr_cd
    )
    )
    order by t01.acct_rr_cd
    )
    )
    )
    FROM adv_trans AS t01
    WHERE t01.company_cd = 'DSC'
    GROUP BY
    company_cd
    ;

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'll try to be as specific as you are: you have an error in your query. May be there's a superfluous SELECT in there...
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First, I'm sorry that I couldn't make a query for more than three levels by using scalar-subselect.
    I got an error message:
    SQL0112N The operand of the column function "SYSIBM.XMLAGG" includes a column
    function, a scalar fullselect, or a subquery. SQLSTATE=42607

    I don't know wheather it is possible or not, now.


    I modified a little for result XML format as following, to make clearer(for me) three levels of XML.
    (names may not be appropriate.)
    Levels are:
    Division(company_cd) ---> Division_Advisor(acct_rr_cd) ---> Advisor_Account(acct_id) ---> InvestorTransaction(tran_cd , tran_dt)

    -<Division name="DSC">
    ---<Division_Advisor>
    -----<AdvisorNumber> BC09</AdvisorNumber>
    -----<Advisor_Account>
    -------<AdvisorAccount>123456</AdvisorAccount>
    -------<InvestorTransaction>
    ---------<TransactionType>Buy</TransactionType>
    ---------<TransactionDate>2010-01-08</TransactionDate>
    -------</InvestorTransaction>
    -------<InvestorTransaction>
    ---------<TransactionType>Sell</TransactionType>
    ---------<TransactionDate>2009-12-15</TransactionDate>
    -------</InvestorTransaction>
    -----</Advisor_Account>
    -----<Advisor_Account>
    -------<AdvisorAccount>456789</AdvisorAccount>
    -------<InvestorTransaction>
    ---------<TransactionType>Buy</TransactionType>
    ---------<TransactionDate>2010-01-21</TransactionDate>
    -------</InvestorTransaction>
    -------<InvestorTransaction>
    ---------<TransactionType>Sell</TransactionType>
    ---------<TransactionDate>2009-11-13</TransactionDate>
    -------</InvestorTransaction>
    -------<InvestorTransaction>
    ---------<TransactionType>Sell</TransactionType>
    ---------<TransactionDate>2009-12-19</TransactionDate>
    -------</InvestorTransaction>
    -----</Advisor_Account>
    ---</Division_Advisor>
    ---<Division_Advisor>
    -----<AdvisorNumber>ABC10</AdvisorNumber>
    -----<Advisor_Account>
    -------<AdvisorAccount>234567</AdvisorAccount>
    -------<InvestorTransaction>
    ---------<TransactionType>Cancelled</TransactionType>
    ---------<TransactionDate>2009-12-17</TransactionDate>
    -------</InvestorTransaction>
    -------<InvestorTransaction>
    ---------<TransactionType>Confirmed</TransactionType>
    ---------<TransactionDate>2010-01-15</TransactionDate>
    -------</InvestorTransaction>
    -----</Advisor_Account>
    ---</Division_Advisor>
    -</Division>

    Here is a query to get the XML format:
    (Tested on DB2 9.7.1 on Windows/NT.)
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      adv_trans( company_cd , acct_rr_cd ) AS (
    VALUES
    ( 'DSC' , ' BC09' ) ,
    ( 'DSC' , 'ABC10' )
    )
    , account( company_cd , acct_rr_cd , acct_id ) as (
    VALUES
    ( 'DSC' , ' BC09' , '123456' ) ,
    ( 'DSC' , ' BC09' , '456789' ) ,
    ( 'DSC' , 'ABC10' , '234567' ) ,
    ( 'DSC' , 'ABC10' , '999999' )
    )
    , transaction( company_cd , acct_rr_cd , acct_id , tran_cd , tran_dt ) AS (
    VALUES
    ( 'DSC' , ' BC09' , '123456' , 'Sell'      , '2009-12-15' ) ,
    ( 'DSC' , ' BC09' , '123456' , 'Buy'       , '2010-01-08' ) ,
    ( 'DSC' , ' BC09' , '456789' , 'Sell'      , '2009-11-13' ) ,
    ( 'DSC' , ' BC09' , '456789' , 'Sell'      , '2009-12-19' ) ,
    ( 'DSC' , ' BC09' , '456789' , 'Buy'       , '2010-01-21' ) ,
    ( 'DSC' , 'ABC10' , '234567' , 'Cancelled' , '2009-12-17' ) ,
    ( 'DSC' , 'ABC10' , '234567' , 'Confirmed' , '2010-01-15' )
    )
    SELECT xml2clob(
              xmlelement(name "Division"
               , xmlattributes(company_cd as "name")
               , xmlagg(
                    acct_list
                    order by acct_rr_cd
                 )
              )
           )
      FROM adv_trans AS t01
         , TABLE
           (SELECT xmlelement(name "Division_Advisor"
                    , xmlelement(name "AdvisorNumber" , acct_rr_cd )
                    , xmlagg(
                         t03.tran_cd_list
                         order by acct_id
                      )
                   ) AS acct_list
              FROM account AS t02
                 , TABLE
                   (SELECT xmlelement(name "Advisor_Account"
                            , xmlelement(name "AdvisorAccount" , acct_id )
                            , xmlagg(
                                 xmlelement(name "InvestorTransaction"
                                  , xmlelement(name "TransactionType" , tran_cd )
                                  , xmlelement(name "TransactionDate" , tran_dt )
                                 )
                                 order by tran_cd
                              )
                           ) AS tran_cd_list
                      FROM transaction AS t03
                     WHERE t03.company_cd = t02.company_cd
                       AND t03.acct_rr_cd = t02.acct_rr_cd
                       AND t03.acct_id    = t02.acct_id
                     GROUP BY
                           company_cd
                         , acct_rr_cd
                         , acct_id
                   ) t03
             WHERE t02.company_cd = t01.company_cd
               AND t02.acct_rr_cd = t01.acct_rr_cd
             GROUP BY
                   company_cd
                 , acct_rr_cd
           ) t02
     WHERE company_cd = 'DSC'
     GROUP BY
           company_cd
    ;

Posting Permissions

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