Home » Developer & Programmer » JDeveloper, Java & XML » duplicate records after xml extract (2 threads merged by bb) (Oracle 10g)
duplicate records after xml extract (2 threads merged by bb) [message #497465] Sat, 05 March 2011 13:53 Go to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
Hi

I've got a table with XML code (CLOB column) and I need to read and write every of parameter(blue marked below) and value (red marked below) record from that.

For example:
create table tmp_mape
(msisdn varchar2(100),
xml_params CLOB )

insert into tmp_mape values
('423903200200',
'<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>410</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>' );

insert into tmp_mape values
('423903200200',
'<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>003004020000c7a1</value></parameter></parameters>');

insert into tmp_mape values
('423903200200',
'<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>110</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>');

insert into tmp_mape values
('423903200200',
'<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>220</value></parameter><parameter name="isDropped"><value>false</value></parameter></parameters>');




<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
<parameter name="id"><value>110</value></parameter>
<parameter name="isDropped"><value>true</value></parameter>
</parameters>


I tried to read this every values of xml code by select below:

select 
extractValue(value(x),'/parameter/@name',    'xmlns="http://datalan.sk/webreporting/params/v1_0"') b,
extractValue(value(x),'/parameter/value',    'xmlns="http://datalan.sk/webreporting/params/v1_0"') a,
a.*
  from   tmp_mape a,
     TABLE (
             XMLSEQUENCE (
                EXTRACT (
                   xmltype (xml_params),
                   '/parameters/parameter',
                   'xmlns="http://datalan.sk/webreporting/params/v1_0"')  )) x 



This select returns a duplicate records because of more the one parameter and value records from XML column.

Could you please take a look at that and tell me how to do that?

Thanks

[Updated on: Sat, 05 March 2011 13:56]

Report message to a moderator

Re: duplicate records after xml extract [message #497486 is a reply to message #497465] Sat, 05 March 2011 16:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11gR2> column msisdn    format a12
SCOTT@orcl_11gR2> column id	   format a20
SCOTT@orcl_11gR2> column isdropped format a20
SCOTT@orcl_11gR2> select a.msisdn,
  2  	    extractValue
  3  	      (value(x),
  4  	       '/parameters/parameter[1]/value',
  5  	       'xmlns="http://datalan.sk/webreporting/params/v1_0"') id,
  6  	    extractValue(value(x),
  7  	      '/parameters/parameter[2]/value',
  8  	      'xmlns="http://datalan.sk/webreporting/params/v1_0"') isdropped
  9  from   tmp_mape a,
 10  	    TABLE (
 11  	      XMLSEQUENCE (
 12  		EXTRACT (
 13  		  xmltype (xml_params),
 14  		  '/parameters',
 15  		  'xmlns="http://datalan.sk/webreporting/params/v1_0"')  )) x
 16  /

MSISDN       ID                   ISDROPPED
------------ -------------------- --------------------
423903200200 410                  true
423903200200 003004020000c7a1
423903200200 110                  true
423903200200 220                  false

4 rows selected.

SCOTT@orcl_11gR2>

Re: duplicate records after xml extract [message #497535 is a reply to message #497486] Sun, 06 March 2011 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without assuming the order of values is always the same one:
SQL> select a.msisdn,
  2         extractValue (value(x), '/parameters/parameter[@name="id"]/value',
  3                       'xmlns="http://datalan.sk/webreporting/params/v1_0"') id,
  4         extractValue (value(x), '/parameters/parameter[@name="isDropped"]/value',
  5                       'xmlns="http://datalan.sk/webreporting/params/v1_0"') isdropped
  6  from tmp_mape a,
  7       TABLE (XMLSEQUENCE (EXTRACT (xmltype (xml_params), '/parameters',
  8                                    'xmlns="http://datalan.sk/webreporting/params/v1_0"')
  9             )            ) x
 10  /
MSISDN       ID                   ISDROPPED
------------ -------------------- --------------------
423903200200 410                  true
423903200200 003004020000c7a1
423903200200 110                  true
423903200200 220                  false

Regards
Michel
Re: duplicate records after xml extract [message #497536 is a reply to message #497486] Sun, 06 March 2011 00:26 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
You hit up on a a solution.

Thanks a lot Barbara

Best Regards
Re: duplicate records after xml extract [message #497539 is a reply to message #497536] Sun, 06 March 2011 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does this mean you ignore my posts?

Regards
Michel
Re: duplicate records after xml extract [message #497545 is a reply to message #497539] Sun, 06 March 2011 01:37 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
I didnt read your post before I sent my comment to Barbara.
Your solution really works too, thanks Michel.
Re: duplicate records after xml extract [message #497626 is a reply to message #497545] Sun, 06 March 2011 10:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Michel's solution is better, because as he said, it does not rely on the parameters always being in the same order.
If the parameters are not in the same order, then my solution could produce incorrect results.
So, you should use Michel's solution instead of mine.

[Updated on: Sun, 06 March 2011 14:27] by Moderator

Report message to a moderator

Re: duplicate records after xml extract [message #498053 is a reply to message #497626] Tue, 08 March 2011 07:57 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member

when I want to insert these values into the table I got an error: ORA-01722: invalid number

source table:
    create table tmp_mape_load
          (msisdn varchar2(100),
          id number,
          isdropped  varchar2(100)  )


And insert simply looks like:
 insert into tmp_mape_load
     select a.msisdn,
            extractValue
              (value(x),
               '/parameters/parameter[1]/value',
               'xmlns="http://datalan.sk/webreporting/params/v1_0"') id,
            extractValue(value(x),
              '/parameters/parameter[2]/value',
              'xmlns="http://datalan.sk/webreporting/params/v1_0"') isdropped
    from   tmp_mape a,
           TABLE (
             XMLSEQUENCE (
           EXTRACT (
             xmltype (xml_params),
             '/parameters',
  		  'xmlns="http://datalan.sk/webreporting/params/v1_0"')  )) x


Do you have any idea how to solve it out?

Thanks
Re: duplicate records after xml extract [message #498055 is a reply to message #498053] Tue, 08 March 2011 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
003004020000c7a1 is not a number.

Regards
Michel
Re: duplicate records after xml extract [message #498057 is a reply to message #498055] Tue, 08 March 2011 08:27 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member

you right Smile
Re: duplicate records after xml extract [message #498656 is a reply to message #498057] Thu, 10 March 2011 13:36 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
Ive got a another one question.

The source table the_mape consists not only xml codes in the xml_params column.
For instance:
insert into tmp_mape values
('423903200200', null)


When I use the Michels select(see under) I got an error:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 1



Is it a way how to use select query for non xml records in that column?

The column xml_params has got on the one hand xml code and on the other non xml code.

Thanks

[Updated on: Thu, 10 March 2011 13:42]

Report message to a moderator

Re: duplicate records after xml extract [message #498660 is a reply to message #498656] Thu, 10 March 2011 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is NULL use DECODE.
It is is not NULL, afaik, no you can't.

Regards
Michel
Re: duplicate records after xml extract [message #498800 is a reply to message #498660] Fri, 11 March 2011 04:12 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member

I struggle to use select below but nothing returns into the X_ID column.
Could you please take a look at that what is wrong?

    select t2.X_ID ,
    t.xml_params
    from  tmp_mape  t ,
          XMLTABLE(XMLNAMESPACES(default 'http://datalan.sk/webreporting/params/v1_0' ),
               'for $i in /*
                  return <datatype name="{$i/name()  }">
                          {
                            for $j in $i/*
                            return $j
                          }
                         </datatype>'
               passing xmltype( XML_PARAMS)
               columns
  X_ID varchar2(100) PATH '/datatype/parameters/parameter[@name="id"]/text()'
            ) t2   


Do you think this xml parsing should be faster than what you wrote before?
I'got a problem to parse xml code due to large table (3.49 Gb).
Do you have idea how to make a faster parse of table with xml?

Thanks

Re: duplicate records after xml extract [message #498926 is a reply to message #498800] Fri, 11 March 2011 23:25 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member

Does anybody know to give me advice?
faster parse of table with xml [message #499000 is a reply to message #497465] Sat, 12 March 2011 12:50 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
I struggle to use select below but nothing returns into the X_ID column.
Could you please take a look at that what is wrong?
create table tmp_mape
(msisdn varchar2(100),
xml_params CLOB )

insert into tmp_mape values
('423903200200',
'<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>410</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>' );



and sql query:
    select t2.X_ID ,
    t.xml_params
    from  tmp_mape  t ,
          XMLTABLE(XMLNAMESPACES(default 'http://datalan.sk/webreporting/params/v1_0' ),
               'for $i in /*
                  return <datatype name="{$i/name()  }">
                          {
                            for $j in $i/*
                            return $j
                          }
                         </datatype>'
               passing xmltype( XML_PARAMS)
               columns
  X_ID varchar2(100) PATH '/datatype/parameters/parameter[@name="id"]/text()'
            ) t2


I'got a problem to parse xml code due to large table (3.49 Gb).
Do you have idea how to make a faster parse of table with xml?

Thanks
Re: faster parse of table with xml [message #499001 is a reply to message #499000] Sat, 12 March 2011 12:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Where did you get this syntax from? Please provide a link.

Re: faster parse of table with xml [message #499005 is a reply to message #499001] Sat, 12 March 2011 13:13 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
I saw it in the one sql by Oracle administrator without any comments.
So it looks like a faster method of xml parsing.
What do you think?

[Updated on: Sat, 12 March 2011 13:14]

Report message to a moderator

Re: faster parse of table with xml [message #499007 is a reply to message #499005] Sat, 12 March 2011 13:21 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member

I've got a big table (3.49 Gb) and I have to make a parse of xml.
I used this plsql below but it has run almost 8 hours and not finished yet. So I stop it.

That's why I think about to use a better sql (in the cursor) of xml parse.

Do you have any ide how to make it faster?

DECLARE

TYPE rowids IS TABLE OF ROWID;
 row1 rowids;
TYPE VARCHARsTab IS TABLE OF VARCHAR2(250);
d1 VARCHARsTab;
x1 VARCHARsTab;
r1 VARCHARsTab;
i1  VARCHARsTab;
l1  VARCHARsTab;
s1 VARCHARsTab;

CURSOR c_xml  IS  select /*+ parallel(a 8) */  
                                  extractValue (value(x), '/parameters/parameter[@name="id"]/value',
                                                 'xmlns="http://datalan.sk/webreporting/params/v1_0"') idd,
                                   extractValue (value(x), '/parameters/parameter[@name="isDropped"]/value',
                                                 'xmlns="http://datalan.sk/webreporting/params/v1_0"') isdropped,
                                   extractValue (value(x), '/parameters/parameter[@name="serviceId"]/value',
                                                 'xmlns="http://datalan.sk/webreporting/params/v1_0"')  serviceId   ,
                                  extractValue (value(x), '/parameters/parameter[@name="X"]/value',
                                                 'xmlns="http://datalan.sk/webreporting/params/v1_0"')  x ,
                                 extractValue (value(x), '/parameters/parameter[@name="result"]/value',
                                                 'xmlns="http://datalan.sk/webreporting/params/v1_0"')  result ,
                               extractValue (value(x), '/parameters/parameter[@name="loginMessage"]/value',
                                                                  'xmlns="http://datalan.sk/webreporting/params/v1_0"')  loginMessage,
                             a.rowid rid
                         from  tmp_mape a ,
                          TABLE (
                                    XMLSEQUENCE (
                                  EXTRACT (
                                    xmltype ( xml_params),
                                    '/parameters',
                                   'xmlns="http://datalan.sk/webreporting/params/v1_0"')  )) x
                        where 1=1
                          and ( existsNode(value(x),'/parameters/parameter[@name="id"]/value',
                                                                  'xmlns="http://datalan.sk/webreporting/params/v1_0"')=1 
                        or existsNode(value(x),'/parameters/parameter[@name="isDropped"]/value',
                                                                  'xmlns="http://datalan.sk/webreporting/params/v1_0"')=1 
                        or existsNode(value(x),'/parameters/parameter[@name="serviceId"]/value',
                                                                  'xmlns="http://datalan.sk/webreporting/params/v1_0"')=1 
                        or existsNode(value(x),'/parameters/parameter[@name="X"]/value',
                                                                  'xmlns="http://datalan.sk/webreporting/params/v1_0"')=1
                        or existsNode(value(x),'/parameters/parameter[@name="result"]/value',
                                                                  'xmlns="http://datalan.sk/webreporting/params/v1_0"')=1 
                        or existsNode(value(x),'/parameters/parameter[@name="loginMessage"]/value',
                                                                  'xmlns="http://datalan.sk/webreporting/params/v1_0"')=1    )   ;
                        
                      
      
BEGIN
OPEN c_xml;
  WHILE TRUE LOOP
  FETCH c_xml BULK COLLECT INTO i1, d1, s1, x1, r1, l1,  row1  LIMIT 20000;
  EXIT WHEN row1.COUNT = 0;

   FORALL indx IN row1.FIRST..row1.LAST 

   UPDATE tmp_mape
         SET idd = i1(indx),
               isdropped= d1(indx),
              serviceid= s1(indx),
              x= x1(indx),
              result= r1(indx),
              loginmessage=l1(indx)
       WHERE ROWID = row1(indx);
        COMMIT;

  
  END LOOP;
  CLOSE c_xml;
  
END;

[Updated on: Sat, 12 March 2011 13:22]

Report message to a moderator

Re: faster parse of table with xml [message #499011 is a reply to message #499005] Sat, 12 March 2011 14:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
mape wrote on Sat, 12 March 2011 11:13

I saw it in the one sql by Oracle administrator without any comments.
So it looks like a faster method of xml parsing.
What do you think?


What do you mean "the one sql by Oracle administrator"? Is that a book or are you referring to some Administrator's guide in the online documentation or something else? If it is not a book, then please provide a link, as previously requested.


Re: faster parse of table with xml [message #499013 is a reply to message #499011] Sat, 12 March 2011 14:41 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member

Is it an important to know for you when I get that sql?
Just in the one select from our support guy but without comments. Im not able to ask him about that Sad
Re: faster parse of table with xml [message #499015 is a reply to message #499013] Sat, 12 March 2011 15:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
mape wrote on Sat, 12 March 2011 12:41

Is it an important to know for you when I get that sql?
Just in the one select from our support guy but without comments. Im not able to ask him about that :(


If you could provide a working example that uses that syntax, then perhaps I could understand what it is doing and adapt it. I have seen some similar things but nothing exactly like that. I believe you have a mixture of mismatched pieces of generic representations of syntax, variable names from another problem, and your problem.



Re: faster parse of table with xml [message #499016 is a reply to message #499015] Sat, 12 March 2011 15:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Have you tested a simple update, as demonstrated below? Typically, pure sql is faster than pl/sql.

SCOTT@orcl_11gR2> create table tmp_mape
  2    (msisdn	      varchar2(100),
  3  	xml_params    CLOB,
  4  	idd	      varchar2(16),
  5  	isdropped     varchar2(10),
  6  	serviceid     varchar2(10),
  7  	x	      varchar2(10),
  8  	result	      varchar2(10),
  9  	loginmessage  varchar2(10))
 10  /

Table created.

SCOTT@orcl_11gR2> insert into tmp_mape (msisdn, xml_params) values
  2  ('423903200200',
  3  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>410</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>' );

1 row created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> insert into tmp_mape (msisdn, xml_params) values
  2  ('423903200200',
  3  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>003004020000c7a1</value></parameter></parameters>');

1 row created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> insert into tmp_mape (msisdn, xml_params) values
  2  ('423903200200',
  3  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>110</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>');

1 row created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> insert into tmp_mape (msisdn, xml_params) values
  2  ('423903200200',
  3  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>220</value></parameter><parameter name="isDropped"><value>false</value></parameter></parameters>');

1 row created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> insert into tmp_mape (msisdn, xml_params) values
  2  ('423903200200',
  3  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="other"><value>003004020000c7a1</value></parameter></parameters>');

1 row created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> UPDATE tmp_mape tm
  2  SET    (idd, isdropped, serviceid, x, result, loginmessage) =
  3  	    (select /*+ parallel(a 8) */
  4  	      extractValue
  5  		(value(x),
  6  		 '/parameters/parameter[@name="id"]/value',
  7  		 'xmlns="http://datalan.sk/webreporting/params/v1_0"') idd,
  8  	      extractValue
  9  		(value(x),
 10  		 '/parameters/parameter[@name="isDropped"]/value',
 11  		 'xmlns="http://datalan.sk/webreporting/params/v1_0"') isdropped,
 12  	      extractValue
 13  		(value(x),
 14  		 '/parameters/parameter[@name="serviceId"]/value',
 15  		 'xmlns="http://datalan.sk/webreporting/params/v1_0"')	serviceId   ,
 16  	      extractValue
 17  		(value(x),
 18  		 '/parameters/parameter[@name="X"]/value',
 19  		 'xmlns="http://datalan.sk/webreporting/params/v1_0"')	x ,
 20  	      extractValue
 21  		(value(x),
 22  		 '/parameters/parameter[@name="result"]/value',
 23  		 'xmlns="http://datalan.sk/webreporting/params/v1_0"')	result ,
 24  	      extractValue
 25  		(value(x),
 26  		 '/parameters/parameter[@name="loginMessage"]/value',
 27  		 'xmlns="http://datalan.sk/webreporting/params/v1_0"')	loginMessage
 28  	    from
 29  	      TABLE
 30  		(XMLSEQUENCE
 31  		  (EXTRACT
 32  		    (xmltype (tm.xml_params),
 33  		     '/parameters',
 34  		     'xmlns="http://datalan.sk/webreporting/params/v1_0"')  )) x)
 35  /

5 rows updated.

SCOTT@orcl_11gR2> column msisdn format a12
SCOTT@orcl_11gR2> select msisdn, idd, isdropped
  2  from   tmp_mape
  3  /

MSISDN       IDD              ISDROPPED
------------ ---------------- ----------
423903200200 410              true
423903200200 003004020000c7a1
423903200200 110              true
423903200200 220              false
423903200200

5 rows selected.

SCOTT@orcl_11gR2>

Re: faster parse of table with xml [message #499080 is a reply to message #499016] Sun, 13 March 2011 07:51 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
I run my update like simply update sql. Update runs almost 4 hour and I am doubt if its a good way.

Here looks my exmplain plan where table TDW_WEB_REPORT_ECARE correspondent to tmp_table.

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 27317545

--------------------------------------------------------------------------------
--------------------------------------------------------------------------

| Id  | Operation                          | Name                   | Rows  | By
tes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------
--------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                   |                        |  3792K|  9
365M|  1202  (13)| 00:00:03 |       |       |        |      |            |

|   1 |  UPDATE                            | TDW_WEB_REPORT_ECARE   |       |
    |            |          |       |       |        |      |            |

|   2 |   PX COORDINATOR                   |                        |       |
    |            |          |       |       |        |      |            |

|   3 |    PX SEND QC (RANDOM)             | :TQ10000               |  3792K|  9
365M|  1202  (13)| 00:00:03 |       |       |  Q1,00 | P->S | QC (RAND)  |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   4 |     PX BLOCK ITERATOR              |                        |  3792K|  9
365M|  1202  (13)| 00:00:03 |     1 |     7 |  Q1,00 | PCWC |            |

|   5 |      TABLE ACCESS FULL             | TDW_WEB_REPORT_ECARE   |  3792K|  9
365M|  1202  (13)| 00:00:03 |    29 |    35 |  Q1,00 | PCWP |            |

|   6 |   COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |
    |            |          |       |       |        |      |            |

--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - UPD$1
   5 - UPD$1        / TM@UPD$1
   6 - SEL$1F3D9D0A

Column Projection Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------

   2 - (upd=3,4,5,6,7,8; cmp=2) "SYS_ALIAS_1".ROWID[ROWID,10], "TM"."XML_PARAMS"
[LOB,4000], "IDD"[VARCHAR2,200], "ISDROPPED"[VARCHAR2,150],

       "SERVICEID"[VARCHAR2,200], "X"[VARCHAR2,200], "RESULT"[VARCHAR2,200], "LO
GINMESSAGE"[VARCHAR2,200]

   3 - (#keys=0) "SYS_ALIAS_1".ROWID[ROWID,10], "TM"."XML_PARAMS"[LOB,4000], "ID
D"[VARCHAR2,200], "ISDROPPED"[VARCHAR2,150],


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       "SERVICEID"[VARCHAR2,200], "X"[VARCHAR2,200], "RESULT"[VARCHAR2,200], "LO
GINMESSAGE"[VARCHAR2,200]

   4 - "SYS_ALIAS_1".ROWID[ROWID,10], "TM"."XML_PARAMS"[LOB,4000], "IDD"[VARCHAR
2,200], "ISDROPPED"[VARCHAR2,150], "SERVICEID"[VARCHAR2,200],

       "X"[VARCHAR2,200], "RESULT"[VARCHAR2,200], "LOGINMESSAGE"[VARCHAR2,200]
   5 - "SYS_ALIAS_1".ROWID[ROWID,10], "TM"."XML_PARAMS"[LOB,4000], "IDD"[VARCHAR
2,200], "ISDROPPED"[VARCHAR2,150], "SERVICEID"[VARCHAR2,200],

       "X"[VARCHAR2,200], "RESULT"[VARCHAR2,200], "LOGINMESSAGE"[VARCHAR2,200]

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   6 - VALUE(A0)[64]

Note
-----
   - dynamic sampling used for this statement


I don't know how to make update for all records Sad

[Updated on: Sun, 13 March 2011 07:52]

Report message to a moderator

Re: duplicate records after xml extract [message #499129 is a reply to message #498800] Sun, 13 March 2011 16:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
mape wrote on Fri, 11 March 2011 02:12

I struggle to use select below but nothing returns into the X_ID column.
Could you please take a look at that what is wrong?

    select t2.X_ID ,
    t.xml_params
    from  tmp_mape  t ,
          XMLTABLE(XMLNAMESPACES(default 'http://datalan.sk/webreporting/params/v1_0' ),
               'for $i in /*
                  return <datatype name="{$i/name()}">
                          {
                            for $j in $i/*
                            return $j
                          }
                         </datatype>'
               passing xmltype( XML_PARAMS)
               columns
  X_ID varchar2(100) PATH '/datatype/parameters/parameter[@name="id"]/text()'
            ) t2   



Please try the code in the examples below. I included two different select statements.

-- test environment:
SCOTT@orcl_11gR2> CREATE TABLE tmp_mape
  2    (msisdn	    VARCHAR2 (100),
  3  	xml_params  CLOB )
  4  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO tmp_mape VALUES
  3  ('423903200200',
  4  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>410</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>' )
  5  INTO tmp_mape VALUES
  6  ('423903200200',
  7  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>003004020000c7a1</value></parameter></parameters>')
  8  INTO tmp_mape VALUES
  9  ('423903200200',
 10  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>110</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>')
 11  INTO tmp_mape VALUES
 12  ('423903200200',
 13  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>220</value></parameter><parameter name="isDropped"><value>false</value></parameter></parameters>')
 14  SELECT * FROM DUAL
 15  /

4 rows created.


-- select statement like what you were trying to do:
SCOTT@orcl_11gR2> SELECT t2.x_id, t2.x_isDropped
  2  FROM   tmp_mape t,
  3  	    XMLTABLE
  4  	      (XMLNAMESPACES (default 'http://datalan.sk/webreporting/params/v1_0'),
  5  	       'for $i in /*
  6  		  return
  7  		    <datatype>
  8  		      {for $j in $i/*
  9  		       return $j}
 10  		    </datatype>'
 11  		PASSING XMLTYPE (xml_params)
 12  		COLUMNS
 13  		  x_id	      VARCHAR2(10) PATH '/datatype/parameter[@name="id"]/value',
 14  		  x_isDropped VARCHAR2(10) PATH '/datatype/parameter[@name="isDropped"]/value'
 15  	      ) t2
 16  /

X_ID       X_ISDROPPE
---------- ----------
410        true
0030040200
110        true
220        false

4 rows selected.

SCOTT@orcl_11gR2>


-- slightly different select statement:
SCOTT@orcl_11gR2> SELECT t2.x_id, t2.x_isDropped
  2  FROM   tmp_mape t,
  3  	    XMLTABLE
  4  	      (XMLNAMESPACES (default 'http://datalan.sk/webreporting/params/v1_0'),
  5  	       'for $i in /*
  6  		  return
  7  		    <datatype>
  8  		      <col1_id>{$i/parameter[@name="id"]/value}</col1_id>
  9  		      <col2_isDropped>{$i/parameter[@name="isDropped"]/value}</col2_isDropped>
 10  		    </datatype>'
 11  		PASSING XMLTYPE (xml_params)
 12  		COLUMNS
 13  		  x_id	      VARCHAR2(10) PATH 'col1_id/value',
 14  		  x_isDropped VARCHAR2(10) PATH 'col2_isDropped/value'
 15  	      ) t2
 16  /

X_ID       X_ISDROPPE
---------- ----------
410        true
0030040200
110        true
220        false

4 rows selected.


[Updated on: Sun, 13 March 2011 16:46]

Report message to a moderator

Re: duplicate records after xml extract [message #499212 is a reply to message #499129] Mon, 14 March 2011 02:50 Go to previous messageGo to next message
_jum
Messages: 573
Registered: February 2008
Senior Member
@BB I don't get your result, slightly modified the query, but get the same unexpected result with your original one (ORACLE 10.2.0.4.0 WIN 64):
WITH tmp_mape AS 
 (SELECT 423903200201 msisdn,
   XMLType('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
              <parameter name="id">
                <value>410</value>
              </parameter>
              <parameter name="isDropped">
                <value>true</value>
              </parameter>
            </parameters>') xml_params FROM dual UNION ALL
  SELECT 423903200202, 
    XMLType('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
               <parameter name="id">
                 <value>003004020000c7a1</value>
               </parameter>
             </parameters>')           FROM dual UNION ALL
  SELECT 423903200203,
     XMLType('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
                <parameter name="id">
                  <value>110</value>
                </parameter><parameter name="isDropped">
                  <value>true</value>
                </parameter>
             </parameters>')           FROM dual UNION ALL
  SELECT 423903200204, 
    XMLType('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
               <parameter name="id">
                 <value>220</value>
               </parameter>
               <parameter name="isDropped">
                 <value>false</value>
               </parameter>
             </parameters>')           FROM dual) 
 SELECT msisdn, t2.x_id, t2.x_isDropped 
   FROM tmp_mape t,
        XMLTABLE
          (XMLNAMESPACES (default 'http://datalan.sk/webreporting/params/v1_0'),
            'for $i in /* return
               <datatype>
                 <col1_id>{$i/parameter[@name="id"]/value}</col1_id>
                 <col2_isDropped>{$i/parameter[@name="isDropped"]/value}</col2_isDropped>
               </datatype>'
           PASSING xml_params
           COLUMNS
             x_id          VARCHAR2(10) PATH 'col1_id/value',
             x_isDropped   VARCHAR2(10) PATH 'col2_isDropped/value'
             ) t2;

msisdn, x_id, x_isDropped    
----------------------------------
423903200201	410	true
423903200202	410	true
423903200203	410	true
423903200204	410	true

[Updated on: Mon, 14 March 2011 02:51]

Report message to a moderator

Re: duplicate records after xml extract [message #499225 is a reply to message #499212] Mon, 14 March 2011 03:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
If what you posted is what you ran, which I can't be certain of without a copy and paste of a continuous run from SQL*Plus with line numbers, then the difference must be due to versions or settings. I have provided a run of a copy and paste of the modified code that you posted on my system below and it works as desired and expected.

SCOTT@orcl_11gR2> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.

SCOTT@orcl_11gR2> WITH tmp_mape AS
  2   (SELECT 423903200201 msisdn,
  3  	XMLType('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  4  		   <parameter name="id">
  5  		     <value>410</value>
  6  		   </parameter>
  7  		   <parameter name="isDropped">
  8  		     <value>true</value>
  9  		   </parameter>
 10  		 </parameters>') xml_params FROM dual UNION ALL
 11    SELECT 423903200202,
 12  	 XMLType('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
 13  		    <parameter name="id">
 14  		      <value>003004020000c7a1</value>
 15  		    </parameter>
 16  		  </parameters>')	    FROM dual UNION ALL
 17    SELECT 423903200203,
 18  	  XMLType('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
 19  		     <parameter name="id">
 20  		       <value>110</value>
 21  		     </parameter><parameter name="isDropped">
 22  		       <value>true</value>
 23  		     </parameter>
 24  		  </parameters>')	    FROM dual UNION ALL
 25    SELECT 423903200204,
 26  	 XMLType('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
 27  		    <parameter name="id">
 28  		      <value>220</value>
 29  		    </parameter>
 30  		    <parameter name="isDropped">
 31  		      <value>false</value>
 32  		    </parameter>
 33  		  </parameters>')	    FROM dual)
 34   SELECT msisdn, t2.x_id, t2.x_isDropped
 35  	FROM tmp_mape t,
 36  	     XMLTABLE
 37  	       (XMLNAMESPACES (default 'http://datalan.sk/webreporting/params/v1_0'),
 38  		 'for $i in /* return
 39  		    <datatype>
 40  		      <col1_id>{$i/parameter[@name="id"]/value}</col1_id>
 41  		      <col2_isDropped>{$i/parameter[@name="isDropped"]/value}</col2_isDropped>
 42  		    </datatype>'
 43  		PASSING xml_params
 44  		COLUMNS
 45  		  x_id		VARCHAR2(10) PATH 'col1_id/value',
 46  		  x_isDropped	VARCHAR2(10) PATH 'col2_isDropped/value'
 47  		  ) t2;

          MSISDN X_ID       X_ISDROPPE
---------------- ---------- ----------
    423903200201 410        true
    423903200202 0030040200
    423903200203 110        true
    423903200204 220        false

4 rows selected.

SCOTT@orcl_11gR2>

Re: duplicate records after xml extract [message #499235 is a reply to message #499225] Mon, 14 March 2011 04:27 Go to previous messageGo to next message
_jum
Messages: 573
Registered: February 2008
Senior Member
Yes, it seems due to versions.
SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> WITH tmp_mape AS
  2   (SELECT 423903200201 msisdn,
  3     XMLType('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  4                <parameter name="id">
  5                  <value>410</value>
  6                </parameter>
  7                <parameter name="isDropped">
  8                  <value>true</value>
  9                </parameter>
 10              </parameters>') xml_params FROM dual UNION ALL
 11    SELECT 423903200202,
 12      XMLType('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
 13                 <parameter name="id">
 14                   <value>003004020000c7a1</value>
 15                 </parameter>
 16               </parameters>')           FROM dual UNION ALL
 17    SELECT 423903200203,
 18       XMLType('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
 19                  <parameter name="id">
 20                    <value>110</value>
 21                  </parameter><parameter name="isDropped">
 22                    <value>true</value>
 23                  </parameter>
 24               </parameters>')           FROM dual UNION ALL
 25    SELECT 423903200204,
 26      XMLType('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
 27                 <parameter name="id">
 28                   <value>220</value>
 29                 </parameter>
 30                 <parameter name="isDropped">
 31                   <value>false</value>
 32                 </parameter>
 33               </parameters>')           FROM dual)
 34   SELECT msisdn, t2.x_id, t2.x_isDropped
 35     FROM tmp_mape t,
 36          XMLTABLE
 37            (XMLNAMESPACES (default 'http://datalan.sk/webreporting/params/v1_0'),
 38              'for $i in /* return
 39                 <datatype>
 40                   <col1_id>{$i/parameter[@name="id"]/value}</col1_id>
 41                   <col2_isDropped>{$i/parameter[@name="isDropped"]/value}</col2_isDropped>
 42                 </datatype>'
 43             PASSING xml_params
 44             COLUMNS
 45               x_id          VARCHAR2(10) PATH 'col1_id/value',
 46               x_isDropped   VARCHAR2(10) PATH 'col2_isDropped/value'
 47               ) t2;

    MSISDN X_ID       X_ISDROPPE
---------- ---------- ----------
4,2390E+11 410        true
4,2390E+11 410        true
4,2390E+11 410        true
4,2390E+11 410        true

BTW I like your profound and competend posts Thumbs Up - many thanks!

[Updated on: Mon, 14 March 2011 04:28]

Report message to a moderator

Re: duplicate records after xml extract [message #499242 is a reply to message #499235] Mon, 14 March 2011 04:50 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
Due to optimalization I created text index:
CREATE INDEX ETDW.MY_DOCS_DOC_ID ON tmp_mape
(XML_PARAMS)
INDEXTYPE IS CTXSYS.CONTEXT
NOPARALLEL;


and insert another one record:
insert into tmp_mape
values ('423903200200', '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="loginMessage"><value>registrationSuccessfull</value></parameter></parameters>')


I think about to search all records which contains required value ( loginMessage from xml_params)
and use select to parse of xml.
The select below does not return any record. Why?
select   *  from tmp_mape a
where   CONTAINS( xml_params, 'login'   ) > 0;

[Updated on: Mon, 14 March 2011 04:51]

Report message to a moderator

Re: duplicate records after xml extract [message #499252 is a reply to message #499242] Mon, 14 March 2011 05:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You need to either create your index after your inserts or use sync(on commit) in your parameters during index creatioin. Records are not searchable until they have been synchronized. Alos, if login is not one word, but part of a word, like loginmessage, then you need to use a wildcard (%) like login%. Please see the demonstration below.


SCOTT@orcl_11gR2> CREATE TABLE tmp_mape
  2    (msisdn	    VARCHAR2 (100),
  3  	xml_params  CLOB )
  4  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO tmp_mape VALUES
  3  ('423903200200',
  4  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>410</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>' )
  5  INTO tmp_mape VALUES
  6  ('423903200200',
  7  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>003004020000c7a1</value></parameter></parameters>')
  8  INTO tmp_mape VALUES
  9  ('423903200200',
 10  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>110</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>')
 11  INTO tmp_mape VALUES
 12  ('423903200200',
 13  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>220</value></parameter><parameter name="isDropped"><value>false</value></parameter></parameters>')
 14  SELECT * FROM DUAL
 15  /

4 rows created.

SCOTT@orcl_11gR2> CREATE INDEX MY_DOCS_DOC_ID ON tmp_mape
  2  (XML_PARAMS)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS ('SYNC (ON COMMIT)')
  5  NOPARALLEL
  6  /

Index created.

SCOTT@orcl_11gR2> insert into tmp_mape
  2  values ('423903200200', '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="loginMessage"><value>registrationSuccessfull</value></parameter></parameters>')
  3  /

1 row created.

SCOTT@orcl_11gR2> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11gR2> select   *  from tmp_mape a
  2  where   CONTAINS( xml_params, 'login%'   ) > 0
  3  /

MSISDN
----------------------------------------------------------------------------------------------------
XML_PARAMS
------------------------------------------------------------------------------------------------------------------------
423903200200
<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="loginMessage"><value>registrationSuccess
full</value></parameter></parameters>


1 row selected.

SCOTT@orcl_11gR2>

Re: duplicate records after xml extract [message #499256 is a reply to message #499252] Mon, 14 March 2011 05:22 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member

I didn't know about synchronization of index, that's a great point.

When I don't want to delete index and create it again how can I do synchronization after insert records?

Thanks Barbara
Re: duplicate records after xml extract [message #499354 is a reply to message #499256] Mon, 14 March 2011 10:58 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You can either create your index to synchronize on commit or at specified intervals or you can use ctx_ddl.sync_index to do it manually whenever you wish. However, the more frequently that you synchronize your text index, the more fragmented it will become, and the slower your searches will be, so you need to optimize periodically to eliminate the fragmentation. Alternatively, you can alter index ... rebuild or drop and recreate. Which method you choose all depends on your situation. If you have constant dml and you need to be able to search the information immediately, then you probably want to use sync(on commit) and optimize hourly or something similar. However, if your only dml is through occasional loads and the information does not need to be available for searching during that time, then you may want to drop your index, do your load, then recreate the index.



Previous Topic: command line arguments...
Next Topic: copy files into system directory
Goto Forum:
  


Current Time: Tue Jul 27 17:18:30 CDT 2021