Converting DB2 data to XML file

navch · #1 (**permalink**) 01-14-10, 09:35

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

tonkuma · #2 (**permalink**) 01-14-10, 12:03

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;
------------------------------------------------------------------------------

navch · #3 (**permalink**) 01-14-10, 13:02

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.

tonkuma · #4 (**permalink**) 01-14-10, 13:24

Quote:

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.

tonkuma · #5 (**permalink**) 01-14-10, 13:41

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
;

navch · #6 (**permalink**) 01-14-10, 13:56

Thanks a lot. You are very helpful.

navch · #7 (**permalink**) 01-21-10, 12:08

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>

Internet Services

Web Development

Digital Marketing

Consumer Tech