2 Replies Latest reply: Mar 11, 2014 5:21 AM by user4423142 RSS

    XQuery modify file oracle xml db

    user4423142

      Hi Everybody,

       

      Oracle used : 11g release 11.2.0.3.0

      I want to modify an XML file in the Oracle XML DB repository.

      The following request is OK but the file is not modified :

       

      select XMLQuery('copy $i := doc("/public/data/ft_data.xml")/data/tags[@id="MP"]

      modify (for $i in $i

      return insert nodes <tag><name>TEST</name><value>12</value></tag> into $i) return $i' returning content) from dual;

       

      I'm looking for write the request in order to really modify the file. For instance :

      select XMLQuery('for $snf in doc("/public/formulations/ft_data.xml")/data/tags[@id="MP"]

      return insert nodes <tag><name>TEST</name><value>12</value></tag> into $snf return $snf') returning content) from dual;

       

      But the syntax is wrong.

      Thanks in advance for your help.

        • 1. Re: XQuery modify file oracle xml db
          odie_63

          Hi,

           

          Oracle XML DB is not a native XML database, it's more an XML-enabled database.

          That is to say, you cannot directly update a document using a direct XQuery Update primitive, you have to use the transform (copy/modify) model and update back the original document with the modified copy, using either SQL or a PL/SQL API.

           

          A couple of examples :

          SQL> declare

            2    res boolean;

            3  begin

            4    res := dbms_xdb.CreateFolder('/public');

            5    res := dbms_xdb.CreateResource('/public/test.xml', xmltype('<data><tags/></data>'));

            6  end;

            7  /

          PL/SQL procedure successfully completed.

          SQL> update resource_view v

            2  set v.res =

            3      xmlquery(

            4      'declare namespace r = "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (::)

            5       copy $d := .

            6       modify (

            7         insert node <tag><name>TEST</name></tag>

            8         into $d/r:Resource/r:Contents/data/tags

            9       )

          10       return $d'

          11       passing v.res

          12       returning content

          13      )

          14  where equals_path(v.res, '/public/test.xml') = 1 ;

           

          1 row updated.

           

          SQL> xquery doc("/public/test.xml")

            2  /

           

          Result Sequence

          --------------------------------------------------------------------------------

          <data>

            <tags>

              <tag>

                <name>TEST</name>

              </tag>

            </tags>

          </data>

           

          SQL> declare

            2

            3    doc      xmltype;

            4    res      dbms_xdbresource.XDBResource;

            5    rpath    resource_view.any_path%type := '/public/test.xml';

            6

            7  begin

            8

            9    res := dbms_xdb.getResource(rpath);

          10    doc := dbms_xdbresource.getContentXML(res);

          11

          12    select xmlquery(

          13           'copy $d := .

          14            modify (

          15              insert nodes <tag><name>TEST2</name></tag> into $d/data/tags

          16            )

          17            return $d'

          18           passing doc

          19           returning content

          20           )

          21    into doc

          22    from dual;

          23

          24    dbms_xdbresource.setContent(res, doc);

          25    dbms_xdbresource.save(res);

          26

          27  end;

          28  /

           

          PL/SQL procedure successfully completed.

           

          SQL> xquery doc("/public/test.xml")

            2  /

           

          Result Sequence

          --------------------------------------------------------------------------------

          <data>

            <tags>

              <tag>

                <name>TEST</name>

              </tag>

              <tag>

                <name>TEST2</name>

              </tag>

            </tags>

          </data>

          • 2. Re: XQuery modify file oracle xml db
            user4423142

            Thank you very much, perfect answer as usual.