developerWorks 图书频道: DB2 V9/9.5 高级应用开发，第 4 章

For  $d in db2-fn:xmlcolumn( ‘ booktable.bookinfo ’ )/book 
 Let $aut := $d//authors/author 
 Where $d/price 
 
 Order by $d//authors/author/@ID 
 Return  <AuthorList>  
   {$aut} 
   </AuthorList >

CREATE NICKNAME books 
   (title VARCHAR(32) OPTIONS(XPATH ‘ ./title/text() ’ )， 
   pk VARCHAR(16) OPTIONS(PRIMARY_KEY ‘ YES ’ ))  
   FOR SERVER xml_server  
   OPTIONS(FILE_PATH ‘ C:\book.xml ’， XPATH ‘ /book ’ )  
 CREATE NICKNAME authors 
   (id INTEGER OPTIONS(XPATH ‘ ./author/@id ’ )， 
   name VARCHAR(32) OPTIONS(XPATH ‘ ./author/text() ’ )， 
   fk VARCHAR(16) OPTIONS(FOREIGN_KEY ‘ books ’ )) 
   FOR SERVER xml_server 
   OPTIONS(XPATH ‘ ./authors ’ )

<xsd:element name= “ book" type="xsd:string “ 
  db2-xdb:rowSet="books" 
  db2-xdb:column= “ bookInfo"/>

insert into books (bookID,bookInfo) values (2356, XMLValidate(?))

<doc> 
   <employee> 
   <empname>Smith</empname> 
   <salary>24000</salary> 
   <empno>20607</empno> 
  <commission>.2</commission> 
   </employee> 
   <employee> 
   <empname>Smith</empname> 
   <salary>12000</salary> 
   <empno>40203</empno> 
   <commission>.7</commission> 
   </employee> 
  <employee> 
   <empname>Jones</empname> 
   <salary>20000</salary> 
   <empno>492399</empno> 
   <commission>.5</commission> 
   </employee> 
 </doc>

