カンタン！DB2テクテク第1歩 拡張機能編: DB2 XMLエクステンダー-第4話

                		  
<?xml version="1.0"?>
<!DOCTYPE Order SYSTEM "c:\dxx\samples\dtd\neworder.dtd">
<Order Key="1">
<Customer>
<Name>American Motors</Name>
<Email>parts@am.com</Email>
</Customer>
<Part Color="black ">
<Key>68</Key>
<ExtendedPrice>3485.16</ExtendedPrice>
<Tax>6.000000e-02</Tax>
<Quantity>36</Quantity>
<Shipment>
<ShipDate>1998-07-23</ShipDate>
<ShipMode>AIR   </ShipMode>
<Comment>This shipment is ordered by an email.</Comment>
</Shipment>
<Shipment>
<ShipDate>1998-08-19<</ShipDate>
<ShipMode>BOAT  </ShipMode>
<Comment>This shipment is requested by a call. 
    
  from AM marketing.</Comment>　
  </Shipment>
  </Part>
  <Part Color="red   ">
  <Key>128</Key>
  <ExtendedPrice>3800.00</ExtendedPrice>
  <Tax>7.000000e-02</Tax>
  <Quantity>28</Quantity>
  <Shipment>
  <ShipDate>1998-12-30</ShipDate>
  <ShipMode>TRUCK </ShipMode>
  <Comment></Comment>
  </Shipment>
  </Part>
  </Order> 

                db2  connect to mydb
db2 "insert into db2xml.dtd_ref values（'neworder.dtd',
db2xml.XMLClobFromFile
（'%DB2DXXPATH%\samples\dtd\neworder.dtd'）, 0, 
 'anita', 'anita','anita'）" ←a 　
db2 "echo -------- Creating result_tab --------"
db2 "create table result_tab（doc db2xml.XMLVarchar）" ←b
		    
rem  create collection "abc"  with validation check
dxxadm enable_collection mydb abc
%DB2DXXPATH%\samples\dad\neworder.dad  ←c
		    
rem  insert data to tables
db2 "insert into order_tab values
（1, 'American Motors', 'parts@am.com'）"
db2 "insert into part_tab values
'red',  156, 1795.4, 0.02, 17, 1）"
		    
（中略）
retrieve mydb abc result_tab ←d
		    
db2 "echo -------- Displaying the 
resulting XML documents --------" ←e
 db2 "select * from result_tab"
		    
rem cleanup
 db2 drop table order_tab ←f
db2 drop table part_tab
db2 drop table ship_tabf
db2 drop table result_tab
		    
dxxadm disable_collection mydb abc ←g
db2 "delete from db2xml.dtd_ref where dtdid='neworder.dtd'"
		    
db2 terminate

                <?xml version="1.0"?> 
 <!DOCTYPE DAD SYSTEM "c:\dxx\dtd\dad.dtd"> 
<DAD>
<dtdid>neworder.dtd</dtdid> 
<validation>YES</validation> 
<Xcollection> 
<prolog>?xml version="1.0"?</prolog>
<doctype>!DOCTYPE Order SYSTEM "c:\dxx\samples\dtd\neworder.dtd"</doctype> 
<root_node> 
<element_node name="Order">
<RDB_node> 
<table name="order_tab" key="order_key"/> 　　合成する表の定義 
<table name="part_tab" key="part_key"/> 
<table name="ship_tab" key="date"/>
<condition>order_tab.order_key=part_tab.o_key 
AND part_tab.part_key=ship_tab.p_key</condition>　
</RDB_node> 
<attribute_node name="Key">
<RDB_node> 
<table name="order_tab"/> 　　　　　　　　　　Key属性のマッピング定義
<column name="order_key" type="integer"/> 
</RDB_node> 
</attribute_node> 
<element_node name="Customer">
<element_node name="Name">　　　　　　　　　　　Name要素のマッピング定義
<text_node>
<RDB_node>
<table name="order_tab"/>
<column name="customer_name" type="varchar（16）"/>
</RDB_node>
</text_node>
 </element_node>

                db2 "connect to mydb"
db2 "echo -------- Setting up the database mydb --------"
db2 "create table order_tab（order_key varchar（4）, 
customer_name varchar（16）, customer_email varchar（16）, ←a 
customer_phone varchar（16））"
db2 "create table part_tab（part_key integer, color char（6）, 
qty integer, price decimal（10,2）, tax real, 
order_key varchar（4））"
db2 "create table ship_tab（date date, mode char（6）, 
comment varchar（128）, part_key integer）"
db2 "create table result_tab（doc varchar（2000））"
		    
db2 "insert into order_tab values
（'1', 'American Motors', 'parts@am.com', '800-AM-PARTS'）"
db2 "insert into part_tab values
（156, 'red', 17, 17954.55, 0.02, '1'）"
（中略）
		    
