Home » SQL & PL/SQL » SQL & PL/SQL » XMLType Column Comparison in Select Query (Oracle11g)
XMLType Column Comparison in Select Query [message #674768] Thu, 14 February 2019 17:29 Go to next message
sotu100
Messages: 1
Registered: February 2019
Junior Member
Hello,

Attached is the database schema that I have.

My requirement is to get all the IDs which :



    exists more than one AND
    <ORDER> is same for all of them AND
    <ITEM> is same for all of them AND
    <MATERIAL> is same for all of them

For the data that I have provided, the query output should be :

ID IDLANE IDDATE
111 111R1 2019-02-10T10:00:00
111 111R2 2019-02-10T11:00:00

It wont include 222 because both 222 IDs have different Material.

I wrote the query to identify more than one ID as below:

SELECT ID FROM IDTABLE
GROUP BY ID
HAVING COUNT(*) > 1

Above query gives me 111 & 222 as answers because both of the IDs exist more than one. But I need to compare XMLTyple COlumn's , and too with each same ID appearing twice. But I do not know how to compare XML Type columns.

Can anyone provide some inputs.

Thanks
Re: XMLType Column Comparison in Select Query [message #674769 is a reply to message #674768] Thu, 14 February 2019 17:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

>Attached is the database schema that I have.
Please post details INLINE & not as attachment
Re: XMLType Column Comparison in Select Query [message #674770 is a reply to message #674768] Fri, 15 February 2019 01:05 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Quote:
Attached is the database schema that I have.
Quote:
Please post details INLINE & not as attachment
I don't see not even any attachments.
Re: XMLType Column Comparison in Select Query [message #674771 is a reply to message #674768] Fri, 15 February 2019 01:06 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Attached is the database schema that I have
As far as I can see nothing attached.

[Updated on: Fri, 15 February 2019 01:07]

Report message to a moderator

Previous Topic: Add new columns and update to 600MB table online mode with zero downtime
Next Topic: Displaying rows as columns and grouping data
Goto Forum:
  


Current Time: Thu Mar 28 16:53:33 CDT 2024