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
)
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
;