oracle中xmltype类型数据的操作总结

APPENDCHILDXML

Appends a user-supplied value onto the target XML as the child of the node indicated by an XPath expression

APPENDCHILDXML(XMLType_Instance>,,,)

UPDATE warehouses

SET warehouse_spec = APPENDCHILDXML(warehouse_spec, 'Warehouse/Building', XMLType('Grandco'))

WHERE EXTRACTVALUE(warehouse_spec, '/Warehouse/Building') = 'Rented';

SELECT warehouse_id, warehouse_name,

EXTRACTVALUE(warehouse_spec, '/Warehouse/Building/Owner') \FROM warehouses

WHERE EXISTSNODE(warehouse_spec, '/Warehouse/Building/Owner') = 1;

DELETEXML

Deletes the node or nodes matched by the XPath expression in the target XML DELETEXML(XMLType_Instance>, , )

SELECT warehouse_id, warehouse_spec

FROM warehouses WHERE warehouse_id IN (2,3);

UPDATE warehouses

SET warehouse_spec=DELETEXML(warehouse_spec,'/Warehouse/Building/Owner') WHERE warehouse_id = 2;

SELECT warehouse_id, warehouse_spec FROM warehouses WHERE warehouse_id IN (2,3);

DEPTH

Returns the number of levels in the path specified by the UNDER_PATH condition with the same correlation variable

SELECT PATH(1), DEPTH(2) FROM RESOURCE_VIEW WHERE UNDER_PATH(res, '/sys/schemas/OE', 1)=1 AND UNDER_PATH(res, '/sys/schemas/OE', 2)=1;

EXISTSNODE

Determines whether traversal of an XML document using a specified path results in any nodes EXISTSNODE(XMLType_Instance>, , )

SELECT warehouse_id, warehouse_name

FROM warehouses WHERE EXISTSNODE(warehouse_spec, '/Warehouse/Docks') = 1;

INSERTCHILDXML

Inserts a user-supplied value into the target XML at the node indicated by the XPath expression INSERTCHILDXML(XMLType_Instance>,,, , )

SELECT warehouse_spec FROM warehouses WHERE warehouse_id = 3;

UPDATE warehouses

SET warehouse_spec = INSERTCHILDXML(warehouse_spec,

'/Warehouse/Building', 'Owner', XMLType('LesserCo')) WHERE warehouse_id = 3;

SELECT warehouse_spec FROM warehouses

WHERE warehouse_id = 3;

INSERTXMLBEFORE

Inserts a user-supplied value into the target XML before the node indicated by the XPath expression

INSERTXMLBEFORE(XMLType_Instance>,,,)

SELECT warehouse_name, EXTRACT(warehouse_spec, '/Warehouse/Building/Owner') \FROM warehouses

WHERE warehouse_id = 3;

UPDATE warehouses

SET warehouse_spec = INSERTXMLBEFORE(warehouse_spec, '/Warehouse/Building/Owner[2]',

XMLType('ThirdOwner')) WHERE warehouse_id = 3;

SELECT warehouse_name, EXTRACT(warehouse_spec, '/Warehouse/Building/Owner') \FROM warehouses

WHERE warehouse_id = 3;

UPDATEXML

Takes as arguments an XMLType instance and an XPath-value pair and returns an XMLType instance with the updated value

UPDATEXML(XMLType_Instance>, ,, )

SELECT warehouse_name,

EXTRACT(warehouse_spec, '/Warehouse/Docks') \FROM warehouses

WHERE warehouse_name = 'San Francisco';

UPDATE warehouses

SET warehouse_spec = UPDATEXML(warehouse_spec, '/Warehouse/Docks/text()',4)

WHERE warehouse_name = 'San Francisco';

SELECT warehouse_name,

EXTRACT(warehouse_spec, '/Warehouse/Docks') \FROM warehouses

WHERE warehouse_name = 'San Francisco';