SQL/XML in DB2

select * from rentalhouse

‘DELETE THE ROWS BASED ON THE XML ELEMENT VALUE

DELETE FROM warehouses e WHERE e.warehouse_spec.extract('//Building/text()').getStringVal()

= 'Leased';

JOINING TABLES BASED ON EXTRACTED STRING VALUE FROM XML FILE ELEMENTS

select  w.warehouse_id,r.warehouse_name from warehouses w,rentalhouse r where w.warehouse_spec.extract('//Building/text()').getstringval()='rented'

=============================

select  w.warehouse_id,r.warehouse_name,(w.warehouse_spec.extract('//Building/text()').getstringval()) as building from warehouses w,rentalhouse r where w.warehouse_spec.extract('//Building/text()').getstringval()=r.warehouse_spec.extract('//Building/text()').getstringval()

==================================

select  w.warehouse_id,r.warehouse_name,(w.warehouse_spec.extract('//Building/text()').getstringval()) as building from warehouses w,rentalhouse r where w.warehouse_spec.extract('//Building/text()').getstringval()= r.warehouse_spec.extract('//Building/text()').getstringval()

==============================================

select warehouse_id,existsnode(warehouse_spec,'/Warehouse/phone') from warehouses

==========================================

SELECT extractvalue(e.poDoc, '/PO/PNAME') PNAME

FROM po_xml_tab e

WHERE e.poDoc.existsNode('/PO/SHIPADDR') = 1 AND

extractvalue(e.poDoc,'//PONO') = 300 AND

extractvalue(e.poDoc,'//@CUSTNAME') like '%John%';

====================================

INSERT INTO warehouses VALUES

(       109, XMLType(

'

rented

3333333333

'), 'Tower Records', 1009,

XMLType('

9000

'));

=====================================

INSERT INTO warehouses VALUES

(       109, XMLType(

'

James

Bond

007

905-007-1007

77007

'),'rented',122);

==================================

select extractvalue(w.WAREHOUSE_SPEC,'/Warehouse/whNo') WAREHOUSENO

FROM warehouses w where w.WAREHOUSE_SPEC.existsnode('/Warehouse/phone') = 1

=====================================

UPDATE po_xml_tab

SET poDoc =  UPDATEXML(poDoc,

'/PO/CUSTNAME/text()', 'John');

=================================

UPDATE warehouses set warehouse_spec = updatexml(warehouse_spec,'/Warehouse/phone/text()','123456789');

============================

select * from warehouses

====================================

UPDATE emp_tab e

SET e.emp_col = UPDATEXML(e.emp_col,

'/EMPLOYEES/EMP[EMPNAME="Joe"]/SALARY/text()',100000,

'//EMP[EMPNAME="Jack"]/EMPNAME/text()','Jackson',

'//EMP[EMPNO=217]',XMLTYPE.CREATEXML(

'217Jane'))

WHERE EXISTSNODE(e.emp_col, '//EMP') = 1;

======================================

INSERT INTO warehouses VALUES

(       105, XMLType(

' 

James

Bond

007

905-007-1007

77007

'), 'Tower Records', 1006,

XMLType('

5000

'));

======================================

SELECT X.*

FROM warehouses,

XMLTABLE (XMLNAMESPACES('http://www.ibm.com/xmltable' as "ibm"),'$d/ibm:dept/ibm:employee' passing warehouse_spec as "d"

COLUMNS

empID   INTEGER    PATH '@id',

firstname   VARCHAR(20)    PATH 'ibm:name/ibm:first',

lastname    VARCHAR(25)    PATH 'ibm:name/ibm:last') AS X