Database Software

I have 2 XML files that each have a collection of a 3 Complex Types which have an ID that I want to make unique within all XML Complex Type instances.

I tried lot's of things, but the simple query below comes the closest, except it only makes them unique between files.


SQL> UPDATE SIMPLE_XPOIS sx
2 SET value(sx) = updatexml(value(sx),'/Simple_XPOIS/Simple/ID/text()', rownum);

2 rows updated.

Elapsed: 00:00:00.13
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> select
2 extractValue(value(s), '/Simple/ID') as ID,
3 extractValue(value(s), '/Simple/VendorName') as VENDORNAME
4 from
5 SIMPLE_XPOIS,
6 table(XMLSequence(extract(OBJECT_VALUE, '/Simple_XPOIS/Simple'))) s;

ID
----------
VENDORNAME
--------------------------------------------------------------------------------
1
Vendor4

1
Vendor5

1
Vendor6


ID
----------
VENDORNAME
--------------------------------------------------------------------------------
2
Vendor1

2
Vendor2

2
Vendor3


Is there a way to do this? Maybe by making use of RESOURCE_VIEW, though I tried that but didn't see how to accomplish this.