asked on

XMLElement function not working as expected

I have a table called players with the following attributes and values:

PLAYER_ID PLAYER_NAME
---------- ------------
1 Smith
2 Jones
3 Wilson

When I issued this query,
select XMLElement("Player",
XMLElement("PlayerID",player_id),
XMLElement("PlayerName",player_name))
from players;

I expected to see results something like this

<Player>
<PlayerID>1</PlayerID>
<PlayerName>Smith</PlayerName>
</Player>
<Player>
<PlayerID>2</PlayerID>
<PlayerName>Jones</PlayerName>
</Player>
<Player>
<PlayerID>3</PlayerID>
<PlayerName>Wilson</PlayerName>
</Player>

but what actually returned was

XMLTYPE()
XMLTYPE()
XMLTYPE()

Is there something I need to do to get this to work?

Sean Stuber

xmlelement returns xmltype's

each type contains the player node you're looking for

Sean Stuber

btw, off topic, please post your code about the iso weeks, I never did figure out what you were doing with the function based index that pertained to that question.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

And if you wanted all of the rows to be inside a single xml doc,
then use xmlagg to aggregate them.

and, that too returns an xmltype, so use getclobval on the xmlagg's returned value to see the combined xml

select XMLAGG(XMLElement("Player",
XMLElement("PlayerID",player_id),
XMLElement("PlayerName",player_name))
).getclobval()
from players;