Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Generate XML from key valye map

user13117585Apr 19 2022

Hi all,
I was wondering if any of you know a better way to generate an XML data structure from a key value table?
For instance, let's take this scenario:

drop table m;

CREATE TABLE m(id number, k varchar2(10), v number);
INSERT INTO m values(1, 'code', 1);
INSERT INTO m values(1, 'lvl', 1);

INSERT INTO m values(2, 'code', 2);
INSERT INTO m values(2, 'lvl', 3);

This select produces the proper result:

SELECT XMLForest(
     id AS id, 
     MIN(CASE k WHEN 'code' THEN v END) as "code", 
     MIN(CASE k WHEN 'lvl' THEN v END) as "lvl"
    )
 FROM m
 WHERE id = 1
 GROUP BY id
;

But I've seen in documentation that PIVOT has an XML option. I'm asking because in this simple example it doesn't take long to generate the result. BUt, in real scenario, I have about 500key values and it takes a while to generate. I'm looking for options to somehow generate it in a different way...
Regards,

This post has been answered by odie_63 on Apr 19 2022
Jump to Answer
Comments
Post Details
Added on Apr 19 2022
5 comments
136 views