CODEWITHSUNDEEP
xmloracle-databaseplsqloracle10g
wadesworld
wadesworld

Reputation: 13733

Using Oracle XMLElement in complex PL/SQL

Using Oracle 10gR2, I need to produce something like the following pseudo-example from data stored in standard relational tables.

<product>
  <productName>p1</productName>
  <productNumber>100</productNumber>
  <productObsoletes>
     <obsoletedProduct label=1>50</obsoletedProduct>
     <obsoletedProduct label=2>55</obsoletedProduct>
  </productObsoletes>
</product>

The problem is, I need to do decision making using the rows which contain the data. My database (which I inherited) is poorly designed and the logic needed to decide upon a rows inclusion is complex. Unfortunately, redesigning the database is not an option. I'm vastly simplifying the logic here, so it's not a case where a simple join or where clause can be used. There's a complex hierarchy to the data, and the data format definition.

In psuedo code, the way-oversimplified decision would look something like:

BEGIN
   --select our basic attributes

     select XMLEMENT("product",XMLELEMENT("productName",name),XMLELEMENT("productNumber",product_number))
into xml_output from products where product_number = 100;

   --now process our obsolete rows
   select XMLELEMENT("productObsoletes") into xml_output from dual;
   FOR c_row in (select * from product_obsoletes where id=100)
   LOOP
       IF c_row.display = 'YES' THEN
        select XMLELEMENT("obsoletedProduct", XMLATTRIBUTES(c_row.label as "label"), c_row.obsoleted_product_id) into xml_output from dual;
       ELSE
          CONTINUE;
       END IF;
   END LOOP;
END;

Obviously though, this doesn't work. First, XMLElement always puts a closing tag on, so unless you can formulate a single select statement containing all of your elements, it's not going to work. Second, in this example, I'd be overwriting the previous XMLELEMENT output. I can't concatenate, because XMLELEMENT has already closed the tag.

The only solution I can think of at this point is to select the various XMLElement pieces I need into different VARCHARS and then use string manipulation functions to find the right spot to insert the various pieces. Ugly with a capital-U.

Is there another solution? Is there perhaps a way to prevent XMELEMENT from closing the tag so that you can you use complex looping logic? Or another Oracle construct which would allow me to achieve my goal of building up this XML without having to use a single select statement?

My path of last resort would be to do the XML generation from another language such as Java or Perl where I could simply fetch the data and then do my complex processing and XML generation there. However, I'd much prefer just to have a SQL function which would return the correct XML as a string, so if it's possible to do it in PL/SQL, that'd be my preferred route.

Upvotes: 2

Views: 21280

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16905

I think this can be done as a query, you'll have to combine xmlagg xmlelement and xmlforest

Here is an example:

select XMLELEMENT("product",(XMLForest( p.product_name as "productName",
                            p.product_number as "productNumber",
                  xmlagg(
  XMLELEMENT("obsoletedProduct", 
             XMLATTRIBUTES(po.label as "label"), 
             po.obsoleted_product_id)) as "productObsoletes")))
from products p 
join product_obsoletes po on p.product_number=po.id
group by p.product_name, p.product_number

and here is the fiddle

Upvotes: 3

cagcowboy
cagcowboy

Reputation: 30838

How about using XMLDOM (a different inbuilt package) to create the XML instead?

You'll probably need to write more code, but if your logic is sufficiently complicated this is what you're going to have to do anyway.

This is a good quick summary of a few options you could use (including XMLDOM), with examples:

http://www.akadia.com/services/ora_gen_xml.html

Upvotes: 2

Related Questions