WebSphere Federation Server Version 9.1을 사용하여 XML 데이터 통합하기 (한글)

SELECT b.xmlinfo, i.quantity, w.city  
FROM bookstore b, inventory i, warehouse w  
WHERE b.isbn = i.isbn 
  AND i.warehouse_id = w.warehouse_id 
AND b.isbn LIKE '%69'

<book isbn="0201633469" year="1993" category="Computers">
  <title>TCP/IP Illustrated</title>
  <author><last>Stevens</last><first>W. Richard</first></author>
  <publisher>Addison-Wesley Professional</publisher>
  <price>65.00</price>
  </book>,
3,
'San Francisco'

INSERT INTO author
  SELECT xb.*
  FROM bookstore b, XMLTABLE(
    '$doc/book' PASSING b.xmlinfo AS "doc" COLUMNS
      "ISBN" CHAR(10) PATH '@isbn',
      "LASTNAME" VARCHAR(30) PATH 'author/last',
      "FIRSTNAME" VARCHAR(30) PATH 'author/first'
    ) AS xb;

SELECT b.isbn, i.warehouse_id, i.quantity
FROM bookstore b, inventory i
WHERE i.isbn = b.isbn 
  AND i.quantity > 0
  AND XMLEXISTS('$doc/book[price < 65.50]' 
        PASSING BY REF b.xmlinfo AS "doc")

SELECT DISTINCT count(*) as NUMBER_OF_BOOKS, a.firstname, a.lastname
FROM author a, bookstore b
WHERE XMLSERIALIZE(CONTENT XMLQUERY('$doc/book/author/first/text()'
    PASSING BY REF b.xmlinfo AS "doc"
    RETURNING SEQUENCE) AS CHAR(50)) = a.firstname
  AND XMLSERIALIZE(CONTENT XMLQUERY('$doc/book/author/last/text()'
    PASSING BY REF b.xmlinfo AS "doc"
    RETURNING SEQUENCE) AS CHAR(50)) = a.lastname
  AND XMLSERIALIZE(CONTENT XMLQUERY('$doc/book/title/text()'
    PASSING BY REF b.xmlinfo AS "doc"
    RETURNING SEQUENCE) AS CHAR(50)) like '%TCP/IP%'
GROUP BY a.firstname, a.lastname, b.isbn

