skip to content »

Updating database tables from xml

In Example 5-10, these empty sequences are part of the overall result set of the SQL query, which contains 132 rows, one for each of the rows in table ' PASSING OBJECT_VALUE RETURNING CONTENT) FROM purchaseorder; XMLQUERY('FOR$IIN/PURCHASEORDERWHERE$I/COSTCENTEREQ"A10"AND$I/USEREQ"SMCCAIN"RET -------------------------------------------------------------------------------- 'Regular', contact VARCHAR2(20) PATH 'Name') xtab; POREF PRIORITY CONTACT -------------------- -------- -------------------- SKING-20021009123336 Fastest Steven A. Both implementations are part of Oracle XML Developer's Kit (XDK).

updating database tables from xml-14updating database tables from xml-10updating database tables from xml-47updating database tables from xml-76

The WHERE clause specifies which record(s) that should be updated. When you sign in to comment, IBM will provide your email, first name and last name to DISQUS.That information, along with your comments, will be governed by DISQUS’ privacy policy. OBJECT_VALUE COLUMNS reference VARCHAR2(28) PATH 'Reference', requestor VARCHAR2(48) PATH 'Requestor', actions XMLType PATH 'Actions', userid VARCHAR2(32) PATH 'User', costcenter VARCHAR2(3) PATH 'Cost Center', shiptoname VARCHAR2(48) PATH 'Shipping Instructions/name', address VARCHAR2(512) PATH 'Shipping Instructions/address', phone VARCHAR2(32) PATH 'Shipping Instructions/telephone', rejectedby VARCHAR2(32) PATH 'Reject/User', daterejected DATE PATH 'Reject/Date', comments VARCHAR2(2048) PATH 'Reject/Comments', specialinstructions VARCHAR2(2048) PATH 'Special Instructions') t WHERE t.reference = 'EABEL-20021009123336251PDT'; INSERT INTO purchaseorder_lineitem (reference, lineno, upc, description, quantity, unitprice) SELECT t.reference, li.lineno, li.upc, li.description, li.quantity, li.unitprice FROM purchaseorder p, XMLTable('/Purchase Order' PASSING p. CREATE TABLE purchaseorder_table (reference VARCHAR2(28) PRIMARY KEY, requestor VARCHAR2(48), actions XMLType, userid VARCHAR2(32), costcenter VARCHAR2(3), shiptoname VARCHAR2(48), address VARCHAR2(512), phone VARCHAR2(32), rejectedby VARCHAR2(32), daterejected DATE, comments VARCHAR2(2048), specialinstructions VARCHAR2(2048)); CREATE TABLE purchaseorder_lineitem (reference, FOREIGN KEY ("REFERENCE") REFERENCES "PURCHASEORDER_TABLE" ("REFERENCE") ON DELETE CASCADE, lineno NUMBER(10), PRIMARY KEY ("REFERENCE", "LINENO"), upc VARCHAR2(14), description VARCHAR2(128), quantity NUMBER(10), unitprice NUMBER(12,2)); INSERT INTO purchaseorder_table (reference, requestor, actions, userid, costcenter, shiptoname, address, phone, rejectedby, daterejected, comments, specialinstructions) SELECT t.reference, t.requestor, t.actions, t.userid, t.costcenter, t.shiptoname, t.address,, t.rejectedby, t.daterejected, t.comments, t.specialinstructions FROM purchaseorder p, XMLTable('/Purchase Order' PASSING p.By commenting, you are accepting the IBM commenting guidelines and the DISQUS terms of service.

The mapping-schema attribute identifies the mapping schema to be used by the updategram.

'; BEGIN res := DBMS_XDB_REPOS.create Resource('/public/emps.xml', empsxmlstring); res := DBMS_XDB_REPOS.create Resource('/public/empsns.xml', empsxmlnsstring); res := DBMS_XDB_REPOS.create Resource('/public/depts.xml', deptsxmlstring); END; / -manipulation language. OBJECT_VALUE AS "p" RETURNING CONTENT) AS VARCHAR2(30)); REFERENCE COUNT(*) -------------------------------- ---------- TFOX-20021009123337784PDT 3 XQuery API for Java (XQJ), also known as JSR-225, provides an industry-standard way for Java programs to access XML data using XQuery.

Its expressions and their results are not necessarily XML data. It lets you evaluate XQuery expressions against XML data sources and process the results as XML data.

XML.extract('//Requestor'); SELECT XMLSerialize(CONTENT XMLQuery('//text()' PASSING x Node RETURNING CONTENT)) INTO v Text FROM DUAL; DBMS_OUTPUT.put_line('The Requestor for Reference ' || v Reference || ' is '|| v Text); END LOOP; v Reference := 'PTUCKER-20021009123335430PDT'; FOR c IN get Purchase Order(v Reference) LOOP x Node := c.

DECLARE x Node XMLType; v Text VARCHAR2(256); v Reference VARCHAR2(32); CURSOR get Purchase Order(reference IN VARCHAR2) IS SELECT OBJECT_VALUE XML FROM purchaseorder WHERE XMLExists('$p/Purchase Order[Reference=$r]' PASSING OBJECT_VALUE AS "p", reference AS "r"); BEGIN v Reference := 'EABEL-20021009123335791PDT'; FOR c IN get Purchase Order(v Reference) LOOP x Node := c.

block provides only the Contact ID column; therefore, the updategram uses only the value to search for the record.