Search code examples
sqloracleplsql

How to create XML from a string with list of values

How to create XML like

<person>
    <item value="Emma"/>
    <item value="John"/>
</person>

from string like Emma;John with Oracle SQL or PL/SQL?

update:

The final solution taking into account the advice of @p3consulting and @Charlieface:

select
    XMLElement("person",
        XMLAgg(
            XMLElement("item",
                XMLAttributes(str as "value")
            )
        )
    ).getStringVal()
from
    (
    select regexp_substr('Emma;John', '[^;]+', 1, level) as str
    from dual
    connect by regexp_substr('Emma;John', '[^;]+', 1, level) is not null
    )
Solution 
  • with strings(s) as (
    select 'Emma;John' from dual
)
select 
    xmlelement("person",
        xmlelement("item", xmlattributes( substr(s, 1, instr(s, ';')-1) as "value")),
        xmlelement("item", xmlattributes( substr(s, instr(s, ';')+1) as "value"))
)
from strings
;