XML használata oracle adatbázisban 2.

External tábla létrehozás

XML tartalom external táblába töltése

Tábla létrehozás

CREATE TABLE KL_XML_DEMO

(

doc1 clob

)

organization external (

type oracle_loader

DEFAULT DIRECTORY "AP_DATA_DIR"

access parameters (

records

xmltag ("Body")

fields notrim

missing field values are null (

doc1 char(1000000)

)

LOCATION

( 'lhfe_sample.xml'

)

REJECT LIMIT 0

PARALLEL ;

Minta lekéderdezések az xml tartalom megjelenítésére

Ez későbbiekben egy adatbázis nézet tudna lenni amelyen keresztül STAGE táblába tölthető lenne úgyan úgy mint a mostani external - > stage tábla töltésnél.

Első tábla

---- Első táblának megfelelő adat kiolvasása (csak pár mező) /// XML színt : /Body/RecordRR/Record

select xt.*

from KL_XML_DEMO x,

xmltable('/Body/RecordNR'

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

columns

"PRODUCT_GLOBAL_ID" number(19) path './PRODUCT_GLOBAL_ID/attribute::v', --- <PRODUCT_GLOBAL_ID v="1165001"/>

"PRODUCT_TYPE" VARCHAR2(20) path './PRODUCT_TYPE/attribute::v', --- <PRODUCT_TYPE v="KHBusinessLoan"/>

"SYSDATE_1" date path './SYSDATE/attribute::v', --- <SYSDATE v="2021-02-01"/>

"CUSTOMERTYPE" VARCHAR2(20) path './CUSTOMERTYPE/attribute::v' --- <CUSTOMERTYPE v="Retail"/>

) xt

order by 1;

Második tábla

---- Másik táblának megfelelő adat kiolvasása (csak pár mező) /// XML színt : /Body/RecordRR/Record szintről

select xt.*

from KL_XML_DEMO x,

xmltable('/Body/RecordRR/Record'

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

columns

"MO_ROWS_COUNTER" number(19) path './MO_ROWS_COUNTER/attribute::v', --- <MO_ROWS_COUNTER v="3"/>

"ROLE" VARCHAR2(20) path './ROLE/attribute::v', --- <ROLE v="FEE"/>

"FEE_NAME" VARCHAR2(20) path './FEE_NAME/attribute::v', --- <FEE_NAME v="50"/>

"FEE_BASE" VARCHAR2(20) path './FEE_BASE/attribute::v', --- <FEE_BASE s="M"/>

"FEE_CCY" VARCHAR2(20) path './FEE_CCY/attribute::v', --- <FEE_CCY v="HUF"/>

"FEE_AMOUNT" VARCHAR2(20) path './FEE_AMOUNT/attribute::v' --- <FEE_AMOUNT v="21375.00"/>

-- /// ORA-01722: érvénytelen szám átalakításnál figyelni kell

) xt

order by 1;

Két tábla összekapcsolt adatának listázása

--- Két tábla adat közös kiolvasása részletezettség miatt (csak pár mező) /// XML színt : /Body/RecordRR/Record szintről

select xt.*

from KL_XML_DEMO x,

xmltable('/Body/RecordRR/Record' ---- Ettől függetlenül fellépünk szülő szinte is hivatkozással

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

columns

"PRODUCT_GLOBAL_ID" number(19) path './../../RecordNR/PRODUCT_GLOBAL_ID/attribute::v', --- Szűlő <PRODUCT_GLOBAL_ID v="1165001"/>

"PRODUCT_TYPE" VARCHAR2(20) path './../../RecordNR/PRODUCT_TYPE/attribute::v', --- Szűlő <PRODUCT_TYPE v="KHBusinessLoan"/>

"MO_ROWS_COUNTER" number(19) path './MO_ROWS_COUNTER/attribute::v', --- <MO_ROWS_COUNTER v="3"/>

"ROLE" VARCHAR2(20) path './ROLE/attribute::v', --- <ROLE v="FEE"/>

"FEE_NAME" VARCHAR2(20) path './FEE_NAME/attribute::v', --- <FEE_NAME v="50"/>

"FEE_BASE" VARCHAR2(20) path './FEE_BASE/attribute::v', --- <FEE_BASE s="M"/>

"FEE_CCY" VARCHAR2(20) path './FEE_CCY/attribute::v', --- <FEE_CCY v="HUF"/>

"FEE_AMOUNT" VARCHAR2(20) path './FEE_AMOUNT/attribute::v' --- <FEE_AMOUNT v="21375.00"/>

-- /// ORA-01722: érvénytelen szám átalakításnál figyelni kell

) xt

order by 1;

SqlDeveloper xml típus létrehozása:

Minták:

Example 1: Using XMLQuery with PASSING Clause

SELECT warehouse_name,

XMLQuery(

'for $i in /Warehouse

where $i/Area > 80000

return <Details>

<Rail>{if ($i/RailAccess = "Y")

then "true" else "false"}

</Rail>

</Details>'

PASSING warehouse_spec RETURNING CONTENT) big_warehouses

FROM warehouses;

Example 2: Using XMLTable to generate a relational view over XML data.

SELECT lines.lineitem, lines.description, lines.partid,

lines.unitprice, lines.quantity

FROM purchaseorder,

XMLTable('for $i in /PurchaseOrder/LineItems/LineItem

where $i/@ItemNumber >= 8

and $i/Part/@UnitPrice > 50

and $i/Part/@Quantity > 2

return $i'

PASSING OBJECT_VALUE

COLUMNS lineitem NUMBER PATH '@ItemNumber',

description VARCHAR2(30) PATH 'Description',

partid NUMBER PATH 'Part/@Id',

unitprice NUMBER PATH 'Part/@UnitPrice',

quantity NUMBER PATH 'Part/@Quantity')

lines;

----

Example 7: Using XMLCast() in GROUP BY / ORDER BY

SELECT XMLCAST(XMLQUERY(‘$p/PurchaseOrder/@poDate’ PASSING T.X

RETURNING CONTENT) AS DATE), COUNT(*)

FROM purchaseorder T

WHERE …

GROUP BY XMLCAST(XMLQUERY(‘$p/PurchaseOrder/@poDate’ PASSING T.X

RETURNING CONTENT) AS DATE)

ORDER BY XMLCAST(XMLQUERY(‘$p/PurchaseOrder/@poDate’ PASSING T.X

RETURNING CONTENT) AS DATE);

Example 8: Using XMLTable() construct for GROUP BY / ORDER BY

SELECT po.DATE, po.poZip, count(*)

FROM purchaseorder T,

XMLTable('$p/PurchaseOrder’

PASSING T.X AS “p”

COLUMNS

poDate DATE PATH '@poDate',

poZip VARCHAR2(8) PATH 'shipAddress/zipCode',

) po

WHERE ….

GROUP BY po.poDate, po.poZip

ORDER BY po.poDate, po.poZip

----

SELECT

EXTRACT(t.xml_data,'/departments/department/department_number/text()').getStringVal(),

EXTRACT(t.xml_data,'/departments/department/department_name/text()').getStringVal()

FROM

xml_tab t;

----

SELECT

XMLQuery(

'for $i in /departments/department

return $i/department_number '

PASSING xml_data RETURNING CONTENT) aa1

/*XMLCAST(XMLQuery(

'for $i in /departments/department

return $i '

PASSING xml_data RETURNING CONTENT) AS VARCHAR2(200) ) aa1

/*,

XMLQUERY('$INPT_XML/departments/department/department_number/text()'

PASSING xml_data AS "INPT_XML" RETURNING CONTENT) aa2

FROM xml_tab;

-----

---Kikeresés

SELECT id FROM purchase_orders

WHERE contains(doc, '(lawn WITHIN comment) WITHIN item') > 0;

---

---Nézet deff

CREATE OR REPLACE VIEW pharma.drugs_vw AS

SELECT d.*

FROM drugs_xmltype dx, XMLTable(XMLNamespaces(default 'http://drugbank.ca'),

'/drugs/drug'

PASSING dx.OBJECT_VALUE COLUMNS

drugbank_id VARCHAR2(20) PATH 'drugbank-id',

drug_name VARCHAR2(50) PATH 'name',

description VARCHAR2(4000) PATH 'description',

cas_number VARCHAR2(20) PATH 'cas-number',

general_references VARCHAR2(4000) PATH 'general-references',

synthesis_reference VARCHAR2(4000) PATH 'synthesis-reference',

indication VARCHAR2(4000) PATH 'indication',

pharmacology VARCHAR2(4000) PATH 'pharmacology',

mechanism_of_action VARCHAR2(4000) PATH 'mechanism-of-action',

toxicity VARCHAR2(4000) PATH 'toxicity',

biotransformation VARCHAR2(4000) PATH 'biotransformation',

absorption VARCHAR2(4000) PATH 'absorption',

half_life VARCHAR2(4000) PATH 'half-life',

protein_binding VARCHAR2(4000) PATH 'protein-binding',

route_of_elimination VARCHAR2(4000) PATH 'route-of-elimination',

volume_of_distribution VARCHAR2(4000) PATH 'volume-of-distribution',

clearance VARCHAR2(4000) PATH 'clearance'

) d

ORDER BY d.drugbank_id

;

---

CREATE OR REPLACE VIEW pharma.secondary_accession_number_vw AS

SELECT dt.drugbank_id, dt.cas_number, sa.*

FROM drugs_xmltype dx,

XMLTABLE(XMLNamespaces(DEFAULT 'http://drugbank.ca'), '/drugs/drug'

PASSING dx.OBJECT_VALUE COLUMNS

drugbank_id VARCHAR2(20) PATH 'drugbank-id',

cas_number VARCHAR2(20) PATH 'cas-number',

secondary_accession_number XMLTYPE PATH 'secondary-accession-numbers/secondary-accession-number') dt,

XMLTABLE(XMLNamespaces(DEFAULT 'http://drugbank.ca'), '/secondary-accession-number'

PASSING dt.secondary_accession_number COLUMNS

secondary_accession_number VARCHAR2(20) PATH '/') sa

ORDER BY dt.drugbank_id

;

----

QL> CREATE TABLE xmltablestore (

2 key_id VARCHAR2(10) PRIMARY KEY

3 , xmlloaddate DATE

4 , xml_column XMLTYPE

5 );

Table created.

SQL> INSERT INTO xmltablestore

2 VALUES ('1', sysdate, XMLType(bfilename('TEST_DIR', 'PROD_20110725_211550427_220b.xml'),nls_charset_id('AL32UTF8')))

3 ;

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT extract(xml_column, '//MapItem/@ProductNum') ProductNum

2 FROM xmltablestore

3 ;

PRODUCTNUM

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

63481062975

SQL> SELECT extractValue(xml_column, '//MapItem/@ProductNum') ProductNum

2 FROM xmltablestore

3 ;

PRODUCTNUM

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

63481062975

SQL> SELECT xmlcast(

2 xmlquery('/Entries/Category/MapItem/@ProductNum'

3 passing t.xml_column

4 returning content

5 )

6 as number

7 ) ProductNum

8 FROM xmltablestore t

9 ;

PRODUCTNUM

----------

6,3481E+10

SQL> SELECT xmlcast(

2 xmlquery('/Entries/Category/MapItem/@ProductNum'

3 passing t.xml_column

4 returning content

5 )

6 as varchar2(30)

7 ) ProductNum

8 FROM xmltablestore t

9 ;

PRODUCTNUM

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

63481062975

---

SELECT xtab.poref, xtab.priority, xtab.contact

FROM purchaseorder,

XMLTable('for $i in /PurchaseOrder

let $spl := $i/SpecialInstructions

where $i/CostCenter eq "A10"

return <PO>

<Ref>{$i/Reference}</Ref>

{if ($spl eq "Next Day Air" or $spl eq "Expedite") then

<Type>Fastest</Type>

else if ($spl eq "Air Mail") then

else ()}

<Name>{$i/Requestor}</Name>

</PO>'

PASSING OBJECT_VALUE

COLUMNS poref VARCHAR2(20) PATH 'Ref',

priority VARCHAR2(8) PATH 'Type' DEFAULT 'Regular',

contact VARCHAR2(20) PATH 'Name') xtab;

Vegy hagyományos és xml mezőket tartalmazó forrás állomány external táblás kezelése/betöltése adatbázis táblába.

--- select * from KL_XML_CLOB

--- drop table KL_XML_CLOB

--- select access_parameters from user_external_tables where table_name = 'KL_XML_CLOB';

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

CREATE TABLE KL_XML_CLOB

( ID NUMBER(9),

PROCESS_CODE VARCHAR2(26),

CALL_ID NUMBER(3),

SERVICE_NAME VARCHAR2(128),

ENTITY_TYPE VARCHAR2(26),

ENTITY_ID NUMBER(8),

USERNAME VARCHAR2(26),

REQUEST_TS VARCHAR2(26),

REQUEST_XML clob, --- VARCHAR2(4000),

RESPONSE_TS VARCHAR2(26),

RESPONSE_XML clob, --- VARCHAR2(4000),

CALL_PURPOSE VARCHAR2(26),

WORKFLOW_STATUS NUMBER(7),

EQCUSTOMERID VARCHAR2(26),

CMDBID NUMBER(12))

ORGANIZATION EXTERNAL

( TYPE ORACLE_LOADER

DEFAULT DIRECTORY AP_DATA_DIR

ACCESS PARAMETERS

(records delimited BY '\r\n'

NOBADFILE

NODISCARDFILE

NOLOGFILE

skip 1

fields terminated BY 'ß'

lrtrim

missing field VALUES are NULL

( ID CHAR(4000),

PROCESS_CODE CHAR(4000),

CALL_ID CHAR(4000),

SERVICE_NAME CHAR(4000),

ENTITY_TYPE CHAR(4000),

ENTITY_ID CHAR(4000),

USERNAME CHAR(4000),

REQUEST_TS CHAR(4000),

REQUEST_XML CHAR(1400000),

RESPONSE_TS CHAR(4000),

RESPONSE_XML CHAR(1400000),

CALL_PURPOSE CHAR(4000),

WORKFLOW_STATUS CHAR(4000),

EQCUSTOMERID CHAR(4000),

CMDBID CHAR(4000)

)

LOCATION ('S1_log_xml_clob_export.txt')

)

REJECT LIMIT UNLIMITED;

IT, BI, DWH, DM, AI

Keresés ebben a blogban