Oracle: How to create an element in a specific namespace with XMLElement()

Question

In Oracle, you can use the XMLElement() function to create an element, as in:

XMLElement('name', 'John')

But how to create an element in a specific namespace? For instance, how to create the following element:

<my:name xmlns:my="http://www.example.com/my"&gt;John&lt;/my:name&gt;

Answer 1

Instead of XMLElement() use:

XMLType('<my:name xmlns:my="http://www.example.com/my"&gt;John&lt;/my:name&gt;')

Yes, it is that simple.

Answer 2

You can also use XMLAttribute:

select xmlelement("my:name",
     xmlattributes('http://www.example.com/my' as "xmlns:my"),
     'John'
       )
from dual

Will return:

<my:name xmlns:my="http://www.example.com/my"&gt;John&lt;/my:name&gt;

You can also check that Oracle recognizes this as a namespace (other than you are not getting a namespace prefix "my" is not declared error):

select xmlelement("my:name",
        xmlattributes('http://www.example.com/my' as "xmlns:my"),
        'John'
       ).getnamespace()
from dual

Will return:

http://www.example.com/my