Convert XMLTYPE to VARCHAR

Question

I'm trying to send xmltype table content on insert with an httpRequest, to do so I have to convert it to varchar to pass it in POST.

Here is my insert:

    Insert into CC.MOVIE (SYS_NC_ROWINFO$) values ('<movie>
      <id>217530</id>
      <title>Midnight Limited</title>
      <originalTitle>Midnight Limited</originalTitle>
      <release_date>1940-03-20</release_date>
      <status>3</status>
      <vote_average>0</vote_average>
      <vote_count>0</vote_count>
      <runtime>0</runtime>
      <certification/>
      <budget>61</budget>
      <tagline>DEATH RIDES THE RAILS!                                                                                                                                                        </tagline>
      <genres>
        <genre>
          <idGenre>12</idGenre>
        </genre>
        <genre>
          <idGenre>28</idGenre>
        </genre>
        <genre>
          <idGenre>80</idGenre>
        </genre>
      </genres>
      <actors>
        <actor>
          <idActor>32218</idActor>
          <characterName>Prof. Van Dillon</characterName>
        </actor>
        <actor>
          <idActor>87545</idActor>
          <characterName>Joan Marshall</characterName>
        </actor>
        <actor>
          <idActor>90336</idActor>
          <characterName>Val Lennon</characterName>
        </actor>
        <actor>
          <idActor>120708</idActor>
          <characterName>Capt. Harrigan</characterName>
        </actor>
      </actors>
      <directors>
        <director>
          <idDirector>120796</idDirector>
        </director>
      </directors>
    </movie>
    ');

and my trigger:

create or replace TRIGGER movie_insert

      AFTER INSERT ON MOVIE
      FOR EACH ROW
      DECLARE
      doc XMLType;
      req utl_http.req;
      res utl_http.resp;
      url varchar2(4000) := 'http://10.0.2.2:8088/VerifActeur/VerifActeur';
      --name varchar2(4000);
      buffer varchar2(5000); 
      BEGIN
    SELECT 
      EXTRACTVALUE(:new.object_value, '/movie/text()')
    INTO buffer
    FROM DUAL;

    --Select CAST(:new.object_value AS VARCHAR2(5000)) into buffer from dual;
      -- buffer := :new.object_value.getStringVal();
        --doc := :new.sys_nc_rowinfo$;
      --buffer:= :new.object_value.extract('/movie/text()').getStringVal();
      dbms_output.put_line(buffer);

        req := utl_http.begin_request(url, 'POST');
        --utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); 
        utl_http.set_header(req, 'content-type', 'text/xml'); 
        utl_http.set_header(req, 'Content-Length', length(buffer));

        utl_http.write_text(req, buffer);

        res := utl_http.get_response(req);
        -- process the response from the HTTP call
        begin
          loop
            utl_http.read_line(res, buffer);
            dbms_output.put_line(buffer);
          end loop;
          utl_http.end_response(res);
        exception
          when utl_http.end_of_body 
          then
            utl_http.end_response(res);
        end;
        utl_http.end_request(req); 
      END;

But the extract value output display nothing, and in this case the request is not dent (probably because the length return nothing). And other methods are returning exceptions.

Answer 1

Your movie node doesn't contain any text, it only contains child nodes; so it is correct for extractvalue to return null. That function is deprecated from 11g, incidentally. Your insert and trigger are currently looking at different columns, which might not be helping, but that may be a mistake in the question rather than your actual code.

You can use XMLSerialize to convert an XMLType value to a string, either a varchar or (if the value is too large for that data type) a CLOB, something like:

SELECT XMLSerialize(DOCUMENT :new.object_value AS VARCHAR2(4000) NO INDENT)
INTO buffer
FROM dual;

You haven't said which version of Oracle you're using. Before 12c the SQL context means you're limited to 4000 characters, but your buffer is declared as 5000. Using no indent may strip out enough whitespace for that to not be an issue, but you may have to use a CLOB instead.

With a dummy table, and inserting into the same XMLType column the trigger is querying (why are you using a column names from a data dictionary tables? Hopefully you aren't actually adding triggers to the data dictionary?) and commenting out the HTTP bits for now, that produces:

Table MOVIE created.

Trigger MOVIE_INSERT compiled

<movie><id>217530</id><title>Midnight Limited</title><originalTitle>Midnight Limited</originalTitle><release_date>1940-03-20</release_date><status>3</status><vote_average>0</vote_average><vote_count>0</vote_count><runtime>0</runtime><certification/><budget>61</budget><tagline>DEATH RIDES THE RAILS!                                                                                                                                                        </tagline><genres><genre><idGenre>12</idGenre></genre><genre><idGenre>28</idGenre></genre><genre><idGenre>80</idGenre></genre></genres><actors><actor><idActor>32218</idActor><characterName>Prof. Van Dillon</characterName></actor><actor><idActor>87545</idActor><characterName>Joan Marshall</characterName></actor><actor><idActor>90336</idActor><characterName>Val Lennon</characterName></actor><actor><idActor>120708</idActor><characterName>Capt. Harrigan</characterName></actor></actors><directors><director><idDirector>120796</idDirector></director></directors></movie>


1 row inserted.

---

Doing this in a trigger looks awkward, even if the dbms_output is only there for debugging. For example, what happens if the http call fails - should the new row still be stored, or is the exception filtered up to the caller and rolled back as you want/expect? It isn't going to be clear what is happening to whoever does the insert. It might be more robust to put the insert and the http call into a stored procedure instead so the logic is together and easy to see, instead of relying on side effects via triggers. Then block direct access to the table (for insert anyway) and only allow the wrapper procedure to be called. Just a thought...