Get rows with duplicate xml values in Oracle SQL

Question

I'd like to get a list of product Ids in my table that has duplicate XML values in CLOB. How can I achieve that in Oracle SQL? Records only with duplicate values in the XML must be returned by the query.

Duplicate products are:

Same Product Type "productType"
Same Status "status"
Same Location "location"

Below is the sample XML stored in the CLOB: List of products is not limited to 6 only, it could be many.

Table: PRODUCT, Primary Key: PR_ID

I just wanted to get the records that contains duplicate products so I can remove duplicates later on.

<productInfo>
    <product>
        <productType>FRNC</productType>
        <status>ACTV</status>
        <location>BASEMENT</location>
    </product>
    <product>
        <productType>FRNC</productType>
        <status>ACTV</status>
        <location>BASEMENT</location>
    </product>
    <product>
        <productType>FRNC</productType>
        <status>ACTV</status>
        <location>BASEMENT</location>
    </product>
    <product>
        <productType>ABCD</productType>
        <status>ACTV</status>
        <location>BASEMENT</location>
    </product>
     <product>
        <productType>ABCD</productType>
        <status>ACTV</status>
        <location>BASEMENT</location>
    </product>
     <product>
        <productType>RCFT</productType>
        <status>ACTV</status>
        <location>BASEMENT</location>
    </product>
</productInfo>

You've only shown a single value, and you haven't shown the output you expect (or what you've tried and what is wrong with that). Please include more representative data and what those should produce. Are you looking for duplicates within a single CLOB/XML, and just whether any exist or actually listing them (all of the produce nodes in your example include duplicates); or between different CLOB/XML values in different rows in your table? — Alex Poole
– Alex Poole, Commented Aug 5, 2022 at 7:39

Sachin Padha · Accepted Answer · 2022-08-05 05:46:44Z

In PLSQL code, you can use FOR Loop and assign the dynamic value of the Index. Sample SQL Queries for your reference.

SELECT Xmltype(t.Xml).Extract('/productInfo/product[1]/productType/text()').Getstringval() Product_Type1,
       Xmltype(t.Xml).Extract('/productInfo/product[2]/productType/text()').Getstringval() Product_Type2,
       Xmltype(t.Xml).Extract('/productInfo/product[3]/productType/text()').Getstringval() Product_Type3,
       Xmltype(t.Xml).Extract('/productInfo/product[4]/productType/text()').Getstringval() Product_Type4,
       Xmltype(t.Xml).Extract('/productInfo/product[5]/productType/text()').Getstringval() Product_Type5,
       Xmltype(t.Xml).Extract('/productInfo/product[6]/productType/text()').Getstringval() Product_Type6
  FROM Xml_Test t;

Same you can do it for Location and other nodes. Use FOR LOOP and iterate it till the length of Nodes.

PLSQL Sample Code. Please test it properly before using it for PROD data.

DECLARE
  l_Pr_Type    VARCHAR2(20);
  l_Pr_Loc     VARCHAR2(20);
  l_Pr_Status  VARCHAR2(20);
  l_Pr_Combine VARCHAR2(60) := NULL;
BEGIN
  FOR i IN 1 .. 6
  LOOP
    SELECT Xmltype(t.Xml).Extract('/productInfo/product[' || i || ']/productType/text()')
           .Getstringval()
      INTO l_Pr_Type
      FROM Xml_Test t;
  
    SELECT Xmltype(t.Xml).Extract('/productInfo/location[' || i || ']/productType/text()')
           .Getstringval()
      INTO l_Pr_Loc
      FROM Xml_Test t;
  
    SELECT Xmltype(t.Xml).Extract('/productInfo/status[' || i || ']/productType/text()')
           .Getstringval()
      INTO l_Pr_Status
      FROM Xml_Test t;
  
    IF (l_Pr_Combine IS NOT NULL)
    THEN
      IF (l_Pr_Combine = l_Pr_Type || l_Pr_Loc || l_Pr_Status)
      THEN
        Dbms_Output.Put_Line('Duplicate Records for Node:' || i);
      END IF;
    END IF;
    l_Pr_Combine := l_Pr_Type || l_Pr_Loc || l_Pr_Status;
  
  END LOOP;
END;

Collectives™ on Stack Overflow

Get rows with duplicate xml values in Oracle SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related