Read xml file into SP 1

[Andrzejek]

I get this xml file with the data I need to load into my table. So my procedure looks like this (well, short version):

CREATE OR REPLACE PROCEDURE MyProcedure 
  (p_ReturnCode       IN OUT NUMBER,
   p_VBErrorMessage   IN OUT VARCHAR2)
IS

  x XMLType := XMLType([red]
    '<?xml version="1.0" encoding="utf-8" ?>
<!--Local Projects Data-->
<LocalProjects>
        <Project>
            <TPMS_ID>2102</TPMS_ID>
            <ProjectNumber>Lx-C-826--73-53</ProjectNumber>
            <Route_ID>1117402</Route_ID>
            <Beg_Milepoint>2.6565</Beg_Milepoint>
            <End_Milepoint>2.6565</End_Milepoint>
            <Beg_Latitude>42.07819</Beg_Latitude>
            <Beg_Longitude>-91.09971</Beg_Longitude>
            <End_Latitude>42.07819</End_Latitude>
            <End_Longitude>-91.09971</End_Longitude>
        </Project>
</LocalProjects>'[/red]);

BEGIN

  FOR r IN (
    SELECT ...

And all samples on the web show how to do it with hard-coded xml file (in red).

But I want to read an xml file from 'outside' the procedure.

I did try to read xml file as regular text file and then assign it to XMLType:

BEGIN

 f_in := UTL_FILE.FOPEN ('e:\data', 'MyXMLFile.xml', 'r');
   loop
      begin
        utl_file.get_line(f_in,s_in);
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
        EXIT;
        string:= string || s_in;
      end;
   end loop;[blue]
   x := XMLType(f_in);[/blue]
   utl_file.fclose(f_in);

But that did not work - an error that x is not declared.

So how do you read an xml file (not hard coded) in your stored procedure?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[stefanhei]

Hi,
your first procedure declares the variable x and initializes it immediately.
Your second code fragment is incomplete. Did you declare the variables x, f_in, s_in and string anywhere?
Another problem I see is that you read the contents of the file into the variable string but then you try to convert the filehandle to XMLType.

CREATE OR REPLACE PROCEDURE MyNewProcedure 
  (p_ReturnCode       IN OUT NUMBER,
   p_VBErrorMessage   IN OUT VARCHAR2)
IS
  [COLOR=#CC0000]x XMLType;
  f_in utl_file.file_type;
  s_in VARCHAR2(200);
  string VARCHAR2(4000);[/color] 
BEGIN
   f_in := utl_file.fopen ('e:\data', 'MyXMLFile.xml', 'r');
   loop
      begin
        utl_file.get_line(f_in,s_in);
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
        EXIT;
        string:= string || s_in;
      end;
   end loop;
   x := XMLType([COLOR=#CC0000]string[/color]);
   utl_file.fclose(f_in); 
   ...
END;
/

 

[Andrzejek]

Yes, I did declare those variables. Sorry about the confusion. With all the ‘comment this out’ and ‘un-comment that’, I missed the declarations.

I was very close, I knew it. I just needed: [tt]x := XMLType([red]string[/red]);[/tt]

Also I had to move this line to be above EXCEPTION

BEGIN
   f_in := utl_file.fopen ('e:\data', 'MyXMLFile.xml', 'r');
   loop
      begin
        utl_file.get_line(f_in,s_in);[blue]
        string:= string || s_in;[/blue]
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
        EXIT;
      end;
   end loop;
   x := XMLType(string);
   utl_file.fclose(f_in); 
   ...
END;

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.