I am having extracted short description which is trimmed to 200 characters from the xml payload. I n

User_QDSE2

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

odie_63

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

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';

cormaco

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>

odie_63

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
)

odie_63

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
;

User_QDSE2

@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';

odie_63

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';

User_QDSE2

@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)"

cormaco

4     $b:=subtr($a,1,1)"

You mistyped "substr" there.

User_QDSE2

@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)

cormaco

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>

odie_63

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
)

User_QDSE2

Thank you @odie_63 Your generic solution worked well.. Thanks much. @cormaco for correcting my syntax issues.

User_QDSE2

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.