Home » Developer & Programmer » JDeveloper, Java & XML » Oracle11g (Need help on XMLTYPE )
Oracle11g [message #665838] Tue, 26 September 2017 00:30 Go to next message
ssyr
Messages: 15
Registered: January 2017
Junior Member
HI all,

Can you please provide me suggestion me below query I want to truncate query upto 512 bytes, how can I do this?
When I am running this query it is not executing how can I execute this query?

select  extractvalue(xmltype(rr.extended_attributes),'/Map/String[@key="affiliate"]/text()') 
from  rest rr


Please give me suggestion

Thanks in Advance.
Re: Oracle11g [message #665840 is a reply to message #665838] Tue, 26 September 2017 00:50 Go to previous messageGo to next message
_jum
Messages: 522
Registered: February 2008
Senior Member
To find out the content of the XML node use:

--no more use superseded: extractvalue

WITH rr (extended_attributes) AS  
 (SELECT
 --here your test data   
 '<Map>    
    <String key = "affiliate">mycomp</String>
    <String key  ="orgarname">orgnam</String>    
  </Map>' 
    FROM dual)
SELECT extractvalue
        (xmltype(rr.extended_attributes),'/Map/String[@key="affiliate"]/text()') aff_id 
  FROM rr;      

AFF_ID
-------
mycomp  

--better use: XMLTable

WITH rr (extended_attributes) AS  
 (SELECT   
 --here your test data   
 '<Map>    
    <String key = "affiliate">mycomp</String>    
    <String key  ="orgarname">orgnam</String>    
  </Map>'
    FROM dual)
SELECT xt.aff_id  
  FROM rr
     , XMLTable('/Map'  
          passing xmltype(rr.extended_attributes)
          columns   
            aff_id  VARCHAR2(100)  path '/Map/String[@key="affiliate"]'  
          ) xt;  

AFF_ID
-------
mycomp  

[Updated on: Tue, 26 September 2017 01:01]

Report message to a moderator

Re: Oracle11g [message #665841 is a reply to message #665840] Tue, 26 September 2017 01:03 Go to previous messageGo to next message
ssyr
Messages: 15
Registered: January 2017
Junior Member
Thanks Jum.

How to extract data upto 512 bytes ?
Re: Oracle11g [message #665843 is a reply to message #665841] Tue, 26 September 2017 02:31 Go to previous message
_jum
Messages: 522
Registered: February 2008
Senior Member
Simply change length of the aff_id variable accordingly:

WITH rr (extended_attributes) AS  
 (SELECT   
 --here your test data , with a 512 byte string (RPAD-function) 
 '<Map>    
    <String key = "affiliate">'||rpad('mycomp',512,'x')||'</String>    
    <String key  ="orgarname">orgnam</String>    
  </Map>' FROM dual)
SELECT length(xt.aff_id) len, xt.aff_id 
  FROM rr
     , XMLTable('/Map'  
          passing xmltype(rr.extended_attributes)
          columns   
            aff_id  VARCHAR2(512)  path '/Map/String[@key="affiliate"]'  
          ) xt; 

LEN   AFF_ID
------------------------------------------
512   mycompxxxxxxxxxxxxxxxxxxxxxx...

[Updated on: Tue, 26 September 2017 02:32]

Report message to a moderator

Previous Topic: data extraction from xmltype
Goto Forum:
  


Current Time: Tue Nov 21 23:23:24 CST 2017

Total time taken to generate the page: 0.06515 seconds