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

    Unanswered: Converting DB2 data to XML file

    Hi, I am trying to create an XML file in the following format
    - <Division>
    - <Division_Advisor>
    <AdvisorNumber>BC09</AdvisorNumber>
    - <InvestorTransaction>
    <TransactionType>Sell</TransactionType>
    </InvestorTransaction>
    - <InvestorTransaction>
    <TransactionType>Buy</TransactionType>
    </InvestorTransaction>
    </Division_Advisor>

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

    There are about 6 different levels. I am not able to group all the transaction types for each AdvisorNumber. I get the following format

    - <Division name="DSC">
    - <Division_Advisor>
    <AdvisorNumber>BC09</AdvisorNumber>
    - <InvestorTransaction>
    <TransactionType>Sell</TransactionType>
    </InvestorTransaction>
    </Division_Advisor>
    - <Division_Advisor>
    <AdvisorNumber>BC09</AdvisorNumber>
    - <InvestorTransaction>
    <TransactionType>Buy</TransactionType>
    </InvestorTransaction>
    </Division_Advisor>
    - <Division_Advisor>
    <AdvisorNumber>ABC10</AdvisorNumber>
    - <InvestorTransaction>
    <TransactionType>Cancelled</TransactionType>
    </InvestorTransaction>
    </Division_Advisor>
    - <Division_Advisor>
    <AdvisorNumber>ABC10</AdvisorNumber>
    - <InvestorTransaction>
    <TransactionType>Confirmed</TransactionType>
    </InvestorTransaction>
    </Division_Advisor>
    -----
    </Division>

    I am using DB v.8.2. and below is the logic

    select xml2clob(
    xmlelement(name "Division",
    xmlattributes(t01.company_cd as "name"),
    xmlagg(
    xmlelement(name "Division_Advisor",
    xmlelement(name "AdvisorNumber", t01.acct_rr_cd),

    xmlelement(name "InvestorTransaction",
    xmlelement(name "TransactionType", t02.tran_cd)
    )
    )
    order by t01.acct_rr_cd
    )
    )
    )
    from navch.adv_trans as t01 join navch.transaction t02 on
    t01.company_cd = t02.company_cd and
    t01.acct_rr_cd = t02.acct_rr_cd

    where t01.company_cd = 'DSC'
    group by t01.company_cd
    with ur

    Where navch.adv_trans has advisors numbers and navch.transaction has transaction types.
    Any solution would be appreciated. Thanks. Nav
    Last edited by navch; 01-14-10 at 10:57.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although, I found some inconsistency of order of AdvisorNumber and TransactionType,
    I'll ignore it and show an example...
    (Tested on DB2 Express-C 9.7.1 on Windows.)
    Code:
    ------------------------------ Commands Entered ------------------------------
    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' )
    )
    SELECT xml2clob(
              xmlelement(name "Division",
                 xmlattributes(t01.company_cd as "name"),
                 xmlagg(
                    xmlelement(name "Division_Advisor",
                       xmlelement(name "AdvisorNumber", t01.acct_rr_cd),
                       tran_cd_list
                    )
                    order by t01.acct_rr_cd
                 )
              )
           )
      FROM adv_trans AS t01
      JOIN TABLE
           (SELECT company_cd
                 , acct_rr_cd
                 , 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
           ) t02
       ON  0=0
     WHERE t01.company_cd = 'DSC'
     GROUP BY
           t01.company_cd;
    ------------------------------------------------------------------------------

  3. #3
    Join Date
    Jan 2010
    Posts
    31
    Thanks for your help. I will try it. One more question, if I have to add more levels then can I use the same logic by just inserting those levels in this format.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    if I have to add more levels then can I use the same logic by just inserting those levels in this format.
    I think so.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think this part of my code is not so usual...
    Code:
      FROM adv_trans AS t01
      JOIN TABLE
           (SELECT company_cd
                 , acct_rr_cd
                 , 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
           ) t02
       ON  0=0
    Usualy, one will use code like...
    Code:
      FROM adv_trans AS t01
      JOIN
           (SELECT company_cd
                 , acct_rr_cd
                 , xmlagg(
                      xmlelement(name "InvestorTransaction",
                         xmlelement(name "TransactionType", tran_cd)
                      )
                      order by tran_cd
                   ) AS tran_cd_list
              FROM transaction AS t02
             GROUP BY
                   company_cd
                 , acct_rr_cd
           ) t02
       ON  t02.company_cd = t01.company_cd
       AND t02.acct_rr_cd = t01.acct_rr_cd
    The reason I used former code was, I afraid that DB2 might not optimize enough for the second code.

    Another example would be using scalar-subselect, like this:
    (Note: If you nested scalar-subselects to add more levels, you may be necessary to add TABLE keyword for inner scalar-subselects.)
    Code:
    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
    ;

  6. #6
    Join Date
    Jan 2010
    Posts
    31
    Thanks a lot. You are very helpful.

  7. #7
    Join Date
    Jan 2010
    Posts
    31
    Hi, I am having problem while adding one more level to the code. I am using last code mentioned as using scalar-subselect approach.

    I have to add one more Advisor Account LEVEL in it. (Actually there 6 different levels). I am new to DB2.

    can you please help me.

    - <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>
    Last edited by navch; 01-21-10 at 13:14.

Posting Permissions

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