Forum Stats

  • 3,825,285 Users
  • 2,260,497 Discussions
  • 7,896,481 Comments

Discussions

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

User_QDSE2
User_QDSE2 Member Posts: 7 Green Ribbon

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

  • odie_63
    odie_63 Member Posts: 8,491 Silver Trophy
    Answer ✓

    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
    odie_63 Member Posts: 8,491 Silver Trophy
    Answer ✓

    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
    cormaco Member Posts: 1,940 Silver Crown
    Answer ✓

    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>
    


    User_QDSE2
  • odie_63
    odie_63 Member Posts: 8,491 Silver Trophy
    Answer ✓

    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
«1

Answers

  • odie_63
    odie_63 Member Posts: 8,491 Silver Trophy
    Answer ✓

    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
    User_QDSE2 Member Posts: 7 Green Ribbon

    @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
    odie_63 Member Posts: 8,491 Silver Trophy
    Answer ✓

    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
    User_QDSE2 Member Posts: 7 Green Ribbon

    @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
    cormaco Member Posts: 1,940 Silver Crown

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

    You mistyped "substr" there.

  • User_QDSE2
    User_QDSE2 Member Posts: 7 Green Ribbon

    @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
    cormaco Member Posts: 1,940 Silver Crown
    Answer ✓

    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>
    


    User_QDSE2
  • odie_63
    odie_63 Member Posts: 8,491 Silver Trophy
    Answer ✓

    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
  • User_QDSE2
    User_QDSE2 Member Posts: 7 Green Ribbon

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

  • User_QDSE2
    User_QDSE2 Member Posts: 7 Green Ribbon

    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.