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,