Blog Oracle PL/SQL

oracle xml nested elements

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.