Forum Stats

  • 3,837,028 Users
  • 2,262,221 Discussions
  • 7,900,184 Comments

Discussions

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

User_8849S
User_8849S Member Posts: 7 Green Ribbon

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

Tagged:

Best Answer

  • cormaco
    cormaco Member Posts: 1,950 Silver Crown
    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.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,580 Red Diamond

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

    SY.

  • User_8849S
    User_8849S Member Posts: 7 Green Ribbon

    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
    cormaco Member Posts: 1,950 Silver Crown
    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.