XML használata oracle adatbázisban

XML forrás kezelési lehetőségek

Az XML file tartalmát megfelelő root (legfelső) szint kijelölésével CLOB vagy Varchar(4000) mezőbe töltjük és onnan áttöltés során megfelelően 'kiforgatjuk' egy vagy több tábla mezőibe.

Mezőhatároló megadási lehetőségek

• FIXED : Each record a fixed length (in bytes)
• VARIABLE : Start of each record contains a character count
• DELIMITED BY : Each record ends with a given string
• XMLTAG : Each record is the content within a given XML tag: <MYTAG>....</MYTAG>

Több azonos forrás fájl feldolgozás

A LOCATION akár több fájlt tartalmazhat, könyvtár meghatározással vagy anélkül megadható hogy az első vagy az összes maszknak megfelelő fájl kerüljön betöltésre : FIRST / ALL

Létrehozási minta

create table

ext_emp_tab

organization external (

type

oracle_datapump

default directory

ext_dir

location ('ext_emp1.dmp', 'ext_emp2.dmp', 'ext_emp3.dmp' )  --- Több forrás megadása

parallel 3

as select * from emp

Minta XML forrás szerkezet

file: "/tmp/test1.xml"

<employees>

  <employee><employee_number>7369</employee_number><employee_name>SMITH</employee_name><job>CLERK</job></employee>

  <employee><employee_number>7499</employee_number><employee_name>ALLEN</employee_name><job>SALESMAN</job></employee>

  <employee><employee_number>7521</employee_number><employee_name>WARD</employee_name><job>SALESMAN</job></employee>

  <employee><employee_number>7566</employee_number><employee_name>JONES</employee_name><job>MANAGER</job></employee>

  <employee><employee_number>7654</employee_number><employee_name>MARTIN</employee_name><job>SALESMAN</job></employee>

  <employee><employee_number>7698</employee_number><employee_name>BLAKE</employee_name><job>MANAGER</job></employee>

  <employee><employee_number>7782</employee_number><employee_name>CLARK</employee_name><job>MANAGER</job></employee>

  <employee><employee_number>7788</employee_number><employee_name>SCOTT</employee_name><job>ANALYST</job></employee>

  <employee><employee_number>7839</employee_number><employee_name>KING</employee_name><job>PRESIDENT</job></employee>

  <employee><employee_number>7844</employee_number><employee_name>TURNER</employee_name><job>SALESMAN</job></employee>

  <employee><employee_number>7876</employee_number><employee_name>ADAMS</employee_name><job>CLERK</job></employee>

  <employee><employee_number>7900</employee_number><employee_name>JAMES</employee_name><job>CLERK</job></employee>

  <employee><employee_number>7902</employee_number><employee_name>FORD</employee_name><job>ANALYST</job></employee>

  <employee><employee_number>7934</employee_number><employee_name>MILLER</employee_name><job>CLERK</job></employee>

</employees>

External Table (XMLTAG) : Single Tag

A legfelső szint (root egy tagcimkével történik)

Létrehozás minta

create table tab_ext (

  doc1 clob                   ---- CLOB mezőbe töltjük (ömlesztve)

organization external (

  type oracle_loader

  default directory tmp_dir

  access parameters (

    records

    xmltag ("employee")

    fields notrim

    missing field values are null (

      doc1 char(1000000)

  location ('test1.xml')

reject limit unlimited;

Áttöltéshez használat minta

select xt.*

from   tab_ext x,

       xmltable('/employee'

         passing xmltype(x.doc1)                           ---- Értelmezővel kiolvassuk (parszoljuk)

         columns

           "employee_number" number(4)    path 'employee_number',

           "employee_name"   varchar2(10) path 'employee_name',

           "job"             varchar2(9)  path 'job'

         ) xt

order by 1;

==>

EMPLOYEE_NUMBER EMPLOYEE_N JOB

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

           7369 SMITH      CLERK

           7499 ALLEN      SALESMAN

           7521 WARD       SALESMAN

           7566 JONES      MANAGER

           7654 MARTIN     SALESMAN

           7698 BLAKE      MANAGER

           7782 CLARK      MANAGER

           7788 SCOTT      ANALYST

           7839 KING       PRESIDENT

           7844 TURNER     SALESMAN

           7876 ADAMS      CLERK

           7900 JAMES      CLERK

           7902 FORD       ANALYST

           7934 MILLER     CLERK

External Table (XMLTAG) : Multiple Tags

A legfelső szint (root több tagcimke felsorolással történik)

Létrehozás minta

---- Létrehozás

create table tab_ext (

  doc1 varchar2(4000)                                   --- Varchar(4000) nem CLOB

organization external (

  type oracle_loader

  default directory tmp_dir1

  access parameters (

    records

    xmltag ("employee_number", "employee_name", "job")  ---- Root szint kijelelőlése tag listával

    fields notrim

    missing field values are null

  location ('test1.xml')

reject limit unlimited;

Áttöltéshez használat minta

---Nyers kilvasás

select doc1 from tab_ext;

DOC1

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

<employee_number>7369</employee_number>

<employee_name>SMITH</employee_name>

<job>CLERK</job>

<employee_number>7499</employee_number>

<employee_name>ALLEN</employee_name>

<job>SALESMAN</job>

<employee_number>7521</employee_number>

<employee_name>WARD</employee_name>

<job>SALESMAN</job>

...

XML tartalom kezelése (external tábla nélkül)

Minta

---  sql_okt séma használat

select * from sql_okt.emp

--- Tábla létrehozás

CREATE TABLE xml_tab (

  id        NUMBER,

  xml_data  XMLTYPE

);

----- XML betöltés hagyományos táblatartalomból generálással

DECLARE

  l_xmltype XMLTYPE;

BEGIN

  SELECT XMLELEMENT("departments",

           XMLAGG(

             XMLELEMENT("department",

               XMLFOREST(

                 d.deptno AS "department_number",

                 d.dname AS "department_name",

                 (SELECT XMLAGG(

                           XMLELEMENT("employee",

                             XMLFOREST(

                               e.empno AS "employee_number",

                               e.ename AS "employee_name"

                  FROM   sql_okt.emp e

                  WHERE  e.deptno = d.deptno

                 ) "employees"

  INTO   l_xmltype

  FROM   sql_okt.dept d;

  INSERT INTO xml_tab VALUES (1, l_xmltype);

  COMMIT;

END;

---- lekérdezés clob-ként

SET LONG 5000

SELECT x.xml_data.getClobVal()

FROM   xml_tab x;

--=>

"<departments>

  <department>

    <department_number>10</department_number>

    <department_name>ACCOUNTING</department_name>

    <employees>

      <employee>

        <employee_number>7782</employee_number>

        <employee_name>CLARK</employee_name>

      </employee>

      <employee>

        <employee_number>7839</employee_number>

        <employee_name>KING</employee_name>

      </employee>

      <employee>

        <employee_number>7934</employee_number>

        <employee_name>MILLER</employee_name>

      </employee>

    </employees>

  </department>

  <department>

    <department_number>20</department_number>

    <department_name>RESEARCH</department_name>

    <employees>

      <employee>

        <employee_number>7369</employee_number>

        <employee_name>SMITH</employee_name>

      </employee>

      <employee>

        <employee_number>7566</employee_number>

        <employee_name>JONES</employee_name>

      </employee>

      <employee>

        <employee_number>7788</employee_number>

        <employee_name>SCOTT</employee_name>

      </employee>

      <employee>

        <employee_number>7876</employee_number>

        <employee_name>ADAMS</employee_name>

      </employee>

      <employee>

        <employee_number>7902</employee_number>

        <employee_name>FORD</employee_name>

      </employee>

    </employees>

  </department>

  <department>

    <department_number>30</department_number>

    <department_name>SALES</department_name>

    <employees>

      <employee>

        <employee_number>7499</employee_number>

        <employee_name>ALLEN</employee_name>

      </employee>

      <employee>

        <employee_number>7521</employee_number>

        <employee_name>WARD</employee_name>

      </employee>

      <employee>

        <employee_number>7654</employee_number>

        <employee_name>MARTIN</employee_name>

      </employee>

      <employee>

        <employee_number>7698</employee_number>

        <employee_name>BLAKE</employee_name>

      </employee>

      <employee>

        <employee_number>7844</employee_number>

        <employee_name>TURNER</employee_name>

      </employee>

      <employee>

        <employee_number>7900</employee_number>

        <employee_name>JAMES</employee_name>

      </employee>

    </employees>

  </department>

  <department>

    <department_number>40</department_number>

    <department_name>OPERATIONS</department_name>

  </department>

</departments>

--- Lekérdezés parszolva két mező a szervezet szintről

SELECT xt.*

FROM   xml_tab x,

       XMLTABLE('/departments/department'

         PASSING xml_data

         COLUMNS

           empno     VARCHAR2(4)  PATH 'department_number',

           ename     VARCHAR2(10) PATH 'department_name'

         ) xt;

--=>

EMPNO   ENAME

10      ACCOUNTING

20      RESEARCH

30      SALES

40      OPERATIONS

--- Lekérdezés parszolva két mező a dolgozók szintről

SELECT xt.*

FROM   xml_tab x,

       XMLTABLE('/departments/department/employees/employee'

         PASSING x.xml_data

         COLUMNS

           empno     VARCHAR2(4)  PATH 'employee_number',

           ename     VARCHAR2(10) PATH 'employee_name'

         ) xt;

--=>

EMPNO   ENAME

7782    CLARK

7839    KING

7934    MILLER

7369    SMITH

7566    JONES

7788    SCOTT

7876    ADAMS

...

----- XML külömböző szintjeíról olvasás

select   X.depno,  X.dname, Y.empno, Y.ename

from        xml_tab

            ,xmltable('/departments/department'

                     passing xml_data                                   ---- XML gyökér szintről olvasás

                     columns

                      depno     VARCHAR2(4)  PATH 'department_number',

                      dname     VARCHAR2(10) PATH 'department_name',

                      dnamex    xmltype PATH './employees'              ---- Másik hierachiaszínt kijelőlése

                     ) X,

           xmltable('/employees/employee'

                     passing X.dnamex                                   ---- Új hierachia szinről forrásból olvasás

                     columns

                     empno     VARCHAR2(4)  PATH 'employee_number',

                     ename     VARCHAR2(10) PATH 'employee_name'

                    )  (+) Y

--=>

DEPNO   DNAME   EMPNO   ENAME

10      ACCOUNTING     7782    CLARK

10      ACCOUNTING     7839    KING

10      ACCOUNTING     7934    MILLER

20      RESEARCH       7369    SMITH

20      RESEARCH       7566    JONES

20      RESEARCH       7788    SCOTT

20      RESEARCH       7876    ADAMS

20      RESEARCH       7902    FORD

30      SALES       7499       ALLEN

30      SALES       7521       WARD

Dinamikusan generált xlm tartalom értelmezés

Minta

---- Dinamikusan generált XML tartalom és elemzés minta

WITH cte("XML") AS (

  SELECT '<root>

          <element>

            <uid>123</uid>

            <sub>

                <text>Lajos</text>

            </sub>

            </element>

          <element>

            <uid>456</uid>

            <sub>

                <text>Béla</text>

            </sub>

           </element>

           <element>

            <uid>789</uid>

            <sub>

                <text>Laci</text>

            </sub>

           </element>

         </root>'

  FROM dual

------   KL1

SELECT  'KL1' as TIP, x.*  ---, c.*

FROM cte c,XMLTable('//text/../..'             ---- Belépő szint kijelőlése hátulról

               PASSING XMLTYPE(c."XML")

               COLUMNS

                 azonosito VARCHAR2(4000) PATH 'uid',

                 nev VARCHAR2(40) PATH 'sub/text',

                 eltolt VARCHAR2(40) PATH '(preceding::uid)[last() -1]/data(.)' --- kettővel megelőző azonosító rekord adat felolvasás

) x

WHERE azonosito IS NOT NULL

union all

------  KL2

SELECT 'KL2' as TIP, x.* -- , c.*

FROM cte c,XMLTable('//text'                    --- kijelőljük a legalsó szintet (hátulról)

          PASSING XMLTYPE(c."XML")

          COLUMNS

           azonosito VARCHAR2(40) PATH '(preceding::uid)[last()]/data(.)'

           ,nev VARCHAR2(4000) PATH '.'

           ,eltolt VARCHAR2(40) PATH '(preceding::uid)[last() -1]/data(.)'  -- megelőző azonosító rekord adat felolvasás

) x

WHERE azonosito IS NOT NULL

union all

------  KL3

SELECT 'KL3' as TIP, x.* -- , c.*

FROM cte c,XMLTable('/root/element/sub/text'                                --- kijelőljük a legalsó szintet

          PASSING XMLTYPE(c."XML")

          COLUMNS

           azonosito VARCHAR2(40) PATH '(preceding::uid)[last()]/data(.)'

           ,nev VARCHAR2(4000) PATH '.'

           ,eltolt VARCHAR2(40) PATH '(preceding::uid)[last() ]/data(.)'  -- ugyanazt a rekord azonosító adat felolvasás

) x

WHERE azonosito IS NOT NULL

----=>

TIP     AZONOSITO      NEV     ELTOLT

KL1            123     Lajos

KL1            456     Béla

KL1            789     Laci    123

KL2            123     Lajos

KL2            456     Béla    123

KL2            789     Laci    456

KL3            123     Lajos   123

KL3            456     Béla    456

Minta3

----------------------------
--- Tábla létrehozás
CREATE TABLE EMPLOYEES
(
   id     NUMBER,
   data   XMLTYPE
);
----------------------------
---- XML tábla normál lekérdezése
select * from EMPLOYEES
=>
ID DATE
1 <XML>
----------------------------
---betöltés
INSERT INTO EMPLOYEES
     VALUES (1, xmltype ('<Employees>
    <Employee emplid="1111" type="admin">
        <firstname>John</firstname>
        <lastname>Watson</lastname>
        <age>30</age>
        <email>johnwatson@sh.com</email>
    </Employee>
    <Employee emplid="2222" type="admin">
        <firstname>Sherlock</firstname>
        <lastname>Homes</lastname>
        <age>32</age>
        <email>sherlock@sh.com</email>
    </Employee>
    <Employee emplid="3333" type="user">
        <firstname>Jim</firstname>
        <lastname>Moriarty</lastname>
        <age>52</age>
        <email>jim@sh.com</email>
    </Employee>
    <Employee emplid="4444" type="user">
        <firstname>Mycroft</firstname>
        <lastname>Holmes</lastname>
        <age>41</age>
        <email>mycroft@sh.com</email>
    </Employee>
</Employees>'));
-----------------------------------
---- Használat
   SELECT t.id, x.*
     FROM employees t,
          XMLTABLE ('/Employees/Employee[age>40]'  ---- Szűrés is bekapcsolható
          PASSING t.data
          COLUMNS
          empID    INTEGER     PATH '@emplid',     ---- attributum kiolvasása
          firstname VARCHAR2(30) PATH 'firstname', ---- Elem kiolvása
          lastname VARCHAR2(30) PATH 'lastname',
          age VARCHAR2(30) PATH 'age') x
    WHERE t.id = 1;
-----------------------------------

Expression Description

nodename Selects all nodes with the name “nodename”

/ Selects from the root node

// Selects nodes in the document from the current node that match the selection no matter where they are

. Selects the current node

.. Selects the parent of the current node

@ Selects attributes

employee Selects all nodes with the name “employee”

employees/employee Selects all employee elements that are children of employees

//employee Selects all employee elements no matter where they are in the document

XML fogalmak és validáció


 

XML (eXtensible Markup Language) : Kiterjeszthető jelölőnyelv



   Jelölő nyelv
  Általában szöveges fájlban tároljuk
  Címkékből (tag), attribútumokból és magából
     a tartalomból áll
  Hierarchikus szerkezetű


Jellemzői:


  Kis és nagybetűk különbözőek.
  Kötelező meg g adni a záró tag-eket.
  Fontos az egymásba ágyazás, így a
     zárás sorrendje.
  Mindig van egy gyökérelem, annak lehet egy
     gyermeke, mely tartalmazhat további leszármazottakat
  Bővíthető
 Elem vagy attribútum használata Nincs rá
     szabály!


 

 

Elem

Az XML dokumentumok elemeket tartalmaznak.

Minden elemnek van neve és tartalma. Ez utóbbi lehet üres is. Egy elem
tetszőleges számban és mélységben tartalmazhat további elemeket.

Az elemet meg kell nyitni és le kell zárni. Minden elemnek van lezárása!



<nev>Buga Jakab</nev>   

/* Ebben a példában definiáltunk egy nev nevű elemet, a ’Buga Jakab’
tartalommal. */



<dolgozo>

    <nev>Buga Jakab</nev>

     <eletkor>20</eletkor>

</dolgozo>

/*Ebben a példában a dolgozo elem további két elemet tartalmaz. Mindkettőnek
van neve és tartalma, és le is vannak zárva.

Amennyiben elemeket egymásba ágyazunk, úgy az nem lehet átfedő! */



Az üres tartalmú elemet kétféleképpen lehet jelölni:

<dvd></dvd>

vagy

<dvd />

 

 

Attribútum



Az XML dokumentumok attribútumokat tartalmaznak. Minden attribútumnak van
neve és tartalma. 

Ez utóbbit aposztrófok közé kell zárni:

<dolgozo neme=”Ferfi” eletkor=”20”>Buga Jakab</nev>

/*Ebben a példában definiáltunk egy nev nevű elemet, a ’Buga Jakab’
tartalommal. Két attribútumunk van, a neme és az eletkor.*/

Gyakori eset, amikor az attribútumokkal mindent leírunk, és az elem tartalma
már üres:

<dvd film=”Micimacko” ara=”2500” />

 

Speciális karakterek

Az XML -ben van néhány karakter, melynek speciális jelentése van.

Ezek a karakterek nem kerülhetnek be elem és attribútum tartalomba,
helyettesítőket kell alkalmazni:

&     &amp;

<      &lt;

>      &gt;

"       &quot;



Vagyis pl.:

<bank>K&H</bank> helyett
<bank>K&amp;H</bank> -ot kell írni.

 

UTF- 8-as vagy ISO-8859-2-es kódtáblában
szereplő karakterek használatosak.

 

Komment lehetőség

< !-- Ez egy értelmező szöveges magyarázat,

amely beilleszthető az XML file-be is -->

 

 

DTD (Document Type Definition) :Dokumentumtípus definíció



 Használható elemek meghatározása,
  elemek struktúrájának,
  adattartalom típusának megadására
  Gyakorlatban: tervrajzként funkcionál
  Megadható az XML fájlban vagy külön állományban
     is.
  Ellenőrzése az érvényesítés


 

Minta 

## XML file
<?xml version="1 0" encoding="UTF <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE cikk SYSTEM "cikk2.dtd">
<cikk>
<datum>2003/05/01</datum>
<szerzo>LAci</szerzo>
<tartalom>tananyag</tartalom>
</cikk>
 
## A külső DTD fájl:
<!ELEMENT cikk (, , ) datum, szerzo, tartalom)>
<!ELEMENT datum (#PCDATA)>
<!ELEMENT szerzo (#PCDATA)>
13
<!ELEMENT tartalom (#PCDATA)>

 

XSD (Xml Schema Definition) : XML séma definíció



 XML dokumentumok tartalmát és szerkezetét meghatározza.
     
 
   Elemeket,
      jellemzőket
   Adat típusokat
      Adat típusokat
   Elemek számára
      adhatunk előírást
   Elrendezést ~
      DTD, de több annál (XML alapú, de további lehetőséget rejt magában)
 


Példa:

<xsd:element name="NEV"
type="xsd:string"/> 

<xsd:element name="DATUMIDO"
type="xsd:dateTime"/>

 

Minta 

#### --------------- Példa 1.
 
## Xml file
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="konyv">
<xs:complexType>
<xs:sequence>
<xs:element name="szerzo" type="xs:string"/>
<xs:element name="cim" type="xs:string"/>
<lt " xs:element name="ar"t " ype= xs:i tn eger"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
 
## Az XML dokumentumban így kell megadni:
<?xml version <?xml version 1 =" 0. " encoding encoding UTF =" -8 ?> "
<konyv xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="konyv.xsd">
<szerzo>XY</szerzo>
15
<cim>XML tananyag</cim>
<ar>1000</ar>
</konyv>
 
### ------------------ Példa 2.
 
## egyszeru.xsd file
<?xml version="1.0" encoding="ISO-8859-2"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="F1VILAGBAJNOK" type="xsd:string">
</xsd:schema>
egyszeru.xml
 
## XML file
<?xml version="1.0" encoding="ISO-8859-2"?>
<F1VILAGBAJNOK>Jacques Villeneuve</F1VILAGBAJNOK>
 

 

XPath



  XML dokumentumokban történő keresésekre
  Navigációra, környezetfüggő kijelölésekre,
     formázásokra
  ~ SQL SELECT


 

Minta 

 
child::cim                                              az aktuális elem ‘cim' gyereke(i)
attribute::nyelv                                        az aktuális elem nyelv' attribútuma
/                                                       a gyökér-elem
/leltar/child::konyv/child::cim[position() = 2]         a második cim
child::cim[attribute::nyelv=en'][child::gyermek=Szöveg']   az aktuális elem azon ‘cim' gyereke,amelynek a ‘nyelv' attribútumának tartalma ‘en' és van egy
                                                           ‘gyermek gyermek' gyereke amelynek tartalma gyereke, amelynek tartalma ‘szoveg'
descendant::cim                                         a dokumentum összes ‘cim' eleme
child::*[self::cim or self::szerzo] child::*[self::cim or self::szerzo]      az aktuális az aktuális elem cim' vagy 'szerzo' elemei
 

 

 

 

XQuery



  XML dokumentumokban történő keresésekre
  XPath kifejezéseken alapul
  ~ SQL
  Adatbázis-rendszerek: Oracle IBM Microsoft által
     rendszerek: Oracle, IBM, Microsoft által támogatott


 

 

NXD (Native XML Databases) : natív XML adatbázis


      Oracle, MS SQL Server, DB2 is fel tud
dolgozni XML-t

IT, BI, DWH, DM, AI

Keresés ebben a blogban