Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 437 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
I am having extracted short description which is trimmed to 200 characters from the xml payload. I n

I am having extracted short description which is trimmed to 200 characters from the xml payload. I need to replace the xml with trimmed short description. Framed with below , only i can replace test , but not the exact attribute
WITH testdata(xmlfile) AS(select xmltype(message) , substr(extract(xmltype(message), '/xxx/xxx/xxx/@xxx'),1,200) as Short
from table where state='Initial' and flow_name='xxxx' and
sub_flow_name='xxxx' and state='Initial' and errortxnid > '20220522' and errormessage like '%ORA-12899: value too large for column%')
SELECT XMLQUERY
( 'copy $tmp := . modify replace value of node $tmp/xxx/xxx/xxx/@xxx with "test" return $tmp 'PASSING xmlfile
RETURNING CONTENT)FROM testdata
Best Answers
-
You can do everything in the same XQuery expression :
select xmlquery( 'copy $d := . modify ( let $a := $d/xxx/xxx/xxx/@xxx return replace value of node $a with substring($a, 1, 5) ) return $d' passing t.message returning content ) from my_table t ;
-
Why are you reverting to (deprecated) UPDATEXML function now?
Anyway, the problem is you can't update an expression : "xmltype(message)".
I guess, MESSAGE column is of CLOB data type? Then you have to serialize back the result of the XQuery Update, and update the MESSAGE column with that :
UPDATE xxxx t SET message = XMLSerialize(document xmlquery( 'copy $d := . modify ( let $a := $d/xxx/xxx/xxx/@xxx return replace value of node $a with substring($a, 1, 195) ) return $d' passing xmlparse(document t.message) returning content ) as clob ) WHERE errortxnid = 'xxxxx';
-
My bad: the function is "substring" in XQuery, not substr. Here is a working example:
with t(message) as (select '<xxx><xxx><xxx xxx="3_5_0"/></xxx></xxx>' from dual) select XMLSerialize(document xmlquery( 'copy $d := . modify ( let $a := $d/xxx/xxx/xxx/@xxx let $b:=substring($a,1,1) let $c:=substring($a,3,1) let $d:=substring($a,4) return replace value of node $a with concat($c,"_",$b,$d) ) return $d' passing xmlparse(document t.message) returning content ) as clob ) result from t RESULT -------------------------------------------------------------------------------- <xxx><xxx><xxx xxx="5_3_0"/></xxx></xxx>
-
I would go for a more generic solution :
XMLSerialize(document xmlquery( 'copy $d := . modify ( let $a := $d/xxx/xxx/xxx/@xxx let $b := string-join( for $i in ora:tokenize($a, "_") order by number($i) descending return $i, "_" ) return replace value of node $a with $b ) return $d' passing xmlparse(document t.message) returning content ) as clob )
Answers
-
You can do everything in the same XQuery expression :
select xmlquery( 'copy $d := . modify ( let $a := $d/xxx/xxx/xxx/@xxx return replace value of node $a with substring($a, 1, 5) ) return $d' passing t.message returning content ) from my_table t ;
-
@odie_63 Thanks. it is working. can you also please help with below query. I need to update the attribute with trimmed value but facing "missed equal sign error" is there any way to update trimmed value to attribute
UPDATE xxxx SET xmltype(message) = UPDATEXML(xmltype(message),
'/xxx/xxx/xxx/@xxx',substr(extract(xmltype(message), '/xxx/xxx/xxx/@xxx'),1,195))
WHERE errortxnid = 'xxxxx';
-
Why are you reverting to (deprecated) UPDATEXML function now?
Anyway, the problem is you can't update an expression : "xmltype(message)".
I guess, MESSAGE column is of CLOB data type? Then you have to serialize back the result of the XQuery Update, and update the MESSAGE column with that :
UPDATE xxxx t SET message = XMLSerialize(document xmlquery( 'copy $d := . modify ( let $a := $d/xxx/xxx/xxx/@xxx return replace value of node $a with substring($a, 1, 195) ) return $d' passing xmlparse(document t.message) returning content ) as clob ) WHERE errortxnid = 'xxxxx';
-
@odie_63 I need to call another API with additional data along with XML. Thatswhy changed the approach. Your response worked. Thanks much. If possible, can you also please help with the below query
UPDATE table t
SET message =
XMLSerialize(document
xmlquery(
'copy $d := .
modify (
let $a := $d/xxx/xxx/xxx/@xxx
$b:=subtr($a,1,1)
$c:=subtr($a,3,1)
return replace value of node $a with CONCAT( CONCAT( $c, '-' ), $b )
)
return $d'
passing xmlparse(document t.message)
returning content
)
as clob
)
WHERE
errortxnid = 'xxxx';
Sample value which will i get in xml is 3_5_0 & i need to replace the xml with 5_3_0. Value should be descending order. When i try to run the above facing error as "ORA-19114: XPST0003 - error during parsing the XQuery expression:
LPX-00801: XQuery syntax error at 'b'
4 $b:=subtr($a,1,1)"
-
4 $b:=subtr($a,1,1)"
You mistyped "substr" there.
-
@cormaco even with that, it is not working
ORA-19114: XPST0003 - error during parsing the XQuery expression:
LPX-00801: XQuery syntax error at 'b'
4 $b:=substr($a,1,1)
-
My bad: the function is "substring" in XQuery, not substr. Here is a working example:
with t(message) as (select '<xxx><xxx><xxx xxx="3_5_0"/></xxx></xxx>' from dual) select XMLSerialize(document xmlquery( 'copy $d := . modify ( let $a := $d/xxx/xxx/xxx/@xxx let $b:=substring($a,1,1) let $c:=substring($a,3,1) let $d:=substring($a,4) return replace value of node $a with concat($c,"_",$b,$d) ) return $d' passing xmlparse(document t.message) returning content ) as clob ) result from t RESULT -------------------------------------------------------------------------------- <xxx><xxx><xxx xxx="5_3_0"/></xxx></xxx>
-
I would go for a more generic solution :
XMLSerialize(document xmlquery( 'copy $d := . modify ( let $a := $d/xxx/xxx/xxx/@xxx let $b := string-join( for $i in ora:tokenize($a, "_") order by number($i) descending return $i, "_" ) return replace value of node $a with $b ) return $d' passing xmlparse(document t.message) returning content ) as clob )
-
How to get the extracted values in sequence rather than concatenation of multiple ids. tried the below approach but not working, receiving like "SYS.XMLSEQUENCETYPE(00000)"
select xmlsequence(extract(xmltype(message), 'aaa/bbb/ccc/@Id')) from xxxx;
Under bbb, there are multiple ccc so different ids.