Oracle XML DB

 

NOTE: Creating a new user is not strictly necessary，the user with the same (or greater) privileges as those listed in Step 1.

注意：必须建立一个新的用户方可执行。这个用户必须拥有跟步骤1同样的权限

 

Step 1: Creating the Database User like:

 

1.建立用户

1. -- create the database user
2. -- adjust tablespace names as necessary for your environment
3. create user otnxml identified by demo
4. default tablespace users
5. temporary tablespace temp
6. quota unlimited on users;

2.赋予权限

1. -- grant system privileges and roles to the user
2. grant create session,      
3. alter session,      
4. create table,      
5. create trigger,      
6. create type,      
7. create any directory,      
8. drop any directory,      
9. xdbadmin
10. to otnxml; 

 

create session—This privilege allows the user to create a session with the database. Without this privilege the user would not be able to connect (or log on) to the database.

alter session—This privilege allows the user to alter attributes related to the session with the database. This is required to successfully execute the registerSchema procedure.

create table—This privilege allows the user to create a table in the database.

create trigger—This privilege allows the user to create a trigger on a table owned by the user.

create type—This privilege allows the user to create a new type in the database. The XML elements will be persisted in the database using types.

create any directory—This privilege allows the user to create a directory object. A directory object will be used to load the XML Instance Document into the database. This is a fairly powerful privilege and should be granted only to trusted users. Because directory objects reside in the system namespace within the database, the "any" keyword is required. There is no user-level "create directory" privilege.

drop any directory—This privilege is the counterpart to the create any directory privilege. It allows the user to drop a directory object. Like the create any directory privilege, this privilege should be granted only to trusted users due to its system-level nature.

xdbadmin role —This privilege allows the user to create directories in the XML DB engine

 

Step 2: Creating the XML Schema

 

The xml file: d:/XML/Player.xml

1. <?xml version="1.0"?>
2. <Player    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    
3.     xsi:noNamespaceSchemaLocation="Player.xsd">  
4.     <PlayerName>Steven Gerrard</PlayerName>  
5.     <PlayerPosition>Midfield</PlayerPosition>  
6.     <PlayerNumber>8</PlayerNumber>  
7.     <PlayerClub>Localhost</PlayerClub>
8. </Player> 

XML Schemas define the elements of your XML files.

http://www.w3schools.com/schema/schema_simple.asp

 

根据xml文件，建立XML Schemas文件
The XML Schemas: d:/XML/Player.xsd

1. <?xml version="1.0" ?>
2. <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"            
3.         xmlns:xdb="http://xmlns.oracle.com/xdb"            
4.         xdb:storeVarrayAsTable="true"     version="1.0">  
5.         <xsd:element name="Player" xdb:defaultTable="PLAYER">    
6.             <xsd:complexType xdb:SQLType="PLAYER_T">      
7.                 <xsd:sequence>        
8.                 <xsd:element name="PlayerName" type="xsd:string" minOccurs="1" xdb:SQLName="PLAYER_NAME" />        
9.                 <xsd:element name="PlayerPosition" type="xsd:string" minOccurs="1" xdb:SQLName="PLAYER_POSITION" />    
10.                 <xsd:element name="PlayerNumber" type="xsd:positiveInteger"  minOccurs="1" xdb:SQLName="PLAYER_NUMBER" />  
11.                 <xsd:element name="PlayerClub" type="xsd:string" minOccurs="1"  xdb:SQLName="PLAYER_CLUB" /> 
12.                 </xsd:sequence>    
13.             </xsd:complexType>  
14.         </xsd:element>
15. </xsd:schema> 

 

Step 3: Registering the XML Schema Document

 

1.建立xml路劲   

1. -- create the directory object that represents the directory2.           
2. -- where the schema and instance documents reside
3. create directory OTNXML_DIR as ‘d:/XML’ 

2.注册XML Schemas文件

1. -- register the xml schema with xml db
2. begin
3. dbms_xmlschema.registerSchema(
4.   schemaurl=>‘http://localhost:8080/home/OTNXML/source/xsd/Player.xsd’,
5.   schemadoc=>bfilename(‘OTNXML_DIR’,’Player.xsd’),
6.   local=>TRUE,
7.   gentypes=>TRUE,
8.   genbean=>FALSE,
9.   force=>FALSE,
10.   owner=>‘OTNXML’,
11.   csid=>nls_charset_id(‘AL32UTF8’)
12. );
13. end;
14. /

