17. Dezember 2007

Relationale Daten als XML per FTP abrufen: Und das nur mit der Datenbank

English title: Get relational data as XML via FTP: Just with the database!

Heute befassen wir uns nochmal mit dem Thema XML. Vor einiger Zeit habe ich ja schon ein wenig über die Methoden zum Generieren von XML geschrieben - heute wenden wir das ganze praktisch an. Wir erzeugen mit den Inhalten der Tabellen DEPT und EMP für jedes Department ein XML-Dokument (mit den SQL/XML Funktionen) und stellen diese direkt als FTP-Download bereit. Wie im letzten Post beschrieben, kann man Pointer (REF) auf Zeilen einer Tabelle OF XMLTYPE erzeugen und mit diesen Pointern Einträge im virtuellen Dateisystem anlegen.
Heute machen wir das wieder genauso, nur wird heute keine TABLE OF XMLTYPE erstellt, sondern eine VIEW OF XMLTYPE.
Today we'll focus again on XML: Some time ago I wrote something about the different methods for generating XML in the Oracle database (The post is here, but it's not translated so far). Now we'll use it in practice: Based on the well-known tables EMP and DEPT we'll create an XML document for each Department using the SQL/XML functions. These "virtual" XML documents will then be made available for download via FTP. As described in the previous post one can create pointers (REF's) on rows in a table OF XMLTYPE and with these pointers entries in the virtual filesystem (the "XML DB Repository") can be created.
Today we'll do the same with just one little difference: We'll not create a TABLE OF XMLTYPE but a VIEW OF XMLTYPE
create view V_DEPT_XML of XMLTYPE
with object oid (extract(sys_nc_rowinfo$, '/department/@id').getnumberval())
as
select 
 XMLElement("department",
  XMLAttributes(d.deptno as "id"),
  XMLElement("name", d.dname),
  XMLElement("location", d.loc),
  XMLElement("employees",
   (
    select 
     XMLAgg(
      XMLElement("employee", 
       XMLAttributes(e.empno as "id"),
       XMLElement("name", e.ename),
       XMLElement("hiredate", e.hiredate),
       XMLElement("payment-info",
        XMLForest(e.sal as "salary", e.comm as "commission")
       )
      )
     )
     from emp e where e.deptno = d.deptno
   )
  )
 )
from dept d
/
Von besonderer Bedeutung ist die zweite Zeile WITH OBJECT OID .... Da dies eine View und keine Tabelle ist, kann auch die Objekt-ID nicht automatisch generiert werden - vielmehr muss man der Datenbank sagen, wie ein "Objekt" (hier: ein XML-Dokument) identifiziert werden soll bzw. wie die Objekt-ID aus dem XML-Dokument abgeleitet werden soll. In diesem Beispiel wird die Objekt-ID aus der DEPTNO abgeleitet.
Nun schauen wir wiederum im Data Dictionary nach den Spalten der View
The second line WITH OBJECT OID ... in the above code is very important. Since this is a view and not a table, the object id cannot be generated automatically - the user has to tell the database how the objects (the generated XML documents) are identified and therefore how to derive the object id from the XML content. In this example the object id is derived from the DEPTNO column.
Now we look into the data dictionary to get information about the view columns.
SQL> select COLUMN_NAME, DATA_TYPE, VIRTUAL_COLUMN, HIDDEN_COLUMN from user_tab_cols where TABLE_NAME='V_DEPT_XML'

COLUMN_NAME                    DATA_TYPE                      VIR HID
------------------------------ ------------------------------ --- ---
SYS_NC_ROWINFO$                XMLTYPE                        NO  NO
SYS_NC_OID$                    RAW                            NO  YES

2 Zeilen ausgewählt.
Die View hat zwei Spalten: Die Spalte SYS_NC_ROWINFO$ enthält das generierte XML Dokument, die Spalte SYS_NC_OID$ die Objekt-ID. Das entspricht in etwa dem Aufbau der TABLE OF XMLTYPE (letzter Post), diese hatte nur eine zusätzliche, versteckte Spalte mit den eigentlichen XML-Daten - da wir nun eine View haben, wird diese nicht benötigt. Nun kann man sich die Pointer (REF) ausgeben lassen.
This view has two columns: SYS_NC_ROWINFO$ is for the generated XML document, SYS_NC_OID contains the (generated) object id. This is similar to a TABLE OF XMLTYPE (previous post); the table had another additional column for the XML data itself - since this is a view such a column is not needed. Now we can get the pointers (REF) to the virtual XML documents.
SQL> select ref(e) from v_dept_xml e;

REF(E)
--------------------------------------------------------------------------------
00004A038A0046417A36A09945F1DBE040A8C08C0211B20000001426010001000100290000000000
090602002A00078401FE0000000A02C10B0000000000000000000000000000000000000000

00004A038A0046417A36A09945F1DBE040A8C08C0211B20000001426010001000100290000000000
090602002A00078401FE0000000A02C1150000000000000000000000000000000000000000

00004A038A0046417A36A09945F1DBE040A8C08C0211B20000001426010001000100290000000000
090602002A00078401FE0000000A02C11F0000000000000000000000000000000000000000

00004A038A0046417A36A09945F1DBE040A8C08C0211B20000001426010001000100290000000000
090602002A00078401FE0000000A02C1290000000000000000000000000000000000000000
Nun erzeugen wir mit folgendem PL/SQL-Code die Einträge im virtuellen Dateisystem:
Now we'll create the virtual files with the following PL/SQL code:
declare
  v_res boolean;
begin
  for i in (
    select 
      ref(e) as reference,
      extractvalue(object_value, '/department/@id') as id
    from v_dept_xml e
  ) loop
    v_res := dbms_xdb.createresource(
      abspath => '/public/department-'||i.id||'.xml',  
      data    => i.reference,                    
      sticky  => true                     
    );
  end loop;
end;
/
sho err
  
commit
/
Anschließend sieht es im Ordner /public des virtuellen Dateisystems in etwa so aus (wenn der FTP-Zugriff noch nicht funktioniert, spielt als SYS das Skript $ORACLE_HOME/rdbms/admin/catxdbdbca.sql ein):
After running this code the folder /public in the virtual filesystem (if FTP does not work, run the script $ORACLE_HOME/rdbms/admin/catxdbdbca.sql as SYS) looks like the following
220- vmware.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 vmware.de.oracle.com FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user scott tiger
331 pass required for SCOTT
230 SCOTT logged in
ftp> cd /public
250 CWD Command successful
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 SCOTT    oracle         0 DEC 17 11:16 department-10.xml
-rw-r--r--   1 SCOTT    oracle         0 DEC 17 11:16 department-20.xml
-rw-r--r--   1 SCOTT    oracle         0 DEC 17 11:16 department-30.xml
-rw-r--r--   1 SCOTT    oracle         0 DEC 17 11:16 department-40.xml
226 ASCII Transfer Complete
ftp: 292 bytes received in 0,00Seconds 292000,00Kbytes/sec.
ftp>
Laden Sie nun eine Datei herunter und öffnen Sie diese mit einem Editor - sie sehen das von der View erzeugte XML-Dokument. Und jetzt kommt's: Ändern Sie mal mit einem SQL UPDATE die Tabellen EMP bzw. DEPT (COMMIT nicht vergessen) und laden Sie das XML-Dokument nochmal herunter - Sie bekommen sofort aktuelle Daten per FTP ... und das alles mit den Bordmitteln der Datenbank!
Download a file, open it using a text editor and you'll see the XML document generated by the view. And this is the trick: Issue a SQL UPDATE command for the table EMP or DEPT and download the same file again (don't forget the COMMIT. You'll always get the most current data via FTP ... and all this without additional tools or software - just with the database!

1 Kommentar:

Patrick Wolf hat gesagt…

Ich sage nur WOW!

Patrick

Beliebte Postings