XQUERY 
  let $b := db2-fn:sqlquery("
    SELECT xmlinfo
    FROM bookstore
    WHERE isbn='0201633469'")
  let $w := db2-fn:sqlquery("
    SELECT XMLELEMENT(NAME warehouse, XMLFOREST(
      w.warehouse_id, w.street, w.city, w.state, i.quantity))
    FROM warehouse w, inventory i
    WHERE w.warehouse_id = i.warehouse_id
    AND i.isbn = '0201633469'")  
  return 
    <report>   
     {$b}
     <stock>{$w}</stock>
    </report>

<report>
  <book isbn="0201633469" year="1993" category="Computers">
    <title>TCP/IP Illustrated</title>
    <author><last>Stevens</last><first>W. Richard</first></author>
    <publisher>Addison-Wesley Professional</publisher>
    <price>65.00</price>
  </book>
  <stock>
    <WAREHOUSE>
      <WAREHOUSE_ID>1</WAREHOUSE_ID>
      <STREET>1234 Market Street</STREET>
      <CITY>San Francisco</CITY>
      <STATE>CA</STATE>
      <QUANTITY>3</QUANTITY>
    </WAREHOUSE>
    <WAREHOUSE>
      <WAREHOUSE_ID>2</WAREHOUSE_ID>
      <STREET>4012 1st Street</STREET>
      <CITY>San Jose</CITY>
      <STATE>CA</STATE>
      <QUANTITY>1</QUANTITY>
    </WAREHOUSE>
  </stock>
</report>

XQUERY 
  for $e in db2-fn:sqlquery("
    SELECT b.xmlinfo 
    FROM bookstore b, inventory i
    WHERE b.isbn = i.isbn 
     AND i.warehouse_id = 1")/book
  where $e/price > 65.00
  return $e/title

<title>Advanced Programming in the UNIX environment</title>

CREATE TABLE bookstore(isbn CHAR(10), xmlinfo XMLType);

CREATE VIEW bookstore_blob (isbn, xmlinfo) AS
SELECT b.isbn, b.xmlinfo.getBlobVal(nls_charset_id('utf8'))
FROM bookstore b;

CREATE NICKNAME bookstore_blob FOR server_name.myuser.bookstore_blob;

CREATE VIEW vbookstore (isbn, xmlinfo) AS
SELECT isbn, XMLPARSE(DOCUMENT xmlinfo PRESERVE WHITESPACE)
FROM bookstore_blob;

CREATE VIEW bookstore_blob (isbn, xmlinfo) AS
SELECT extract(xmlinfo, '//book').getBlobVal(nls_charset_id('utf8'))
FROM bookstore;

CREATE VIEW bookstore_blob (isbn, xmlinfo) AS
SELECT '<root>' || 
       extract(xmlinfo,//book').getBlobVal(nls_charset_id('utf8'))|| 
       '</root>' 
FROM bookstore;

CREATE VIEW vbookstore (isbn, xmlinfo) AS
SELECT isbn, XMLVALIDATE(XMLPARSE(DOCUMENT xmlinfo WHITESPACE)
                          ACCORDING TO XMLSCHEMA ID bookschema)
FROM bookstore_blob;

REGISTER XMLSCHEMA schema_URI FROM content_URI AS bookschema COMPLETE;

CREATE VIEW bookstore_blob (isbn, xmlinfo) AS
SELECT isbn, 
extract(xmlinfo,'/').getBlobVal(nls_charset_id('utf8'))
FROM bookstore; 

CREATE VIEW clob_books (isbn, xmlinfo) AS
SELECT isbn, extract(xmlinfo, '/').getClobVal() 
FROM bookstore;

CREATE VIEW bookstore_blob (isbn, xmlinfo) AS
SELECT b.isbn, b.xmlinfo.getBlobVal(nls_charset_id('utf8'))
FROM bookstore2 b;

CREATE VIEW review_blob (review_id, xmlinfo) AS
SELECT review_id, CAST(xmlinfo AS VARBINARY(MAX))
FROM review;

CREATE VIEW customer_info_blob (customer_id, xmlinfo) AS
SELECT customer_id, XMLSERIALIZE(xmlinfo AS BLOB INCLUDING XMLDECLARATION)
FROM customer_info;

CREATE NICKNAME bookstore_blob FOR "oracle10g2".myuser.bookstore_blob;
CREATE NICKNAME review_blob FOR "mssql".myuser.review_vlob;
CREATE NICKNAME customer_info_blob 
  FOR "db2v91".myuser.customer_info_blob; 
CREATE NICKNAME inventory FOR "db2v91".myuser.inventory;
CREATE NICKNAME warehouse FOR "db2v91".myuser.warehouse;

CREATE NICKNAME order (
  order_id CHAR(10) OPTIONS (XPATH '@id'),
  order_date DATE OPTIONS (XPATH '@date'),
  customer_id CHAR(10) OPTIONS (XPATH '@cid'))  
FOR SERVER "xml_server"
OPTIONS (
  FILE_PATH '/my/path/orders.xml', 
  XPATH '/orders/order');

CREATE NICKNAME order_item (
  order_id VARCHAR(10) OPTIONS (XPATH '../@id'),
  isbn CHAR(10) OPTIONS (XPATH '@isbn'))
FOR SERVER "xml_server"
OPTIONS (
  FILE_PATH '/my/path/orders.xml', 
  XPATH '/orders/order/item');

CREATE VIEW vbookstore2 (isbn, xmlinfo) AS
SELECT isbn, XMLPARSE(DOCUMENT xmlinfo PRESERVE WHITESPACE)
FROM bookstore_blob;

CREATE VIEW vreview (review_id, xmlinfo) AS
SELECT review_id, XMLPARSE(DOCUMENT xmlinfo PRESERVE WHITESPACE)
FROM review_blob;

CREATE VIEW vcustomer_info (customer_id, xmlinfo) AS
SELECT customer_id, XMLPARSE(DOCUMENT xmlinfo PRESERVE WHITESPACE)
FROM customer_info_blob;

CREATE VIEW big_bookstore (isbn, xmlinfo) AS
  SELECT isbn, xmlinfo FROM bookstore1
  UNION ALL
    SELECT isbn, xmlinfo FROM vbookstore2; 

SELECT XMLELEMENT(NAME "book",
         XMLFOREST(
           b."title" AS "title", 
           r."review" AS "review"))
FROM big_bookstore vb, vreview vr,
  XMLTABLE('$doc/book' PASSING vb.xmlinfo AS "doc"
    COLUMNS "isbn" CHAR(10) PATH '@isbn',
            "title" VARCHAR(50) PATH 'title') as b,
  XMLTABLE('$doc/reviews/review' PASSING vr.xmlinfo AS "doc"
    COLUMNS "isbn" CHAR(10) PATH '@isbn',
            "review" VARCHAR(70) PATH '.') as r
WHERE b."isbn" = r."isbn";

<book>
  <title>Advanced Programming in the UNIX environment</title>
  <review>A clear and detailed discussion of UNIX programming.</review>
</book>
<book>
  <title>TCP/IP Illustrated</title>
  <review>One of the best books on TCP/IP.</review>
</book>

<list>
  <state name="CA">
    <city name="San Francisco">
      <book>TCP/IP Illustrated</book>
    </city>
    <city name="San Jose">
      <book>Advanced Programming in the UNIX environment</book>
      <book>TCP/IP Illustrated</book>
      <book>TCP/IP Illustrated</book>
    </city>
  </state>
  <state name="NY">
    <city name="New York">
      <book>TCP/IP Illustrated</book>
    </city>
  </state>
</list>

VALUES (XMLELEMENT(NAME "list", (
  SELECT XMLAGG (s.state)
  FROM ( SELECT XMLELEMENT(NAME "state", 
           XMLATTRIBUTES(c.state AS "name"),
           XMLAGG(c.city)) AS state
         FROM ( SELECT ci."state" AS state, 
                  XMLELEMENT(NAME "city", 
                  XMLATTRIBUTES(ci."city" AS "name"),
                  XMLAGG(XMLELEMENT(NAME "book", b."title"))) AS city
                FROM order o, order_item oi, 
                     big_bookstore vb, vcustomer_info vci,
	          XMLTABLE('$p/customer' PASSING vci.xmlinfo AS "p"
		    COLUMNS "id" VARCHAR(10) PATH '@id',
			    "city" VARCHAR(20) PATH 'address/city',
			    "state" VARCHAR(10) PATH 'address/state') AS ci,
	          XMLTABLE('$p/book' PASSING vb.xmlinfo AS "p"
	            COLUMNS "isbn" VARCHAR(10) PATH '@isbn',
	                    "year" VARCHAR(4) PATH '@year',
	                    "title" VARCHAR(100) PATH 'title') AS b
                     WHERE o.order_id = oi.order_id 
                       AND oi.isbn = b."isbn"
                       AND o.customer_id = ci."id"
                       AND o.order_date <= '10/25/2004'
                     GROUP BY ci."city", ci."state") AS c 
         GROUP BY c.state) AS s
  )));

 
XQUERY 
  let $a := fn:avg(
    db2-fn:xmlcolumn("BIG_BOOKSTORE.XMLINFO")
    /book[@category = "Computers"]/price/text())
  for $book in db2-fn:xmlcolumn('BIG_BOOKSTORE.XMLINFO')/book
  for $review in db2-fn:xmlcolumn('VREVIEW.XMLINFO')/reviews/review 
where 
  $book/@isbn = $review/@isbn
  and $book/price/text() <= $a 
  and fn:contains($review/text(), "best")
return 
  <book>{$book/title/text()}</book>

<book>TCP/IP Illustrated</book>

XQUERY
  for $total_order in db2-fn:sqlquery('
    SELECT 
      XMLELEMENT(NAME "total_order",
        XMLFOREST(
          i.isbn AS "isbn",
          sum(i.quantity) AS "total"
        )
      )
    FROM warehouse w, inventory i
    WHERE w.warehouse_id = i.warehouse_id
    GROUP BY i.isbn
  ')
  let $warehouses := db2-fn:sqlquery('
    SELECT
        XMLAGG(
          XMLELEMENT(NAME "warehouse",        
            XMLFOREST(
              i.isbn AS "isbn",
              i.quantity AS "quantity",
              w.city AS "city",
              w.warehouse_id AS "id"
            ) 
          )
        )
    FROM warehouse w, inventory i      
    WHERE w.warehouse_id = i.warehouse_id
  ')[isbn=$total_order/isbn]
  return
    <book isbn="{$total_order/isbn}">
      <stock total="{$total_order/total}">
        {
          for $wh in $warehouses
          return 
            <warehouse id="{$wh/id}">
              <city>{$wh/city/text()}</city>
              <quantity>{$wh/quantity/text()}</quantity>
            </warehouse>
        }
      </stock>
    </book>   

<book isbn="0201563177">
  <stock total="7">
    <warehouse id="1">
      <city>San Francisco</city>
      <quantity>2</quantity>
    </warehouse>
    <warehouse id="2">
      <city>San Jose</city>
      <quantity>5</quantity>
    </warehouse>
  </stock>
</book>  
<book isbn="0201633469">
  <stock total="4">
    <warehouse id="1">
      <city>San Francisco</city>
      <quantity>3</quantity>
    </warehouse>
    <warehouse id="2">
      <city>San Jose</city>
      <quantity>1</quantity>
    </warehouse>
  </stock>
</book>

('0201633469', 
<book isbn="0201633469" year="1993" category="Computers">
  <title>TCP/IP Illustrated</title>
  <author>
    <last>Stevens</last>
    <first>W. Richard</first>
  </author>
  <publisher>Addison-Wesley Professional</publisher>
  <price>65.00</price>
</book>)
,
('0201563177', 
<book isbn="0201563177" year="1992" category="Computers">
  <title>Advanced Programming in the UNIX environment</title>
  <author>
    <last>Stevens</last>
    <first>W. Richard</first>
  </author>
  <publisher>Addison-Wesley Professional</publisher>
  <price>65.95</price>
</book>)

SELECT * FROM inventory;

INVENTORY_ID WAREHOUSE_ID ISBN       QUANTITY
------------ ------------ ---------- -----------
        1001            1 0201633469           3
        1002            1 0201563177           2
        1003            2 0201633469           1
        1004            2 0201563177           5

4 record(s) selected.

SELECT * FROM warehouse;

WAREHOUSE_ID STREET             CITY          STATE
------------ ------------------ ------------- -----------------
           2 4012 1st Street    San Jose      CA
           1 1234 Market Street San Francisco CA

2 record(s) selected.

CREATE TABLE bookstore1(isbn CHAR(10), xmlinfo XML);

CREATE TABLE bookstore2(isbn CHAR(10), xmlinfo XMLTYPE);

CREATE TABLE review(review_id INT, xmlinfo XML);

CREATE TABLE customer_info(customer_id INT, xmlinfo XML);

CREATE TABLE warehouse(warehouse_id INT, street CHAR(50), city CHAR(30), state CHAR(30));
CREATE TABLE inventory(inventory_id INT, warehouse_id INT, isbn CHAR(10), quantity INT);

CREATE WRAPPER net8;
CREATE WRAPPER drda;
CREATE WRAPPER mssqlodbc3;
CREATE WRAPPER xml_wrapper LIBRARY 'libdb2lsxml.a';

CREATE SERVER "oracle10g2" TYPE ORACLE VERSION 10.2
  WRAPPER net8 OPTIONS (NODE 'MYORA10');
 
CREATE SERVER "db2v91" TYPE db2/cs VERSION 9 
  WRAPPER drda AUTHORIZATION "myuser" PASSWORD "secret"
  OPTIONS (NODE 'MYDB2', DBNAME 'MYDB');

CREATE SERVER "mssql" TYPE MSSQLSERVER VERSION 2005 
  WRAPPER mssqlodbc3 OPTIONS (NODE 'MYMSSQL', DBNAME 'MYDB');

CREATE SERVER "xml_server" WRAPPER xml_wrapper;
			

CREATE USER MAPPING FOR USER SERVER "oracle10g2"
  OPTIONS (REMOTE_AUTHID 'myuser', REMOTE_PASSWORD 'secret'); 

CREATE USER MAPPING FOR USER SERVER "db2v91"
  OPTIONS (REMOTE_AUTHID 'myuser', REMOTE_PASSWORD 'secret');

CREATE USER MAPPING FOR USER SERVER "mssql"
  OPTIONS (REMOTE_AUTHID 'myuser', REMOTE_PASSWORD 'secret');

% DB2 Federated Server
INSERT INTO bookstore1 VALUES('0201633469', 
'<book isbn="0201633469" year="1993" category="Computers">
  <title>TCP/IP Illustrated</title>
  <author>
    <last>Stevens</last>
    <first>W. Richard</first>
  </author>
  <publisher>Addison-Wesley Professional</publisher>
  <price>65.00</price>
</book>');

% Oracle 10g Release 2
INSERT INTO bookstore2 VALUES('0201563177', XMLTYPE(
'<book isbn="0201563177" year="1992" category="Computers">
  <title>Advanced Programming in the UNIX environment</title>
  <author>
    <last>Stevens</last>
    <first>W. Richard</first>
  </author>
  <publisher>Addison-Wesley Professional</publisher>
  <price>65.95</price>
</book>'));

INSERT INTO review VALUES
  (1, 
  '<reviews>
    <review id="10001" isbn="155860622X">A very good discussion of 
semi-structured database systems and XML.</review>
    <review id="10002" isbn="0201563177">A clear and detailed discussion 
of UNIX programming.</review>
    <review id="10003" isbn="0201633469">One of the best books on 
TCP/IP.</review>
  </reviews>');

INSERT INTO customer_info VALUES(1, XMLPARSE(DOCUMENT
'<customer id="1">
  <name><last>Smith</last><first>Joe</first></name>
  <address>
    <street>6127 Crown Bld</street>
    <city>San Jose</city>
    <state>CA</state>
  </address>
</customer>' PRESERVE WHITESPACE));

 INSERT INTO customer_info VALUES(2, XMLPARSE(DOCUMENT
'<customer id="2">
  <name><last>Miller</last><first>Bob</first></name>
  <address>
    <street>544 Capitol Expw</street>
    <city>San Jose</city><state>CA</state>
  </address>
</customer>' PRESERVE WHITESPACE));

INSERT INTO customer_info VALUES(3, XMLPARSE(DOCUMENT
'<customer id="3">
  <name><last>Meyer</last><first>Ann</first></name>
  <address>
    <street>8400 Main St</street>
    <city>San Francisco</city><state>CA</state>
  </address>
</customer>' PRESERVE WHITESPACE));

INSERT INTO customer_info VALUES(4, XMLPARSE(DOCUMENT
'<customer id="3">
  <name><last>Apple</last><first>Sue</first></name>
  <address>
    <street>2200 Diamond Blvd</street>
    <city>New York</city><state>NY</state>
  </address>
</customer>' PRESERVE WHITESPACE));

INSERT INTO warehouse VALUES (1, '1234 Market Street', 'San Francisco', 'CA');
INSERT INTO warehouse VALUES (2, '4012 1st Street', 'San Jose', 'CA');
INSERT INTO inventory VALUES (1001, 1, '0201633469', 3);
INSERT INTO inventory VALUES (1002, 1, '0201563177', 2);
INSERT INTO inventory VALUES (1003, 2, '0201633469', 1);
INSERT INTO inventory VALUES (1004, 2, '0201563177', 5);

<?xml version="1.0" encoding="UTF-8"?>
<orders>
  <order id="101" cid="1" date="2004-10-24">
    <item isbn="0201633469"/>    
    <item isbn="0201633469"/>    
  </order>
  <order id="102" cid="3" date="2004-10-25">
    <item isbn="0201633469"/>    
  </order>
  <order id="103" cid="2" date="2004-10-26">
    <item isbn="0201633469"/>    
    <item isbn="0201563177"/>    
  </order>
  <order id="104" cid="1" date="2004-10-27">
    <item isbn="0201563177"/>    
  </order>
  <order id="105" cid="3" date="2004-10-28">
    <item isbn="0201563177"/>    
    <item isbn="0201563177"/>    
  </order>
</orders>