Use of extractValue function more than one time in oracle give duplicate data from first or last record for all rows

Question

Suppose I have a customer table.

And there are three columns (id [type-Number], name [type-Varchar2], detail [type-XMLTYPE]. Detail column contain address of customer in xml format including his name which is same as second column name.

First sql : Working fine [name is same from both columns]

SELECT id,
name,
extractValue(detail, '/customer/address[@type=''HOME'']/name1') name1
FROM
(
    SELECT inner1.*,
    (SELECT detail FROM customer c1 WHERE c1.id = inner1.id) detail
    FROM
    (SELECT c.id, c.name FROM customer c ) inner1
)

Output :

1   Janice  Janice
2   Rita    Rita
3   Cory    Cory

Second sql [Not working fine : name1 is duplicate for all rows from first row]

SELECT id,
name,
extractValue(detail, '/customer/address[@type=''HOME'']/name1') name1,
extractValue(detail, '/customer/address[@type=''HOME'']/name2') name2
FROM
  (
    SELECT inner1.*,
          (SELECT detail FROM customer c1 WHERE c1.id = inner1.id) detail
    FROM
      (SELECT c.id, c.name FROM customer c) inner1
  )

Output :

1   Janice  Janice  Dunn
2   Rita    Janice  Hale
3   Cory    Janice  Jones

Problem : In the second sql I only requested additional data name2 from detail column which change the out put completely , as you can see the duplicate data for name1 in output of second sql. How this behavior is possible and what are the possible solution?

The issue does not exist if I do not use attribute in xpath in sql.The sql still works and give correct data. like

extractValue(detail, '/customer/address/name1') name1,

Details

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Customer.sql

CREATE TABLE "CUSTOMER"

(   "ID" NUMBER, 
    "NAME" VARCHAR2(20 BYTE), 
    "DETAIL" "SYS"."XMLTYPE" 
   )

 XMLTYPE COLUMN "DETAIL" STORE AS SECUREFILE BINARY XML (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  CACHE  NOCOMPRESS  KEEP_DUPLICATES 
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ALLOW NONSCHEMA DISALLOW ANYSCHEMA ;   

Insert into CUSTOMER (ID,NAME,DETAIL) values (1,'Janice','<?xml version = ''1.0''?><customer>
   <customerno>1</customerno>
   <address type="HOME">
      <name1>Janice</name1>
      <name2>Dunn</name2>
      <email>janice.dunn98@example.com</email>
      <dob>5/2/1980</dob>
      <city>Barn St</city>
   </address>
</customer>');
Insert into CUSTOMER (ID,NAME,DETAIL) values (2,'Rita','<?xml version = ''1.0''?><customer>
   <customerno>2</customerno>
   <address type="HOME">
      <name1>Rita</name1>
      <name2>Hale</name2>
      <email>rita.hale40@example.com</email>
      <dob>2/2/1981</dob>
      <city>Seventh St</city>
   </address>
</customer>');
Insert into CUSTOMER (ID,NAME,DETAIL) values (3,'Cory','<?xml version = ''1.0''?><customer>
   <customerno>3</customerno>
   <address type="HOME">
      <name1>Cory</name1>
      <name2>Jones</name2>
      <email>cory.jones89@example.com</email>
      <dob>9/7/1984</dob>
      <city>Arther St</city>
   </address>
</customer>');

Please post data as formatted text, [not screenshots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) — Aleksej, May 19 '17 at 13:51

Boneist · Answer 1 · 2017-05-19T14:12:23.300

EXTRACTVALUE (and EXTRACT) is deprecated in 11g. Instead, you should be using the XMLTABLE function which was introduced in 10g, like so:

WITH customer AS (SELECT 1 ID, 'Janice' NAME, XMLTYPE('<?xml version="1.0"?>
<customer><customerno>1</customerno>
   <address type="HOME">
      <name1>Janice</name1>
      <name2>Dunn</name2>
      <email>janice.dunn98@example.com</email>
      <dob>5/2/1980</dob>
      <city>Barn St</city>
   </address>
</customer>') detail FROM dual UNION ALL
                  SELECT 2 ID, 'Rita' NAME , XMLTYPE('<?xml version="1.0"?><customer>
   <customerno>2</customerno>
   <address type="HOME">
      <name1>Rita</name1>
      <name2>Hale</name2>
      <email>rita.hale40@example.com</email>
      <dob>2/2/1981</dob>
      <city>Seventh St</city>
   </address>
</customer>') detail FROM dual UNION ALL
                  SELECT 3 ID, 'Cory' NAME , XMLTYPE('<?xml version="1.0"?><customer>
   <customerno>3</customerno>
   <address type="HOME">
      <name1>Cory</name1>
      <name2>Jones</name2>
      <email>cory.jones89@example.com</email>
      <dob>9/7/1984</dob>
      <city>Arther St</city>
   </address>
</customer>') detail FROM dual)
-- end of mimicking your customer table - you would not need the above, as you already have the table.
-- see the SQL below:
SELECT c.id,
       c.name,
       x.name1,
       x.name2
FROM   customer c
       CROSS JOIN XMLTABLE('/customer' PASSING c.detail
                           COLUMNS name1 VARCHAR2(20) PATH 'address[@type="HOME"]/name1',
                                   name2 VARCHAR2(20) PATH 'address[@type="HOME"]/name2') x;

        ID NAME   NAME1                NAME2
---------- ------ -------------------- --------------------
         1 Janice Janice               Dunn
         2 Rita   Rita                 Hale
         3 Cory   Cory                 Jones

As for your question re. why EXTRACTVALUE isn't giving the right results - when I run your exact sql statement with the same data as in my answer, I get the exact same results. I'm on 11.2.0.4, and I can only conclude that if you're getting the wrong answer, it must be a bug in your particular version of Oracle.

I'm confused as to why you're doing a scalar subquery to retrieve the detail column, though - is your xml column not in the same table as the rest of your details in your real-life database?

Use of extractValue function more than one time in oracle give duplicate data from first or last record for all rows

1 Answers1