I recently encountered a request to generate XML in one select query in Oracle to address the problem on the Cartesian table on the query created by another developer. It also addresses the additional overhead from the java program that transforms the data. As we know, Oracle has a number of handy xml functions that can help us do this requirement. For this particular requirement, it’s something new in such a way that it is nested XML beyond the second level. I’ve created queries for similar requests previously but I never did a nested data.


At any rate, here’s how I implemented the requirement. Since our CRM is Siebel, my sample is basically applicable for it. Basically, the requirement is to show the details of the customer account, billing accounts, the assets under the billing accounts, attributes attached to the asset and then the contact person of the customer. The hierarchy is illustrated below.

root
  +- customer
    +- billing
      +-asset
        +-attribute
    +- contact

As you can see, this is effectively four levels not counting the root. And here’s the sql query.

SELECT XMLROOT(XMLELEMENT("Root",
         XMLAGG(XMLELEMENT("Customer",
                  XMLELEMENT("DocNum",accnt.x_doc_num),
                  XMLELEMENT("Name",accnt.name),
                  (SELECT XMLAGG(XMLELEMENT("Billing",
                       XMLELEMENT("AccountNo",bill.ou_num),
                       (SELECT XMLAGG(XMLELEMENT("Asset",
                                    XMLELEMENT("Product", prod.name),
                                    XMLELEMENT("SerialNo", moli.serial_num),
                                    (
                                    SELECT XMLAGG(XMLELEMENT("Attribute",
                                           XMLELEMENT("AttrName", attr.attr_name),
                                           XMLELEMENT("AttrValue", DECODE(attr.data_type_cd,
                                                                          'Text',   attr.char_val,
                                                                          'Number', attr.num_val,
                                                                          'Date', TO_CHAR(attr.Date_Val,'MON DD, YYYY'))
                                                      )
                                           )
                                           )
                                    FROM   siebel.s_asset_xa attr
                                    WHERE   attr.asset_id = moli.row_id
                                    )
                                    )
                                )
                        FROM   siebel.s_asset moli,
                               siebel.s_prod_int prod
                        WHERE  prod.row_id       = moli.prod_id
                        AND    moli.bill_accnt_id = bill.row_id
                        AND    moli.row_id = moli.root_asset_id
                       )
                    )
                    )
             FROM   siebel.s_org_ext bill
             WHERE  bill.master_ou_id = accnt.row_id
             AND    bill.accnt_type_cd = 'Billing'
             ),
             (SELECT   XMLAGG(XMLELEMENT("Contact",
                          XMLATTRIBUTES(contact.x_contact_name "ContactName"),
                          XMLELEMENT("ContactName",contact.x_contact_name),
                          XMLELEMENT("HomePhone",contact.home_ph_num),
                          XMLELEMENT("WorkPhone",contact.work_ph_num),
                          XMLELEMENT("EmailAdd", contact.email_addr)
                                        )
                             )
               FROM  SIEBEL.S_CONTACT contact,
                     SIEBEL.S_PARTY_PER spp
               WHERE contact.emp_flg = 'N'
               AND   contact.priv_flg = 'N'
               AND   spp.party_id = accnt.ROW_ID
              AND   spp.person_id = contact.row_id
              )
           )
         )
       ), VERSION '1.0', STANDALONE YES)  xml_fragment    
FROM   siebel.s_org_ext accnt
WHERE  accnt.x_doc_num    = 'S8818392A'
AND    accnt.accnt_type_cd = 'Customer'

The output would be in CLOB format.

Comments are closed.

Post Navigation