注意：如果该注册信息错误，可以进行删除再建。以下是删除注册：

1. BEGIN  
2.     DBMS_XMLSCHEMA.deleteSchema(  
3.         SCHEMAURL => ‘Player.xsd’,  
4.         DELETE_OPTION => dbms_xmlschema.DELETE_CASCADE_FORCE);
5. END;
6. / 

Step 4: Loading the XML Instance Document

1. insert into player
2. values(XMLType(bfilename(‘OTNXML_DIR’,’Player.xml’),nls_charset_id(‘AL32UTF8’) ));
4. commit;

1. select value(a).getClobVal()  from player a ; 

Results of the statement: OBJECT_VALUE------------------------------------------------------------------------------- <?xml version="1.0"?>  <Player xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://localhost:8080/home/OTNXML/source/xsd/Player.xsd">   <PlayerName>Steven Gerrard</PlayerName>   <PlayerPosition>Midfield</PlayerPosition>   <PlayerNumber>8</PlayerNumber>   <PlayerClub>Localhost</PlayerClub>  </Player> 1 row selected.

1. select           
2.     extractvalue (value(t),'/Player/PlayerName')  PlayerName,      
3.     extractvalue (value(t),'/Player/PlayerPosition')  PlayerPosition,      
4.     extractvalue (value(t),'/Player/PlayerNumber')  PlayerNumber,      
5.     extractvalue (value(t),'/Player/PlayerClub')  PlayerClub        
6. from  PLAYER t 

Results of the statement: PLAYERNAME-------------PLAYERPOSITION --------------PLAYERNUMBER----------------PLAYERCLUB --------------------------- Steven Gerrard            Midfield                        8                    Localhost  1 row selected.

 

 

 

复杂格式的xml文件处理
--------------------------------------------------------------------
--Player.xml----------------------------------------------------
--------------------------------------------------------------------

1. <?xml version="1.0"?>
2. <Player
3.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4.     xsi:noNamespaceSchemaLocation="Player.xsd">
5.  <H>
6.   <HName>aaa</HName>
7.  </H>
8.  <Player>
9.   <PlayerName>1a</PlayerName>
10.   <PlayerPosition>vxccvbvcv</PlayerPosition>
11.   <PlayerNumber>2</PlayerNumber>
12.   <PlayerClub>Localhost</PlayerClub>
13.  </Player>
14.  <Player>
15.   <PlayerName>2a</PlayerName>
16.   <PlayerPosition>vxccvbvcv</PlayerPosition>
17.   <PlayerNumber>2</PlayerNumber>
18.   <PlayerClub>Localhost</PlayerClub>
19.  </Player>
20.  <Player>
21.   <PlayerName>3a</PlayerName>
22.   <PlayerPosition>vxccvbvcv</PlayerPosition>
23.   <PlayerNumber>2</PlayerNumber>
24.   <PlayerClub>Localhost</PlayerClub>
25.  </Player>
26. </Player>

--------------------------------------------------------------------
--Player.xsd的格式-------------------------------------------
--------------------------------------------------------------------

1. <?xml version="1.0" ?>
2. <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
3.             xmlns:xdb="http://xmlns.oracle.com/xdb"
4.             xdb:storeVarrayAsTable="true"
5.             version="1.0">
6.  <xsd:element name="Player" type="Playertype" xdb:defaultTable="PLAYER"/>
7.  <xsd:complexType name="Playertype"  xdb:SQLType="PLAYER_T">
8.  <xsd:sequence>
9.    <xsd:element name="Player"  maxOccurs="unbounded" type="Playtype"/>
10.    <xsd:element name="H" maxOccurs="unbounded" type="Htype"/>
11.  </xsd:sequence>
12. </xsd:complexType>
14.  <xsd:complexType name="Playtype"> 
15.    <xsd:sequence>
16.         <xsd:element name="PlayerName" type="xsd:string" minOccurs="1"
17.              xdb:SQLName="PLAYER_NAME" />
18.         <xsd:element name="PlayerPosition" type="xsd:string" minOccurs="1"
19.              xdb:SQLName="PLAYER_POSITION" />
20.         <xsd:element name="PlayerNumber" type="xsd:positiveInteger"
21.              minOccurs="1" xdb:SQLName="PLAYER_NUMBER" />
22.         <xsd:element name="PlayerClub" type="xsd:string" minOccurs="1"
23.              xdb:SQLName="PLAYER_CLUB" />
24.       </xsd:sequence>
25.   </xsd:complexType>
27.   <xsd:complexType name="Htype"> 
28.    <xsd:sequence>
29.         <xsd:element name="HName" type="xsd:string" minOccurs="1"
30.              xdb:SQLName="H_NAME" />
31.      </xsd:sequence>
32.   </xsd:complexType>
34. </xsd:schema>

