apex xml

[mrosa001]

Pessoal ,
preciso fazer uma aplicacao para guardar relatorios xml

alguém pode me ajudar?

os relatorios xml podem ficar em um diretorio ,

sendo que serao mostrados quando clicar no report ( link item ).

Obrigado

[dr_gori]

Aqui tem um LINK muito bom:
http://www.toadworld.com/platforms/orac ... files.aspx

Selecionar tudo

Loading External XML Files

When a developer is setting out to store the contents of an XML document in the database to perform queries over its content and structure, one of the first questions that arises is, "How do I get XML files in there?" Here you'll start simple and work through the steps of loading an existing XML file into a CLOB column.

CLOB (pronounced "klob" or "see-lob") stands for C haracter L arge OB ject. Think of a column of type CLOB as a very, very large VARCHAR2. It can hold character-based data like XML documents as large as four gigabytes (4GB).

Assume that there is an XML document like claim77804.xml below in a directory named C:\XMLFILES on the filesystem of the machine where our database is installed:

<!-- claim77804.xml -->
<Claim>
  <ClaimId>77804</ClaimId>
  <Policy>12345</Policy>
  <Settlements>
    <Payment Approver="JCOX">1000</Payment>
    <Payment Approver="PSMITH">1850</Payment>
  </Settlements>
  <DamageReport>
    The insured's <Vehicle Make="Volks">Beetle</Vehicle>
    broke through the guard rail and plummeted into a ravine.
    The cause was determined to be <Cause>faulty brakes</Cause>.
    Amazingly there were no casualties.
  </DamageReport>
</Claim>
Since operating systems differ in their file and directory naming conventions, Oracle abstracts these details with a logical directory object. To create a logical directory use the command:

CREATE DIRECTORY directoryname AS 'OS-Specific-Dirname';
Then use the logical directory name directoryname while working with files inside PL/SQL. In order to create a logical directory, use SQL*Plus to connect to the database as a user with the CREATE ANY DIRECTORY privilege. The SYS and SYSTEM users have this privilege by default, so the easiest way to proceed is to connect as SYS or SYSTEM and create the directory with:

CREATE DIRECTORY xmlfiles AS 'C:\xmlfiles';
Once the logical directory name exists, it is possible to grant READ permission on the directory to another user like XMLBOOK with the command:

GRANT READ ON DIRECTORY xmlfiles TO xmlbook;
This enables the XMLBOOK user to read files from the XMLFILES directory. To verify this, connect to the database as XMLBOOK and issue the SELECT statement:

SELECT directory_name,directory_path 
  FROM all_directories;
which gives a list of all the directories to which XMLBOOK has access:

DIRECTORY_NAME	                DIRECTORY_PATH
------------------------------	--------------------
XMLFILES	                C:\xmlfiles
Any time you want to refer to an operating system file in PL/SQL, use the built-in datatype for external files called a BFILE. To refer to the existing claim77804.xml file in the XMLFILES directory, use the special BFileName( ) function, whose syntax is:

BFileName('logical-directory-name','filename').
Let's immediately put this file to use. For example, it is possilbe to determine the length of the claim77804.xml file with the SELECT statement:

SELECT DBMS_LOB.getLength( BFileName('XMLFILES','claim77804.xml')) 
       AS length
  FROM dual;
which uses one of the functions in the built-in DBMS_LOB package to determine the length of a BFILE object, returning the result:

LENGTH
----------
       488
Now you're ready to load the document into the database. There are two choices:

Save a handle to the external file in a column of type BFILE
 
Save a copy of the contents of the external file in a column of type CLOB
The BFILE column offers an option that occupies virtually no space inside the database (just a file pointer) but restricts the contents of the external document to be read-only. The CLOB column option takes up space in the database but is fully readable and writable. XML documents in both BFILEs and CLOBs can be indexed for fast XML document searching across millions of rows. The need for the document content to be writable and/or the desire to have the content completely inside the database (where it cannot be accessed by file-based tools) are the factors in deciding whether to use CLOBs over BFILEs.

Let's study the CLOB example. It is possible to create a simple table named xml_documents having a "document name" as a primary key and the body of the XML document in a CLOB column:

CREATE TABLE xml_documents (
  docname    VARCHAR2(200) PRIMARY KEY,
  xmldoc     CLOB,
  timestamp  DATE
);
By adding a database trigger, the table will automatically maintain the last modified timestamp column on any XML documents stored in this table:

CREATE TRIGGER xml_documents_timestamp
BEFORE INSERT OR UPDATE ON xml_documents
FOR EACH ROW
BEGIN
  :new.timestamp := SYSDATE;
END;
With the xml_documents table in place, it is time to start inserting XML documents into it. To store an external XML file into your xml_documents table, follow these steps:

Insert a new row into xml_documents with an empty CLOB for the xmldoc column.
Retrieve the empty CLOB into a variable.
Get a BFILE handle to the external file.
Open the file for reading.
Copy the contents of the file into the CLOB variable.
Close the file and COMMIT.
The example below shows a sample insertXMLFile stored procedure that accepts as arguments the directory name, the filename, and the name associated with the document as its primary key. Then it performs the six steps above to store the external file into the xml_documents table.

Inserting an External XML File into a CLOB

CREATE OR REPLACE PROCEDURE insertXmlFile( dir VARCHAR2,
                                          file VARCHAR2,
                                          name VARCHAR2 := NULL) IS
  theBFile   BFILE;
  theCLob    CLOB;
  theDocName VARCHAR2(200) := NVL(name,file);
BEGIN
  -- (1) Insert a new row into xml_documents with an empty CLOB, and
  -- (2) Retrieve the empty CLOB into a variable with RETURNING..INTO

  INSERT INTO xml_documents(docname,xmldoc) VALUES(theDocName,empty_clob(  ))
  RETURNING xmldoc INTO theCLob;

  -- (3) Get a BFile handle to the external file
  theBFile := BFileName(dir,file);

  -- (4) Open the file
  dbms_lob.fileOpen(theBFile);

  -- (5) Copy the contents of the BFile into the empty CLOB
  dbms_lob.loadFromFile(dest_lob => theCLob,
                         src_lob => theBFile,
                         amount  => dbms_lob.getLength(theBFile));

  -- (6) Close the file and commit
  dbms_lob.fileClose(theBFile);
  COMMIT;
END;
With the insertXmlFile procedure now in place, from the SQL*Plus command-line, execute the stored procedure to load claim77804.xml from the XMLFILES directory using the syntax:

EXEC insertXmlFile('XMLFILES','claim77804.xml')
Directory names are case-sensitive and are created in uppercase by default, like other database object names. So this syntax:

CREATE DIRECTORY mydir AS 'F:\files\mydir';
creates a directory object named MYDIR, while:

CREATE DIRECTORY  "AnotherDir" AS 'W:\another\mydir';
creates a directory object named AnotherDir. When using the BFileName( ) constructor to refer to a file in a directory, the directory name is always case-sensitive, and the filename is case-sensitive if filenames on the operating system of your database server are case-sensitive. So if the directory object is named MYDIR, the function BFileName('mydir','myfile.xml') will fail because the directory object's name was referred to in lowercase.

Note this line in the example:

theDocName VARCHAR2(200) := NVL(name,file);
This ensures that if you do not pass in the optional third argument to assign a meaningful name to the file being stored, the value of theDocName will default to the name of the file being loaded and will be used during the insert as the value of the docname column in the xml_documents table.