Updating xml clob in oracle


create or replace function get Clob Document( filename in varchar2, charset in varchar2 default NULL) return CLOB deterministic is file bfile := bfilename('DIR',filename); char Content CLOB := ' '; target File bfile; lang_ctx number := DBMS_LOB.default_lang_ctx; charset_id number := 0; src_offset number := 1 ; dst_offset number := 1 ; warning number; begin if charset is not null then charset_id := NLS_CHARSET_ID(charset); end if; target File := file; DBMS_LOB.fileopen(target File, DBMS_LOB.file_readonly); DBMS_LOB.LOADCLOBFROMFILE(char Content, target File, DBMS_Length(target File), src_offset, dst_offset, charset_id, lang_ctx,warning); DBMS_LOB.fileclose(target File); return char Content; end; / -- create XMLDIR directory -- connect system/manager -- create directory XMLDIR as ''; -- grant read on directory xmldir to public with grant option; -- you can use get CLOBDocument() to generate a CLOB from a file containin -- an XML document.

In situations where this is not the case, full instance validation can be enabled using one of the following approaches: ALTER TABLE XML_PURCHASEORDER add constraint VALID_PURCHASEORDER check (XMLIs Valid(sys_nc_rowinfo$)=1); -- This returns: -- Table altered INSERT INTO XML_PURCHASEORDER values (xmltype(get Document('Invalid Purchase Order.xml'))); INSERT INTO XML_PURCHASEORDER; * -- This returns: -- ERROR at line 1: -- ORA-02290: check constraint (DOC92.

VALID_PURCHASEORDER) violated CREATE TRIGGER VALIDATE_PURCHASEORDER before insert on XML_PURCHASEORDER for each row declare XMLDATA xmltype; begin XMLDATA := :new.sys_nc_rowinfo$; xmltype.schemavalidate(XMLDATA); end; / -- This returns: -- Trigger created.

EMP WHERE extract Value(value(x),'/Purchase Order/User') = EMP. EMPNO = 7876; -- This returns: -- EXTRACTVALUE(VALUE(X),'/PURCHASEORDER/REFERENCE') -- -------------------------------------------------- -- ADAMS-20011127121040988PST set long 20000 SELECT extract(value(X), '/Purchase Order/Line Items/Line Item/Description') FROM XMLTABLE X; -- This returns: -- EXTRACT(VALUE(X),'/PURCHASEORDER/LINEITEMS/LINEITEM/DESCRIPTION') -- ------------------------------------------------------------------ -- UPDATE XMLTABLE t SET value(t) = update XML(value(t), '/Purchase Order/Reference/text()', 'MILLER-200203311200000000PST') WHERE exists Node(value(t), '/Purchase Order[Reference="ADAMS-20011127121040988PST"]') = 1; The W3C XSLT Recommendation defines an XML language for specifying how to transform XML documents from one form to another.

Transformation can include mapping from one XML schema to another or mapping from XML to some other format such as HTML or WML.

With Oracle XML DB you can express hierarchical queries against XML documents in a familiar, standards compliant manner.