I need to check a table if there exists duplicates between the xml cells (and delete them). Up to now I use xmltable. I want to ask if there is a "better" solution available. My table has around 10 Mio. rows, so maybe there is a faster, more pure ;) xml query solution ...
I have a table with a ID column and a XML column + some more columns. Currently I use a xmltable like this example, assuming I have just 2 nodes:
DELETE FROM SCHEMA.TABLE WHERE NOT ID IN (SELECT MAX(ID) FROM SCHEMA.TABLE o, xmltable('$c/xmlpath' passing o.data as "c" COLUMNS SensoID INTEGER path 'SensoID', Sequence_NR INTEGER path 'Sequence_NR' ) AS x GROUP BY SensoID,Sequence_NR)