Updating database tables from xml Uae sexy girls chat live

Posted by / 01-Jul-2020 06:18

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.phone, t.rejectedby, t.daterejected, t.comments, t.specialinstructions FROM purchaseorder p, XMLTable('/Purchase Order' PASSING p.

OBJECT_VALUE AS "p" RETURNING CONTENT) AS VARCHAR2(30)) userid, CASE WHEN XMLExists('$p/Purchase Order/Reject/Date' PASSING po.

In real-world examples you will typically pass a value that is available only at runtime; Example 5-28 shows how to do that. Example 5-29 updates multiple text nodes and attribute nodes.

Example 5-30 updates selected nodes within a collection.

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.

OBJECT_VALUE AS "p" RETURNING CONTENT) AS VARCHAR2(12)); REFERENCE USERID STATUS STATUS_DATE -------------------------------- -------- -------- ------------ VJONES-20020916140000000PDT SVOLLMAN Accepted 2002-10-11 SMCCAIN-2002091213000000PDT SKING Rejected 2002-10-12 2 rows selected.

updating database tables from xml-43updating database tables from xml-20updating database tables from xml-50

OBJECT_VALUE COLUMNS reference VARCHAR2(28) PATH 'Reference', lineitem XMLType PATH 'Line Items/Line Item') t, XMLTable('Line Item' PASSING t.lineitem COLUMNS lineno NUMBER(10) PATH '@Item Number', upc VARCHAR2(14) PATH 'Part/@Id', description VARCHAR2(128) PATH 'Description', quantity NUMBER(10) PATH 'Part/@Quantity', unitprice NUMBER(12,2) PATH 'Part/@Unit Price') li WHERE t.reference = 'EABEL-20021009123336251PDT'; SELECT reference, userid, shiptoname, specialinstructions FROM purchaseorder_table; REFERENCE USERID SHIPTONAME SPECIALINSTRUCTIONS -------------------------------- -------- ------------------------------------------------ ------------------- EABEL-20021009123336251PDT EABEL Ellen S.