CREATE TABLE xmltable ( num INTEGER,xdata XML ) 
 INSERT INTO xmltable VALUES \ 

  (1,XMLPARSE(DOCUMENT'<doc> <type>car</type> 
  <make>Pontiac</make> 
  <model>Sunfire</model> 
  </doc>'PRESERVE WHITESPACE)), \ 
  (2,XMLPARSE(DOCUMENT'<doc> <type>car</type> 
  <make>Mazda</make> <model>Miata</model> 
   </doc>'PRESERVE WHITESPACE)), \ 
  (3,XMLPARSE(DOCUMENT'<doc> <type>person</type> 
  <name>Mary</name> <town>Vancouver</town> 
  <street>Waterside</street> </doc>'PRESERVE WHITESPACE)), \ 
  (4,XMLPARSE(DOCUMENT'<doc> <type>person</type> 
  <name>Mark</name> <town>Edmonton</town> 
  <street>Oak</street> </doc>'PRESERVE WHITESPACE)), \ 
  (5,XMLPARSE(DOCUMENT'<doc> <type>animal</type> 
  <name>dog</name> </doc>'PRESERVE WHITESPACE)), \ 
  (6,NULL),  \ 
  (7,XMLPARSE(DOCUMENT'<doc> <type>car</type> 
   <make>Ford</make> <model>Taurus</model> 
   </doc>'PRESERVE WHITESPACE)), \ 
  (8,XMLPARSE(DOCUMENT'<doc> <type>person</type> 
  <name>Kim</name> <town>Toronto</town> 
  <street>Elm</street> </doc>'PRESERVE WHITESPACE)), \ 
  (9,XMLPARSE(DOCUMENT'<doc> <type>person</type> 
  <name>Bob</name> <town>Toronto</town> 
  <street>Oak</street> </doc>'PRESERVE WHITESPACE)), \ 
  (10,XMLPARSE(DOCUMENT'<doc> <type>animal</type> 
  <name>bird</name> </doc>'PRESERVE WHITESPACE))

xquery for $d in db2-fn:xmlcolumn('XMLTABLE.XDATA')/doc \ 
 where $d/type = "car" \ 
 return <CarName> \ 
 {$d/name } \ 
 </CarName> 

 xquery for $d in db2-fn:sqlquery('select XDATA from XMLTABLE where NUM = 3')/doc \ 
 where $d/type = "car" \ 
 return ( <CarName> \ 
 {$d/name } \ 
 </CarName> )

Chapter 5. External routines 
  C and C++ routines 
  Examples of C and C++ routines 
  Example: XML and XQuery support in C procedure

create procedure xmlProc1 ( IN inNUM integer, \ 
 IN inXML xml as clob (1k), \ 
 out out1XML xml as clob(1k), \ 
 out out2XML xml as clob(1k)) \ 
 language c \ 
 parameter style sql \ 
 dynamic result sets 0 \ 
 fenced \ 
 threadsafe \ 
 deterministic \ 
 no dbinfo \ 
 modifies sql data \ 
 program type sub \ 
 external name 'gwenProc!xmlProc1'

// stored procedure: xmlProc1 
 #include <stdio.h> 
 #include <string.h> 
 #include <stdlib.h> 
 #include <sqlda.h> 
 #include <sqlca.h> 
 #include <sqludf.h> 
 #include <sql.h> 
 #include <memory.h> 
 #ifdef __cplusplus 
 extern "C" 
 #endif 
 SQL_API_RC SQL_API_FN xmlProc1 ( sqlint32* inNum, 
  SQLUDF_CLOB* inXML, 
  SQLUDF_CLOB* out1XML, 
  SQLUDF_CLOB* out2XML, 
 SQLUDF_NULLIND *inNum_ind, 
  SQLUDF_NULLIND *inXML_ind, 
  SQLUDF_NULLIND *out1XML_ind, 
  SQLUDF_NULLIND *out2XML_ind, 
 SQLUDF_TRAIL_ARGS) 
 { 
 EXEC SQL INCLUDE SQLCA; 
 EXEC SQL BEGIN DECLARE SECTION; 
  char stmt1[1000], stmt2[1000]; 
  sqlint32 hvNum; 
  SQL TYPE IS XML AS CLOB(2000) hvXML, hvXML1, hvXML2; 
 EXEC SQL END DECLARE SECTION; 

 if ((*inNum_ind < 0) || (*inXML_ind <0)) 
 { 
  strcpy(sqludf_sqlstate, "38100"); 
  strcpy(sqludf_msgtext, "received null input"); 
  return 0; 
 } 
 hvNum = *inNum; 
 hvXML.length = inXML->length; 
 strncpy(hvXML.data, inXML->data, inXML->length); 
 //Insert Data 
 EXEC SQL INSERT INTO xmltable (num, xdata) VALUES (:hvNum, :hvXML); 
 //XQuery with SQL embedded in it 
 sprintf(stmt1, " select xmlquery('for $i in $d/doc let $j := $i/name return $j'
  passing xdata as \"d\") from xmltable  
  where xmlexists ('$d/doc/type = \"car\"' passing xdata as \"d\") "); 
  
 EXEC SQL PREPARE s1 FROM :stmt1; 
 EXEC SQL DECLARE c1 CURSOR FOR s1; 
 EXEC SQL OPEN c1 ; 
 while( sqlca.sqlcode == SQL_RC_OK ) 
 { 
  EXEC SQL FETCH c1 INTO :hvXML1; 
 } 
 EXEC SQL CLOSE c1; 
 out1XML->length = hvXML1.length; 
 strncpy(out1XML->data, hvXML1.data, hvXML1.length); 
 *out1XML_ind = 0; 
 //SQL with XQuery embedded in it 
 sprintf(stmt2, " xquery for $d in db2-fn:sqlquery('select XDATA from XMLTABLE ')/doc 
 where $d/type = \"car\" return ( <CarName> {$d/name }  </CarName> ) "); 
 
 EXEC SQL PREPARE s2 FROM :stmt2; 
 EXEC SQL DECLARE c2 CURSOR FOR s2; 
 EXEC SQL OPEN c2; 
 while( sqlca.sqlcode == SQL_RC_OK ) 
 { 
  EXEC SQL FETCH c2 INTO :hvXML2; 
 } 
 EXEC SQL CLOSE c2; 
 out2XML->length = hvXML2.length; 
 strncpy(out2XML->data, hvXML2.data, hvXML2.length); 
 *out2XML_ind = 0; 
 EXEC SQL COMMIT; 
 exit: 
  return sqlca.sqlcode; 
  }

CALL xmlproc1 (2, XMLPARSE(DOCUMENT '<doc> 
 <type>fruit</type> 
 <name>apple</name> 
 </doc>' PRESERVE WHITESPACE),?,?)

------------------ 
 OUT1XML 
 <name>apple</name> 
 OUT2XML 
 <CarName/>

~/bookstore/app/views/books/new.rhtml 
 ~/bookstore/app/views/books/show.rhtml 
 ~/bookstore/app/views/books/edit.rhtml

qing@ubuntu:~/bookstore$ curl -H "Accept: application/xml" 
			-i -X GET http://localhost:3000/books/100 
			
 HTTP/1.1 200 OK 
 Cache-Control: no-cache 
 Connection: Keep-Alive 
 Date: Thu, 21 Jun 2007 06:05:23 GMT 
 Content-Type: application/xml; charset=utf-8 
 Server: WEBrick/1.3.1 (Ruby/1.8.5/2006-08-25) 
 Content-Length: 601 
 Set-Cookie: _bookstore_session_id=afdffcf5f66096ad5b4e0de34cf78fa9; path=/ 
 <?xml version="1.0" encoding="UTF-8"?> 
 <book> 
  <bookid>123456</bookid> 
  <bookinfo type="xml"><?xml version="1.0" encoding="UTF-8" ?> 
  <book> 
  <authors> 
  <author id="47"> 张 三 </author> 
  <author id="58"> 李 四 </author> 
  </authors> 
  <title> 数据库系统 </title> 
  <price>29</price> 
  <keywords> 
  <keyword>SQL</keyword> 
  <keyword> 关系 </keyword> 
  </keywords> 
  </book> 
  </bookinfo> 
  <id type="integer">100</id> 
 </book>