--------------------------------------------------------------------
--Player.xsd注册----------------------------------------------
--------------------------------------------------------------------

1. BEGIN
2.   DBMS_XMLSCHEMA.deleteSchema(
3.   SCHEMAURL => 'Player.xsd',
4.   DELETE_OPTION => dbms_xmlschema.DELETE_CASCADE_FORCE);
5. END;
6. /
8. begin
9. dbms_xmlschema.registerSchema(
10.   schemaurl=>'Player.xsd',
11.   schemadoc=>bfilename('OTNXML_DIR','Player.xsd'),
12.   local=>TRUE,
13.   gentypes=>TRUE,
14.   genbean=>FALSE,
15.   force=>FALSE,
16.   owner=>'system',
17.   csid=>nls_charset_id('AL32UTF8')
18. );
19. end;
20. /

--------------------------------------------------------------------
--XML表的操作----------------------------------------------
--------------------------------------------------------------------

1. CREATE TABLE Player of XMLType   
2. XMLSCHEMA "Player.xsd"   
3. ELEMENT "Player"; 
5. insert into player
6. values(XMLType(bfilename('OTNXML_DIR','Player.xml'),nls_charset_id('AL32UTF8')));
8. commit；

----当H为记录行-----------------------------------------------

1. Select 
2. extractValue(value(b),'/Player/H/HName') Hname,
3. extractValue(value(b),'/Player/PlayerName') trName ,
4. extractValue(value(b),'/Player/PlayerPosition') trPosition,
5. extractValue(value(b),'/Player/PlayerNumber') trNumber,
6. extractValue(value(b),'/Player/PlayerClub') trClub
7. from 
8. Player, table(XMLSequence(extract(OBJECT_VALUE, '//Player'))) b

----当H为表头字段--------------------------------------------

1. Select 
2. extractValue(OBJECT_VALUE,'/Player/H/HName') Hname,
3. extractValue(value(b),'/Player/PlayerName') trName ,
4. extractValue(value(b),'/Player/PlayerPosition') trPosition,
5. extractValue(value(b),'/Player/PlayerNumber') trNumber,
6. extractValue(value(b),'/Player/PlayerClub') trClub
7. from 
8. Player, table(XMLSequence(extract(OBJECT_VALUE, '//Player'))) b
10. ==等于==
12. Select 
13. extractValue(value(a),'/Player/H/HName') Hname,
14. extractValue(value(b),'/Player/PlayerName') trName ,
15. extractValue(value(b),'/Player/PlayerPosition') trPosition,
16. extractValue(value(b),'/Player/PlayerNumber') trNumber,
17. extractValue(value(b),'/Player/PlayerClub') trClub
18. from 
19. Player a, table(XMLSequence(extract(value(a), '//Player'))) b
20. where extractValue(value(b),'/Player/H/HName')  is null;

---------Update------------------------

1. UPDATE Player p
2.   SET OBJECT_VALUE = updateXML(OBJECT_VALUE, '/Player/Player/PlayerName/text()', '21')
3.   WHERE extractValue(OBJECT_VALUE, '/Player/H/HName') = 'aaa';
5. UPDATE Player
6.   SET OBJECT_VALUE = updateXML(OBJECT_VALUE, '/Player/H/HName/text()', 'wq')
7.   WHERE extractValue(OBJECT_VALUE, '/Player/H/HName') = 'aaa';
8.   
9. UPDATE Player p
10.   SET OBJECT_VALUE = updateXML(OBJECT_VALUE,
11.    '/Player/Player/PlayerName/text()', '1221', 
12.    '/Player/Player/PlayerPosition/text()','1234rr')
13.    WHERE existsNode(OBJECT_VALUE, '/Player/Player[PlayerName="1a"]') = 1;