db2 "echo -------- Calling the RDB-node XML 
retrieval stored procedure --------"
tests2x mydb %DB2DXXPATH%\samples\dad\order_rdb.dad result_tab ←b
		    
db2 "echo -------- Displaying the resulting XML documents --------" ←c 
db2 "select * from result_tab"
		    
db2 "echo -------- Cleaning up the database mydb --------"
		    
db2 "connect to mydb"
db2 "drop table order_tab" ←d
db2 "drop table part_tab"
db2 "drop table ship_tab"
db2 "drop table result_tab"
		    
 db2 "terminate"
		    
		  

                <?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "c:\dxx\dtd\dad.dtd"> 
<DAD> 
<dtdid>order_rdb.dtd</dtdid> 
<validation>NO</validation> 
<Xcollection> 
<prolog>?xml version="1.0"?</prolog> 
<doctype>!DOCTYPE Order SYSTEM "c:\dxx\samples\dtd\order_rdb.dtd"</doctype>
<root_node> 
<element_node name="Order"> 
<RDB_node> 
<table name="order_tab"/> 　　　　　　　　合成する表の定義
<table name="part_tab"/> 
<table name="ship_tab"/> 
<condition>order_tab.order_key=part_tab.order_key AND 
part_tab.part_key=ship_tab.part_key </condition> 
</RDB_node> 
<attribute_node name="Key">
<RDB_node>
<table name="order_tab"/> 　　　　　　　Key属性のマッピング定義
<column name="order_key"/> 
</RDB_node>
</attribute_node>
<element_node name="Customer">
<element_node name="Name">　　　Name要素のマッピング定義
<text_node> 
<RDB_node>
<table name="order_tab"/>
<column name="customer_name"/>
</RDB_node>              
</text_node>
</element_node>
		  

                db2 "connect to SAMPLE"
		    
cd /d %DB2DXXPATH%\bnd
db2 "bind @dxxbind.lst"
cd /d %DB2PATH%\bnd
db2 "bind @db2cli.lst"
		    
db2 "terminate"
		    
cd /d %DB2DXXPATH%\samples\cmd 
dxxadm enable_db SAMPLE
		  

                @echo off
		    rem  
		    rem  genxml_sql - driver to test SQL-to-XML mapping stored procedure.
		    rem               Result column type: varchar
		    rem               Database: sample
		    rem 
		    rem   Notice:
		    rem          1. make sure that database sample 
		    is created via db2sampl 
		    rem          2. make sure that sample database 
		    is binded with XML Extender and CLI 
		    rem          3. see ../c/tests2x.sqc for 
		    the way to call dxxGenXML（）
		    rem 
		    rem  This file is shipped with XML Extender. 
		    
		    db2 "connect to sample"
		    
		    db2 "echo -------- Creating result_tab --------"  ←a 
		    db2 "create table result_tab（doc varchar（3000））"
		    
		    db2 "echo -------- Calling the SQL-to-XML 
		    mapping stored procedure --------"
		    tests2x sample 
		    %DB2DXXPATH%\samples\dad\department.dad result_tab    ←b 
		    
		    db2 "echo -------- Displaying the resulting XML documents --------"
		    db2 "select * from result_tab" ←c 
		    
		    db2 "echo -------- Cleaning up --------"
		    db2 "drop table result_tab"        ←d 
		    
		    db2 "terminate"
		  

                <?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "c:\dxx\dtd\dad.dtd">
<DAD>
  <dtdid>department.dtd</dtdid> 
  <validation>NO</validation> 
  <Xcollection>
  <SQL_stmt>SELECT d.deptno, d.deptname, p.projno, p.prstdate, p.prstaff, 
p.projname, e.empno, e.job, e.firstnme, e.lastname 
                   FROM department d, employee e, project p 
                   WHERE e.workdept=d.deptno AND 
                    p.deptno=d.deptno AND d.deptname like '%SYSTEMS%'
                   ORDER BY deptno, projno, empno               
                    
      合成するデータを取り出すSQL文の定義 
  </SQL_stmt>
  <prolog>?xml version="1.0"?</prolog>
  <doctype>!DOCTYPE department SYSTEM "c:\dxx\dtd\department.dtd"</doctype>
  <root_node>
  <element_node name="department">
    <attribute_node name="id">   id属性のマッピング定義 
      <column name="deptno"/>
    </attribute_node> 
    <element_node name="name">
      <text_node>                     name要素のマッピング定義 
        <column name="deptname"/>
      </text_node>
    </element_node>
    <element_node name="work" multi_occurrence="NO">
      <element_node name="project">
        <attribute_node name="id">   id属性のマッピング定義 
          <column name="projno"/> 
        </attribute_node>
        <attribute_node name="start"> 
          <column name="prstdate"/>   start属性のマッピング定義 
        </attribute_node>