Oracle11g [message #665838] |
Tue, 26 September 2017 00:30 |
|
ssyr
Messages: 65 Registered: January 2017
|
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 |
_jum
Messages: 577 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 #665843 is a reply to message #665841] |
Tue, 26 September 2017 02:31 |
_jum
Messages: 577 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
|
|
|