CODEWITHSUNDEEP
oracle-databaseoracle-apexoracle-apex-5.1bi-publisher
Ashlesh Kumar
Ashlesh Kumar

Reputation: 99

Key value pair XML generation in Oracle APEX for BI Publisher report

In Oracle APEX, I am planning to have a report query which has the required SQL to populate a template developed in BI Publisher. I need a query that needs to generate an output which is similar to the XML mentioned below.

<DOCUMENT>
    <ROWSET>
      <PRIMARY_ID>10</PRIMARY_ID>
      <KEY_VALUE>
        <Key>Apple</Key>
        <Value>2</Value>
      </KEY_VALUE>
    </ROWSET>
    <ROWSET>
       <PRIMARY_ID>20</PRIMARY_ID>
       <KEY_VALUE>
        <Key>Orange</Key>
        <Value>5</Value>
       </KEY_VALUE>
    </ROWSET>
</DOCUMENT>

Please note that this query will be placed in "Report Query" section of Oracle APEX 5.1 version.

Currently, I was able to write an SQL query which could generate XML as below. The difference being there is NO "KEY_VALUE" tag enclosing the "Key" and "Value".

<DOCUMENT>
    <ROWSET>
      <PRIMARY_ID>10</PRIMARY_ID>
      <Key>Apple</Key>
      <Value>2</Value>
    </ROWSET>
    <ROWSET>
       <PRIMARY_ID>20</PRIMARY_ID>
       <Key>Orange</Key>
       <Value>5</Value>
    </ROWSET>
</DOCUMENT>

This is the query I came up with.

SELECT 
    distinct v.id id,
    (Select 'Status' from dual)  "KEY",
    (Select v.status from dual)  "Value"
FROM
    .......

Upvotes: 0

Views: 339

Answers (1)

Ashlesh Kumar
Ashlesh Kumar

Reputation: 99

Finally, I was able to come up with the Query that generates the output that I was expecting (I am sure there might be a better way, but with the time frames that I had this is the best that I could come up with, may be we can get some better solutions in this post).

I created a view with this SQL query below and used this view in Oracle APEX BI publisher.

Select * from 
    (SELECT PRIMARY_ID, 'Apple' as Key, item_count as value
     FROM xxc_test where item_id = 45
     UNION
     SELECT PRIMARY_ID, 'Orange' as Key, item_count as value
     FROM xxc_test where item_id = 46
     UNION
     SELECT PRIMARY_ID, 'Grapes' as Key, item_count as value
     FROM xxc_test where item_id = 47);

Upvotes: 0

Related Questions