Forum Stats

  • 3,836,825 Users
  • 2,262,197 Discussions
  • 7,900,120 Comments

Discussions

Categories

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

User_8849S
User_8849S Member Posts: 7 Green Ribbon
in General XML

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:
· Share on TwitterShare on Facebook

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.

    · Share on TwitterShare on Facebook

Answers

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

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

    SY.

    · Share on TwitterShare on Facebook
  • 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

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook