Home » Developer & Programmer » JDeveloper, Java & XML » XML parse automatically by xml tree (Oracle 10g)
XML parse automatically by xml tree [message #502909] Mon, 11 April 2011 08:36 Go to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
Hello

I got the table where are logged every single events from web site. Sometimes (depends on events)is written xml code into the xml_params column of table.
And I just wonder how to make an automatically procedure (sql query, function, or something like that) which every value of xml code should be written separate into the column the table.

The point is I dont know what parameters will be included in the xml code in future.

For instance xml looks like:
<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  <parameter name="loginReason">
    <value>invalidUserNameOrPassword</value>
  </parameter>
</parameters>

<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  <parameter name="_wrCommand">
    <value>clearCacheAfter</value>
  </parameter>
</parameters>

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

<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  <parameter name="X">
    <value>false</value>
  </parameter>
</parameters>

I would just like to put down every name and value from xml code
into the new columns.
I would like to an avoid to make select where I have to write every name from xml code like:
extractValue
 (value(x),
  '/parameters/parameter[@name="id"]/value',
  'xmlns="http://datalan.sk/webreporting/params/v1_0"')


Does anybody know idea how to make it?


Thanks

[Updated on: Tue, 12 April 2011 02:19] by Moderator

Report message to a moderator

Re: XML parse automatically by xml tree [message #502912 is a reply to message #502909] Mon, 11 April 2011 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Can you tell me how many times I asked you to not exceed 80 characters per line?

Regards
Michel

Report message to a moderator

Re: XML parse automatically by xml tree [message #502930 is a reply to message #502912] Mon, 11 April 2011 12:55 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member

I'm sorry I don't remember about not exceed 80 characters per line. Did you tell me that? Smile
Could you tell me how to write 80 characters per line in there? I am really don't know how to do it.

Report message to a moderator

Re: XML parse automatically by xml tree [message #502938 is a reply to message #502930] Mon, 11 April 2011 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use the "enter" key on your keyboard.

Regards
Michel

Report message to a moderator

Re: XML parse automatically by xml tree [message #502940 is a reply to message #502938] Mon, 11 April 2011 13:58 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member

All right I learn new skills Smile

So back to the point.
Is it a possible read and write every parameter name and value to the separate columns?
I mean to do it without naming of every name and value in the query.

Report message to a moderator

Re: XML parse automatically by xml tree [message #502984 is a reply to message #502940] Tue, 12 April 2011 02:09 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
Maybe it's not possible to avoid naming of every xml name/value in the sql query.
But how can I identified all of parameter name and value from xml code?

When I try to do it by sql belowe I get an errors ORA-00932: inconsistent datatypes: expected - got CLOB

          select xml_params from tmp_mape
           group by xml_params


Thanks

Report message to a moderator

Re: XML parse automatically by xml tree [message #502994 is a reply to message #502909] Tue, 12 April 2011 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Is this what you need?
SQL> select * from t;
VAL
-------------------------------------------------------------------------------------------
<parameters>
  <parameter name="loginReason">
    <value>invalidUserNameOrPassword</value>
  </parameter>
</parameters>
<parameters>
  <parameter name="_wrCommand">
    <value>clearCacheAfter</value>
  </parameter>
</parameters>
<parameters>
  <parameter name="isDropped">
    <value>true</value>
  </parameter>
  <parameter name="serviceId">
    <value>6</value>
  </parameter>
</parameters>
<parameters>
  <parameter name="X">
    <value>false</value>
  </parameter>
</parameters>

4 rows selected.

SQL> col name format a20
SQL> col value format a30
SQL> select extractvalue(value(x), '/parameter/@name') name,
  2         extractvalue(value(x), '/parameter/value') value
  3  from t, table(xmlsequence(extract(xmltype(val), '/parameters/parameter'))) x
  4  /
NAME                 VALUE
-------------------- ------------------------------
loginReason          invalidUserNameOrPassword
_wrCommand           clearCacheAfter
isDropped            true
serviceId            6
X                    false

Regards
Michel

Report message to a moderator

Re: XML parse automatically by xml tree [message #502999 is a reply to message #502994] Tue, 12 April 2011 03:33 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
XML record looks like: 
     <parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>410</value> 
         </parameter><parameter name="isDropped"><value>true</value></parameter></parameters>  

    <parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="phoneNumber">
    <value>0904804936</value>
     </parameter></parameters>

   <parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  <parameter name="button">
    <value>no</value>
  </parameter>
  <parameter name="contractDN">
    <value>tmo-camib-contract-id=BSCS-PRD-CONTRACT-2119733,ou=contracts,cn=BSCS-PRD-CUSTOMER-2044737,o=postpaid,o=camib,dc=t-mobile,dc=sk</value>
  </parameter></parameters>


And when I apply your sql query then nothing returns Sad

[Updated on: Tue, 12 April 2011 03:33]

Report message to a moderator

Re: XML parse automatically by xml tree [message #503011 is a reply to message #502999] Tue, 12 April 2011 04:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
Senior Member
You can use regexp_replace to change all of the <parameters...> to just <parameters>, then concatenate <table> to the beginning and </table> to the end. Please see the demonstration below.

SCOTT@orcl_11gR2> create table tmp_mape
  2    (xml_params  clob)
  3  /

Table created.

SCOTT@orcl_11gR2> insert into tmp_mape values (
  2  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  3  	<parameter name="id"><value>410</value></parameter>
  4  	<parameter name="isDropped"><value>true</value></parameter>
  5   </parameters>
  6   <parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  7  	<parameter name="phoneNumber"><value>0904804936</value></parameter>
  8   </parameters>
  9   <parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
 10  	<parameter name="button"><value>no</value></parameter>
 11  	<parameter name="contractDN"><value>tmo-camib-contract-id=BSCS-PRD-CONTRACT-2119733,ou=contracts,cn=BSCS-PRD-CUSTOMER-2044737,o=postpaid,o=camib,dc=t-mobile,dc=sk</value></parameter>
 12   </parameters>')
 13  /

1 row created.

SCOTT@orcl_11gR2> col name format a20
SCOTT@orcl_11gR2> col value format a30
SCOTT@orcl_11gR2> select extractvalue (value (x), '/parameter/@name') name,
  2  	    extractvalue (value (x), '/parameter/value') value
  3  from   tmp_mape t,
  4  	    table
  5  	      (xmlsequence
  6  		(extract
  7  		  (xmltype
  8  		    ('<table>' ||
  9  		     regexp_replace (t.xml_params, '<parameters [a-z0-9=":/._]*>', '<parameters>')
 10  		     || '</table>'),
 11  		   '/table/parameters/parameter'))) x
 12  /

NAME                 VALUE
-------------------- ------------------------------
id                   410
isDropped            true
phoneNumber          0904804936
button               no
contractDN           tmo-camib-contract-id=BSCS-PRD
                     -CONTRACT-2119733,ou=contracts
                     ,cn=BSCS-PRD-CUSTOMER-2044737,
                     o=postpaid,o=camib,dc=t-mobile
                     ,dc=sk


5 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: XML parse automatically by xml tree [message #503014 is a reply to message #503011] Tue, 12 April 2011 05:34 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
Barbara, thanks for excellent solution.

Regards

Report message to a moderator

Re: XML parse automatically by xml tree [message #503017 is a reply to message #503014] Tue, 12 April 2011 05:46 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member

ANd last question: What if one xml record contain two parameter names and values
and I would like to get them into the one row ?

For instance:
      insert into tmp_mape values (
           ' <parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
     <parameter name="result"><value>ok</value>
     </parameter>
     <parameter name="serviceId"><value>7</value>
     </parameter>
     </parameters>' )


Thanks

Report message to a moderator

Re: XML parse automatically by xml tree [message #503019 is a reply to message #503017] Tue, 12 April 2011 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Maybe you should have a closer look of what we posted then you will have the answer to your question.

Regards
Michel

Report message to a moderator

Re: XML parse automatically by xml tree [message #503020 is a reply to message #503019] Tue, 12 April 2011 05:54 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
ok

Report message to a moderator

Re: XML parse automatically by xml tree [message #503023 is a reply to message #503020] Tue, 12 April 2011 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you want it in one row, how should be the result?

Regards
Michel

Report message to a moderator

Re: XML parse automatically by xml tree [message #503083 is a reply to message #503023] Tue, 12 April 2011 13:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
Senior Member
 
SCOTT@orcl_11gR2> create table tmp_mape
  2    (xml_params  clob)
  3  /

Table created.

SCOTT@orcl_11gR2> insert into tmp_mape values (
  2  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  3  	<parameter name="id"><value>410</value></parameter>
  4  	<parameter name="isDropped"><value>true</value></parameter>
  5   </parameters>
  6   <parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  7  	<parameter name="phoneNumber"><value>0904804936</value></parameter>
  8   </parameters>
  9   <parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
 10  	<parameter name="button"><value>no</value></parameter>
 11  	<parameter name="contractDN"><value>tmo-camib-contract-id=BSCS-PRD-CONTRACT-2119733,ou=contracts,cn=BSCS-PRD-CUSTOMER-2044737,o=postpaid,o=camib,dc=t-mobile,dc=sk</value></parameter>
 12   </parameters>')
 13  /

1 row created.

SCOTT@orcl_11gR2> col name1  format a11
SCOTT@orcl_11gR2> col name2  format a10
SCOTT@orcl_11gR2> col value1 format a10
SCOTT@orcl_11gR2> col value2 format a45
SCOTT@orcl_11gR2> select extractvalue (value (x), '//parameter[1]/@name') name1,
  2  	    extractvalue (value (x), '//parameter[1]/value') value1,
  3  	    extractvalue (value (x), '//parameter[2]/@name') name2,
  4  	    extractvalue (value (x), '//parameter[2]/value') value2
  5  from   tmp_mape t,
  6  	    table
  7  	      (xmlsequence
  8  		(extract
  9  		  (xmltype
 10  		    ('<table>' ||
 11  		     regexp_replace (t.xml_params, '<parameters [a-z0-9=":/._]*>', '<parameters>')
 12  		     || '</table>'),
 13  		   '/table/parameters'))) x
 14  /

NAME1       VALUE1     NAME2      VALUE2
----------- ---------- ---------- ---------------------------------------------
id          410        isDropped  true
phoneNumber 0904804936
button      no         contractDN tmo-camib-contract-id=BSCS-PRD-CONTRACT-21197
                                  33,ou=contracts,cn=BSCS-PRD-CUSTOMER-2044737,
                                  o=postpaid,o=camib,dc=t-mobile,dc=sk


3 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: XML parse automatically by xml tree [message #503084 is a reply to message #503083] Tue, 12 April 2011 14:13 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member

Works perfect.

Thanks a lot

Best Regards

Report message to a moderator

Re: XML parse automatically by xml tree [message #503086 is a reply to message #503084] Tue, 12 April 2011 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So you wanted the result in 4 columns.
Wby didn't you say it?
And what if there are 3 "parameter" in one "parameters"?

Regards
Michel

[Updated on: Tue, 12 April 2011 14:19]

Report message to a moderator

Re: XML parse automatically by xml tree [message #503108 is a reply to message #503086] Wed, 13 April 2011 01:06 Go to previous message
_jum
Messages: 577
Registered: February 2008
Senior Member
I wouldn prefer the namespace syntax instead of regexp:
WITH tmp_mape AS 
 (SELECT XMLType
   ('<data>
     <parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
      <parameter name="id"><value>410</value></parameter>
      <parameter name="isDropped"><value>true</value></parameter>
     </parameters>
     <parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
      <parameter name="phoneNumber"><value>0904804936</value></parameter>
     </parameters>
     <parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
      <parameter name="button"><value>no</value></parameter>
      <parameter name="contractDN"><value>tmo-camib-cont,
       o=postpaid,o=camib,dc=t-mobile,dc=sk</value></parameter>
     </parameters>
    </data>') xml_params  FROM dual
 )
 SELECT extractvalue (value (x), '//@name') name,
        extractvalue (value (x), '//value','xmlns="http://datalan.sk/webreporting/params/v1_0"') value
   FROM tmp_mape,
        TABLE
         (xmlsequence
          (extract(xml_params,'//parameter','xmlns="http://datalan.sk/webreporting/params/v1_0"'))) x;

NAME         VALUE
------------------------
id	     410
isDropped    true
phoneNumber  0904804936
button       no
contractDN   tmo-camib-cont,
         o=postpaid,o=camib,dc=t-mobile,dc=sk

Report message to a moderator

Previous Topic: Please help me fix this error! (4 threads merged by bb)
Next Topic: oracle.apps.fnd.framework.OAException: java.lang.ClassCastException
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 09 08:50:02 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.