1 Reply Latest reply on Sep 10, 2018 6:16 AM by cormaco

    replace multiple occurrences of a node in XML

    3778846

      Hi

      I'm trying to delete and replace the value of various nodes within XML, where the nodes I'm changing can occur more than once in my document

       

       

       

      DECLARE
        v_xml      xmltype;
        w_xml      xmltype;
       
      BEGIN
          --Generate simple XML document to pretend we received it by some interface
          v_xml := xmltype.createxml('
          <Response>
              <Card>
                    <Address attr_cust="1">
                      <Line1>abc</Line1>
                      <Line2>def</Line2>
                      <Line3>ghi</Line3>
                      <Line4>jkl</Line4>
                      <Line5>mno</Line5>
                      <PostCode>123</PostCode>
                      <Country/>
                    </Address>
                    <Email>aaa.bbb@ccc.ddd</Email>
              </Card>
              <Card>
                    <Address attr_cust="1">
                      <Line1>abc</Line1>
                      <Line2>def</Line2>
                      <Line3>ghi</Line3>
                      <Line4>jkl</Line4>
                      <Line5>mno</Line5>
                      <PostCode>123</PostCode>
                      <Country/>
                    </Address>
                    <Email>aaa.bbb@ccc.ddd</Email>
              </Card>
          </Response>
          ');

          select
            XMLQuery('copy $tmp := . modify
                      (delete nodes
                          ($tmp//Line1
                          ,$tmp//PostCode
                          ,$tmp//Email
                          ),
                      replace value of node $tmp//Line3 with "x",
                      replace value of node $tmp//@attr_cust with "0"
                      ) return $tmp'
                     PASSING v_xml RETURNING CONTENT)
              INTO w_xml
              FROM (SELECT v_xml FROM dual);

          dbms_output.put_line( w_xml.extract('/*').getClobVal() );
      END;

       

       

       

      it almost works ok, except that it errors for example if node Line3 or attribute attr_cust occur more than once in my XML document

       

      [1]: XVM-01136: [XUTY0008] Invalid target expression for 'replace'

       

       

      is there a way round this?

       

       

      Thanks

        • 1. Re: replace multiple occurrences of a node in XML
          cormaco

          You have to loop over the nodes:

           

             SELECT
                XMLQUERY('copy $tmp := . 
                          modify
                          (delete nodes
                              ($tmp//Line1
                              ,$tmp//PostCode
                              ,$tmp//Email
                              ),
                          (for $l3 in $tmp//Line3
                              return replace value of node $l3 with "x"
                          ),
                          (for $ac in $tmp//@attr_cust
                              return replace value of node $ac with "0"
                          )
                          )
                          return $tmp'
                         PASSING v_xml RETURNING CONTENT)
                  INTO w_xml
                  FROM dual;
          

           

          Output:

          <Response>
            <Card>
              <Address attr_cust="0">
                <Line2>def</Line2>
                <Line3>x</Line3>
                <Line4>jkl</Line4>
                <Line5>mno</Line5>
                <Country/>
              </Address>
            </Card>
            <Card>
              <Address attr_cust="0">
                <Line2>def</Line2>
                <Line3>x</Line3>
                <Line4>jkl</Line4>
                <Line5>mno</Line5>
                <Country/>
              </Address>
            </Card>
          </Response>
          
          
          
          1 person found this helpful