[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

XMLElement function not working as expected

Posted on 2007-11-21
4
Medium Priority
?
643 Views
Last Modified: 2011-09-20
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?
0
Comment
Question by:awking00
  • 4
4 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 20330544
xmlelement returns xmltype's

each type contains the player node you're looking for

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20330561
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.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 20330650
uset the getclobval() method of the returned xmltype's to see the contents

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

Expert Comment

by:sdstuber
ID: 20330661
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;
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question