Есть интересный пример по мапингу XML.. одна проблема пример загрузки XML приведен на Java на Pl/sql нетути .. к сожалению. .
Oracle Database 10g XML & SQL: Design, Build & Manage XML Applications in Java, C, C++ & PL/SQL
Object Views
If an XML document is not in the canonical format, you can create object views or XMLType views, to allow XSU to map XML documents to database tables. In the following contact.xml XML document, the contact information is stored as follows:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
<Contact_List>
<Contact>
<User_id>userid</User_id>
<First_Name>Steve</First_Name>
<Last_Name>Jones</Last_Name>
<Business>
<Email>Steve.Jones@oracle.com<;/Email>
<Phone>( 650 ) 5769801 </Phone>
<Address>
<Street1>4op11</Street1>
<Street2> 500 Oracle Parkway</Street2>
<City>Redwood Shores</City>
<State>CA</State>
<Zipcode> 94065 </Zipcode>
<Country>USA</Country>
</Address>
</Business>
</Contact>
</Contact_List>
Oracle Database 10g XML & SQL: Design, Build & Manage XML Applications in Java, C, C++ & PL/SQL
The database schema is defined as follows:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
CREATE TYPE address_typ AS OBJECT(
street1 VARCHAR2( 200 ),
street2 VARCHAR2( 200 ),
city VARCHAR2( 100 ),
state VARCHAR2( 20 ),
zipcode VARCHAR2( 20 ),
country VARCHAR2( 20 ));
/
CREATE TABLE contact_tbl(
contactid VARCHAR2( 15 ) PRIMARY KEY,
firstname VARCHAR2( 100 ),
lastname VARCHAR2( 200 ),
midname VARCHAR2( 50 ),
business_phone VARCHAR2( 20 ),
home_phone VARCHAR2( 10 ),
cell_phone VARCHAR2( 20 ),
business_addr address_typ,
business_email VARCHAR2( 150 ));
Oracle Database 10g XML & SQL: Design, Build & Manage XML Applications in Java, C, C++ & PL/SQL
Using the canonical mapping, the XML document cannot directly map to the table columns as the document contains multiple levels; thus, to insert this XML document, you need to create the following object view:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE TYPE contactinfo_type AS OBJECT(
phone VARCHAR2( 20 ),
email VARCHAR2( 150 ),
address address_typ);
/
-- Create Object View
CREATE VIEW contact_view AS
SELECT contactid AS user_id, firstname AS first_name, lastname AS
last_name,midname AS mid_name,
contactinfo_type(business_phone,business_email,
business_addr) AS business
FROM contact_tbl;
Oracle Database 10g XML & SQL: Design, Build & Manage XML Applications in Java, C, C++ & PL/SQL
Then, you can run a similar command to load the XML file into CUSTOMER_VIEW:
1.
2.
java OracleXML putXML -conn "jdbc:oracle:thin:@localhost:1521:orclX"
-user "demo/demo" -fileName "contacts.xml" "contact_view"
Я как довольно слабый в части pl/sql не могу понять можно ли с помощью данного решения решить проблему хранения XML сообщений смешанной структуры (вертикальная+горизонтальная) с хорошей степенью вложенности контейнеров..
И еще как посредством pl/sql сделать мапинг XML таки в эту конструкцию...
Извиняюсь на назойливость.. Но проект горит и уже сзати попахивает паленным.. Сорри
