CODEWITHSUNDEEP
sqloracle-database
Ti J
Ti J

Reputation: 271

Get rows with duplicate xml values in Oracle SQL

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:

  1. Same Product Type "productType"
  2. Same Status "status"
  3. 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>

Upvotes: 0

Views: 266

Answers (1)

Sachin Padha
Sachin Padha

Reputation: 221

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;

Upvotes: 1

Related Questions