Home » Developer & Programmer » JDeveloper, Java & XML » read xml parameter (merged) (Oracle 10g)
read xml parameter (merged) [message #485271] Tue, 07 December 2010 03:23 Go to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
Hi

I got table with column of XML codes like this:

select xmltype ('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
                   <parameter name="result"><value>success</value></parameter>
                   <parameter name="showBirthday"><value>false</value></parameter>
                   <parameter name="_wrCommand"><value>clearCacheBefore</value></parameter>
                   </parameters>'
) val
   from    dual;



And I need to select records from table where xml code complies with requirement:
name="result" equals success.
name="result"><value>success</value>


Does anybody know how to do it?

thanks

[Updated on: Tue, 07 December 2010 12:34]

Report message to a moderator

get values from xml [message #485350 is a reply to message #485271] Tue, 07 December 2010 12:46 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member

I would like to extract the value between <value>...</value> from that XML.
I try to figure it out but still successfully.


   select   extractvalue (value (tr),'/parameter[name="result"]' ) col
   from (
   select xmltype ('<parameters>
                                   <parameter name="result"><value>success</value></parameter>
                                    <parameter name="showBirthday"><value>false</value></parameter>
                                   <parameter name="id"><value>110</value></parameter>
                                   <parameter name="isDropped"><value>true</value></parameter>
                             </parameters>' ) val
               from dual ) tt,               
              table (xmlsequence (extract (tt.val, '/parameters'))) tr;



Could you give me and advice how to write it correctly?

Thanks

Report message to a moderator

Re: get values from xml [message #485351 is a reply to message #485350] Tue, 07 December 2010 12:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/485271/136107/#msg_485271

do NOT multi-post!

Report message to a moderator

Re: get values from xml [message #485353 is a reply to message #485350] Tue, 07 December 2010 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Do not multipost your questions.

Regards
Michel

Report message to a moderator

Re: get values from xml [message #485354 is a reply to message #485351] Tue, 07 December 2010 12:48 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
there is a different example and question

Report message to a moderator

Re: get values from xml [message #485355 is a reply to message #485354] Tue, 07 December 2010 12:49 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
ok

Report message to a moderator

Re: get values from xml [message #485356 is a reply to message #485355] Tue, 07 December 2010 12:50 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
please can you help me with it?

Report message to a moderator

Re: read xml parameter (merged) [message #485361 is a reply to message #485271] Tue, 07 December 2010 13:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
SQL> with 
  2    data as ( 
  3      select xmltype (
  4  '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  5     <parameter name="result"><value>success</value></parameter>
  6     <parameter name="showBirthday"><value>false</value></parameter>
  7     <parameter name="_wrCommand"><value>clearCacheBefore</value></parameter>
  8  </parameters>') val
  9     from dual
 10    )
 11  select extract(value(x),'/parameter','xmlns="http://datalan.sk/webreporting/params/v1_0"') res
 12  from data, 
 13       table(xmlsequence(extract(val, '/parameters/parameter',
 14                                 'xmlns="http://datalan.sk/webreporting/params/v1_0"'))) x
 15  where extractValue(value(x),'/parameter/@name',
 16                     'xmlns="http://datalan.sk/webreporting/params/v1_0"') = 'result'
 17    and extractValue(value(x),'/parameter/value',
 18                     'xmlns="http://datalan.sk/webreporting/params/v1_0"') = 'success'
 19  /
RES
----------------------------------------------------------------------------------------------------------------
<parameter xmlns="http://datalan.sk/webreporting/params/v1_0" name="result"><value>success</value></parameter>

Regards
Michel

Report message to a moderator

Re: read xml parameter (merged) [message #485371 is a reply to message #485361] Tue, 07 December 2010 13:59 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member

This is the perfect solution, works fine.


Thanks a lot

Report message to a moderator

Re: read xml parameter (merged) [message #486002 is a reply to message #485371] Sun, 12 December 2010 12:28 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member

I'd like to define this condition into the Cognos reporting tool, but this function like extractValue
is not usable for that.
One way how is supposed to be is to write function using extractValue(value(x) ...


Is it a possible to write function where input parameters would meet the requirements
what I look for?
For instance I need to get result from the_table where xml_params complies with requirement:
<parameter name="result"><value>success</value></parameter>


I got the the_table :
msisdn varchar2(100),
id number,
xml_params clob


select * from the_table
MSISDN             ID                 XML_PARAMS
-------------------------------------------------
12345              2                    <clob>
56789              4                    <clob>


And the xml_params consist of:
<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
       <parameter name="result"><value>success</value></parameter>
       <parameter name="showBirthday"><value>false</value></parameter>
       <parameter name="_wrCommand"><value>clearCacheBefore</value></parameter>
</parameters>


Thanks for help.

Report message to a moderator

Re: read xml parameter (merged) [message #486003 is a reply to message #486002] Sun, 12 December 2010 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data.

Regards
Michel

Report message to a moderator

Re: read xml parameter (merged) [message #486035 is a reply to message #486003] Mon, 13 December 2010 02:35 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
 
CREATE TABLE the_table
(
  ID                 NUMBER                     NOT NULL,
  DATE_CREATED       TIMESTAMP(6),
  XML_PARAMS         CLOB
)





insert into the_table values
 (2062044, to_date('06.12.2010 16:08:10', 'DD.MM.YYYY HH24:MI:SS' ), '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="userAgent"><value>Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)</value></parameter><parameter name="result"><value>success</value></parameter><parameter name="showBirthday"><value>false</value></parameter><parameter name="_wrCommand"><value>clearCacheBefore</value></parameter><parameter name="contractDN"><value>tmo-camib-contract-id=BSCS-PRD-CONTRACT-2435272,ou=contracts,cn=BSCS-PRD-CUSTOMER-1609218,o=postpaid,o=camib,dc=t-mobile,dc=sk</value></parameter></parameters>'  )

insert into the_table values
 (2062045, to_date('06.12.2010 18:08:10', 'DD.MM.YYYY HH24:MI:SS' ), '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>000735ffffa277db</value></parameter></parameters>'  )

insert into the_table values
 (2062055, to_date('06.12.2010 19:08:10', 'DD.MM.YYYY HH24:MI:SS' ), '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="userAgent"><value>Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.3 (KHTML, like Gecko) Chrome/6.0.472.63 Safari/534.3</value></parameter><parameter name="result"><value>falses</value></parameter><parameter name="showBirthday"><value>false</value></parameter><parameter name="_wrCommand"><value>clearCacheBefore</value></parameter><parameter name="contractDN"><value>tmo-camib-contract-id=BSCS-PRD-CONTRACT-3755073,ou=contracts,cn=BSCS-PRD-CUSTOMER-3333073,o=postpaid,o=camib,dc=t-mobile,dc=sk</value></parameter></parameters>'  )

insert into the_table values
 (2062055, to_date('06.12.2010 20:08:10', 'DD.MM.YYYY HH24:MI:SS' ),'<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>210</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>' )



Is it a possible to write function where input parameters would meet the requirements
what I look for?
For instance I need to get result from the_table where xml_params complies with requirement:
<parameter name="result"><value>success</value></parameter>

thanks

[Updated on: Mon, 13 December 2010 02:55]

Report message to a moderator

Re: read xml parameter (merged) [message #486044 is a reply to message #486035] Mon, 13 December 2010 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What should be the result for the test case you gave?

And keep your lines in 80 character width when you use code tags.

Regards
Michel

Report message to a moderator

Re: read xml parameter (merged) [message #486057 is a reply to message #486044] Mon, 13 December 2010 05:33 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member

The result should be just list of records from the_table which complies requirement:
<parameter name="result"><value>success</value></parameter>


It would be a very useful to have a function refer to the the_table (or something like that)
with input two parameters. And when I use this function I can get just records which comply of input
parameters.
Something like: select * from the_table
where the_function() in ('result','succes')

I just want to avoid of extractValue(value(x) ....


I dont know if its a really possible to do that what I think of Sad

Report message to a moderator

Re: read xml parameter (merged) [message #486060 is a reply to message #486057] Mon, 13 December 2010 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Still don't know. Which parameters? What the function input? What is the function output?
Why can't you use extractValue inside the function?

Regards
Michel

[Updated on: Mon, 13 December 2010 05:49]

Report message to a moderator

Re: read xml parameter (merged) [message #486061 is a reply to message #486060] Mon, 13 December 2010 06:09 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
Im not able to use an expression (see below) to the Cognos and for that reason I try to use some function instead of it.

  TABLE (
             XMLSEQUENCE (
                EXTRACT (
                   xmltype (xml_params),
                   '/parameters/parameter',
                   'xmlns="http://datalan.sk/webreporting/params/v1_0"'))) x
    WHERE  extractValue(value(x),'/parameter/@name',
                      'xmlns="http://datalan.sk/webreporting/params/v1_0"') = 'result'
           AND extractValue(value(x),'/parameter/value',
                      'xmlns="http://datalan.sk/webreporting/params/v1_0"') = 'success'


Maybe I think about it more complicated, maybe simply way is to parse xml_params
and write every value into the another column for the same record. But this is not very easy for me to do that Smile

[Updated on: Mon, 13 December 2010 06:10]

Report message to a moderator

Re: read xml parameter (merged) [message #486063 is a reply to message #486061] Mon, 13 December 2010 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you can call a function then put the query inside the function. Where is the problem?

Regards
Michel

Report message to a moderator

Re: read xml parameter (merged) [message #486064 is a reply to message #486063] Mon, 13 December 2010 06:33 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
I dont get it exactly what you mean.

Report message to a moderator

Re: read xml parameter (merged) [message #486065 is a reply to message #486064] Mon, 13 December 2010 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't know what is allowed and what you can do with Cognos but maybe you can call a procedure/function that returns a ref cursor.
If you can do as you said:
select * from the_table where the_function() in ('result','succes')
then you can easily create a function that take the clob, the name and returns the value as the query above gives.
Or you can create a pipelined function that returns all rows for the parameters
And so on.

Just describe all what is allowed and all what is not.

Regards
Michel

Report message to a moderator

Re: read xml parameter (merged) [message #486096 is a reply to message #486065] Mon, 13 December 2010 10:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
 
SCOTT@orcl_11gR2> CREATE TABLE the_table
  2  (
  3    ID		  NUMBER		     NOT NULL,
  4    DATE_CREATED	  TIMESTAMP(6),
  5    XML_PARAMS	  CLOB
  6  )
  7  /

Table created.

SCOTT@orcl_11gR2> insert into the_table values
  2   (2062045, to_date('06.12.2010 18:08:10', 'DD.MM.YYYY HH24:MI:SS' ),
  3    '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  4  	  <parameter name="id"><value>000735ffffa277db</value></parameter>
  5  	</parameters>')
  6  /

1 row created.

SCOTT@orcl_11gR2> insert into the_table values
  2   (2062050, to_date('06.12.2010 19:08:10', 'DD.MM.YYYY HH24:MI:SS' ),
  3    '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  4  	  <parameter name="userAgent"><value>Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.3 (KHTML, like Gecko) Chrome/6.0.472.63 Safari/534.3</value></parameter>
  5  	  <parameter name="result"><value>success</value></parameter>
  6  	  <parameter name="showBirthday"><value>false</value></parameter>
  7  	  <parameter name="_wrCommand"><value>clearCacheBefore</value></parameter>
  8  	  <parameter name="contractDN"><value>tmo-camib-contract-id=BSCS-PRD-CONTRACT-3755073,ou=contracts,cn=BSCS-PRD-CUSTOMER-3333073,o=postpaid,o=camib,dc=t-mobile,dc=sk</value></parameter>
  9  	</parameters>')
 10  /

1 row created.

SCOTT@orcl_11gR2> insert into the_table values
  2   (2062055, to_date('06.12.2010 20:08:10', 'DD.MM.YYYY HH24:MI:SS' ),
  3    '<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
  4  	  <parameter name="id"><value>210</value></parameter>
  5  	  <parameter name="isDropped"><value>true</value></parameter>
  6  	</parameters>')
  7  /

1 row created.

SCOTT@orcl_11gR2> create or replace function the_function
  2    (p_xml_params  in clob,
  3  	p_param_name  in varchar2,
  4  	p_param_value in varchar2)
  5    return		 number
  6  as
  7    v_count		 number;
  8    v_url		 varchar2(32767);
  9  begin
 10    v_url := substr (p_xml_params, 1, instr (p_xml_params, '>') - 1);
 11    v_url := ltrim (substr (v_url, instr (v_url, '<parameters') + 11));
 12    select count(*)
 13    into   v_count
 14    from   table
 15  		(xmlsequence
 16  		  (extract
 17  		    (xmltype (p_xml_params),
 18  		     '/parameters/parameter',
 19  		     v_url))) x
 20    where  extractValue
 21  		(value(x),
 22  		 '/parameter/@name',
 23  		 v_url) = p_param_name
 24    and    extractValue
 25  		(value(x),
 26  		 '/parameter/value',
 27  		 v_url) = p_param_value;
 28    return v_count;
 29  end the_function;
 30  /

Function created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> select *
  2  from   the_table
  3  where  the_function (xml_params, 'result', 'success') > 0
  4  /

        ID
----------
DATE_CREATED
---------------------------------------------------------------------------
XML_PARAMS
--------------------------------------------------------------------------------
   2062050
06-DEC-10 07.08.10.000000 PM
<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
     <parameter name="userAgent"><value>Mozilla/5.0 (Windows; U; Windows NT 5.1;
 en-US) AppleWebKit/534.3 (KHTML, like Gecko) Chrome/6.0.472.63 Safari/534.3</va
lue></parameter>
     <parameter name="result"><value>success</value></parameter>
     <parameter name="showBirthday"><value>false</value></parameter>
     <parameter name="_wrCommand"><value>clearCacheBefore</value></parameter>
     <parameter name="contractDN"><value>tmo-camib-contract-id=BSCS-PRD-CONTRACT
-3755073,ou=contracts,cn=BSCS-PRD-CUSTOMER-3333073,o=postpaid,o=camib,dc=t-mobil
e,dc=sk</value></parameter>
   </parameters>


1 row selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: read xml parameter (merged) [message #486179 is a reply to message #486096] Tue, 14 December 2010 02:52 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
Thanks a lot Barbara, excelent solution. This is axactly what I did mean.

Report message to a moderator

Re: read xml parameter (merged) [message #486182 is a reply to message #486179] Tue, 14 December 2010 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And this what I meant although I would not write it like this as it will have some performances
problem if the table is big (it scans the whole table for each row of this table, more it can lead
to some incorrect results due to the fact that the queries inside the function are not consistent
with the one outside it).

Regards
Michel

Report message to a moderator

Re: read xml parameter (merged) [message #486239 is a reply to message #486182] Tue, 14 December 2010 07:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Tue, 14 December 2010 01:06

...I would not write it like this as it will have some performances problem if the table is big (it scans the whole table for each row of this table...


I see the issue, but I don't see a workaround that does not have other issues. How would you write it?

Michel Cadot wrote on Tue, 14 December 2010 01:06

...more it can lead to some incorrect results due to the fact that the queries inside the function are not consistent with the one outside it...


Please demonstrate how it can return incorrect results. I do not see the inconsistency.

Report message to a moderator

Re: read xml parameter (merged) [message #486241 is a reply to message #486239] Tue, 14 December 2010 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
For the first point, I will do it as soon as I have a moment and database to test it.
For the second point, if a row is deleted during the execution of the main query then
it will not be seen by the function query and the count will return 0 when it should
return > 0.

Regards
Michel

Report message to a moderator

Re: read xml parameter (merged) [message #486246 is a reply to message #486241] Tue, 14 December 2010 07:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Tue, 14 December 2010 05:28

...if a row is deleted during the execution of the main query then it will not be seen by the function query and the count will return 0 when it should return > 0...


I am still not seeing this. I would need to see a demo that proves it. The query within the function does not select from the table; It only selects from the value of the input parameter that is passed to it.

Report message to a moderator

Re: read xml parameter (merged) [message #486253 is a reply to message #486246] Tue, 14 December 2010 08:13 Go to previous message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
The query within the function does not select from the table; It only selects from the value of the input parameter that is passed to it.

Doh! This is the point I missed.

Regards
Michel

Report message to a moderator

Previous Topic: Convert a complete oracle database in a single XML file?
Next Topic: table output to XML possible?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Feb 03 17:32:09 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.