Skip to Main Content
Forums

DevOps, CI/CD and Automation

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!

How to generate the xml (group of tags) with multiple times??

User_8849SMar 12 2022

Hi Team,
I'm having the parameter's list in one of the master detail table, I want to generate the xml tags for Parameter name & value. Could you please help us on this?
Xml:

<pub:item>
   <pub:name>p_from_date</pub:name>
<pub:values>
   <pub:item>10-01-2019</pub:item>
</pub:values>
</pub:item>

I want to generate the above xml with multi records

Regards,
Praveen
This post has been answered by cormaco on Mar 12 2022
Jump to Answer

Comments

Solomon Yakobson

Provide create master detail table statement along with insert statements for sample data and expected results
SY.

User_8849S

Table Script:
CREATE TABLE XX_MASTER_DATA_PARAM (PARAM_NAME VARCHAR2(100), PARAM_VALUE VARCHAR2(100))
Insert Data:
INSERT INTO "RACE"."XX_MASTER_DATA_PARAM" (PARAM_NAME, PARAM_VALUE) VALUES ('P_FROM_DATE', '01-JAN-2022');
INSERT INTO "RACE"."XX_MASTER_DATA_PARAM" (PARAM_NAME, PARAM_VALUE) VALUES ('P_PERSON_ID', '70023');
INSERT INTO "RACE"."XX_MASTER_DATA_PARAM" (PARAM_NAME, PARAM_VALUE) VALUES ('P_TO_DATE', '31-DEC-2022');
I want the xml set for 3 time with these values

cormaco
Answer

Like this:

select
    xmlagg(
        xmlelement("pub:item",
            xmlelement("pub:name",param_name),
            xmlelement("pub:values",
                xmlelement("pub:item",param_value)
            )
        )
    )
from xx_master_data_param

Output (pretty printed):

<pub:item>
    <pub:name>P_FROM_DATE</pub:name>
    <pub:values>
        <pub:item>01-JAN-2022</pub:item>
    </pub:values>
</pub:item>
<pub:item>
    <pub:name>P_PERSON_ID</pub:name>
    <pub:values>
        <pub:item>70023</pub:item>
    </pub:values>
</pub:item>
<pub:item>
    <pub:name>P_TO_DATE</pub:name>
    <pub:values>
        <pub:item>31-DEC-2022</pub:item>
    </pub:values>
</pub:item>

Note that this an incomplete XML fragment, it's missing a root tag and the defintion of the pub namespace.
By the way: Storing dates as varchars is generally a bad idea.

Marked as Answer by User_8849S · Mar 13 2022
1 - 3

Post Details

Added on Mar 12 2022
#general-xml, #pl-sql, #plsql, #sql, #xml
3 comments
451 views