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  |
mape
Messages: 298
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  |
 |
Barbara Boehmer
Messages: 9102
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 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #498053 is a reply to message #497626] |
Tue, 08 March 2011 07:57 |
mape
Messages: 298
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 #498656 is a reply to message #498057] |
Thu, 10 March 2011 13:36 |
mape
Messages: 298
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 #498800 is a reply to message #498660] |
Fri, 11 March 2011 04:12 |
mape
Messages: 298
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
|
|
|
|
|
|
| faster parse of table with xml [message #499000 is a reply to message #497465] |
Sat, 12 March 2011 12:50 |
mape
Messages: 298
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 #499007 is a reply to message #499005] |
Sat, 12 March 2011 13:21 |
mape
Messages: 298
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 #499016 is a reply to message #499015] |
Sat, 12 March 2011 15:16 |
|
Barbara Boehmer
Messages: 9102
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 |
mape
Messages: 298
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 
[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 |
|
Barbara Boehmer
Messages: 9102
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 |
_jum
Messages: 577
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 |
|
Barbara Boehmer
Messages: 9102
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 |
_jum
Messages: 577
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  - 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 |
mape
Messages: 298
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 |
|
Barbara Boehmer
Messages: 9102
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 |
mape
Messages: 298
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 |
|
Barbara Boehmer
Messages: 9102
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.
|
|
|
|
Goto Forum:
Current Time: Thu Jan 09 08:50:15 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
