Home » SQL & PL/SQL » SQL & PL/SQL » How to use Distinct clause to create the XML from SQLXML
How to use Distinct clause to create the XML from SQLXML [message #197550] Wed, 11 October 2006 13:21 Go to next message
raghu.nm
Messages: 5
Registered: October 2006
Location: US
Junior Member
Hello ,

I am trieng to buil a xml by using distinct clause.

create table employee ( employee_id number,employee_name varchar2 (20),employee_manager (20));

insert into employee (1,'ralph','nm');
insert into employee (2,'rob','nm');
insert into employee (3,'nm','russ');

i want create xml by using the query "select distinct employee_manager from employee"
when i try below query it errors out as ORA-22950: cannot ORDER objects without MAP or ORDER method

SELECT DISTINCT
XMLELEMENT(name "" XMLATTRIBUTES(employee_manager as "Manager")) FROM EMPLOYEE;

please do help
regards,
raghu



Re: How to use Distinct clause to create the XML from SQLXML [message #197635 is a reply to message #197550] Thu, 12 October 2006 02:12 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hint:
When you do post Create Table and Insert statements, could you actually post ones that work? I mean it would have taken seconds for you to actually test the commands you wrote, but apparantly you couldn't be bothered.
You're missing a VARCHAR2 from the create table statement, a VALUES from each insert, and when I run the SQL you posted, the actual error is
SQL> SELECT DISTINCT
  2  XMLELEMENT(name "" XMLATTRIBUTES(employee_manager as "Manager")) FROM EMPLOYEE; 
ERROR:
ORA-01741: illegal zero-length identifier


Now, I can get the error you report by doing this (once I've fixed the rest of it:
  1  SELECT DISTINCT
  2* XMLELEMENT(name "Name", XMLATTRIBUTES(employee_manager as "Manager")) FROM EMPLOYEE
SQL> /
XMLELEMENT(name "Name", XMLATTRIBUTES(employee_manager as "Manager")) FROM EMPLOYEE
                *
ERROR at line 2:
ORA-22950: cannot ORDER objects without MAP or ORDER method

This is because there is no MAP or ORDER operator defined for the XMLTYPE object. Perhaps there should be, but that's the way of things.



There's an easy fix (once I've fixed the XMLELEMENTS to return what I think you're looking for - you didn't specify what you were trying to get):
Do a distinct on the source:
SQL> SELECT XMLELEMENT("Name" , XMLATTRIBUTES(employee_manager as "Manager"), employee_name )
  2  from  (select distinct * from employee);

XMLELEMENT("NAME",XMLATTRIBUTES(EMPLOYEE_MANAGERAS"MANAGER"),EMPLOYEE_NAME)
-----------------------------------------------------------------------------------------------
<Name Manager="nm">ralph</Name>
<Name Manager="nm">rob</Name>
<Name Manager="russ">nm</Name>

Previous Topic: How to read a text file ?
Next Topic: floating value
Goto Forum:
  


Current Time: Fri Dec 02 23:04:09 CST 2016

Total time taken to generate the page: 0.13392 seconds