Updating multiple table

Example 4-9 Extracting XML Data with EXTRACTVALUE, and Inserting It into a Table This example extracts data from an XML purchase-order document, and inserts it into a relational table using SQL function CREATE OR REPLACE PROCEDURE insert Purchase Order(Purchase Order XMLType) AS reference VARCHAR2(28); BEGIN INSERT INTO purchaseorder_table (reference, requestor, actions, userid, costcenter, shiptoname, address, phone, rejectedby, daterejected, comments, specialinstructions) VALUES (extract Value(Purchase Order, '/Purchase Order/Reference'), extract Value(Purchase Order, '/Purchase Order/Requestor'), extract(Purchase Order, '/Purchase Order/Actions'), extract Value(Purchase Order, '/Purchase Order/User'), extract Value(Purchase Order, '/Purchase Order/Cost Center'), extract Value(Purchase Order, '/Purchase Order/Shipping Instructions/name'), extract Value(Purchase Order, '/Purchase Order/Shipping Instructions/address'), extract Value(Purchase Order, '/Purchase Order/Shipping Instructions/telephone'), extract Value(Purchase Order, '/Purchase Order/Rejection/User'), extract Value(Purchase Order, '/Purchase Order/Rejection/Date'), extract Value(Purchase Order, '/Purchase Order/Rejection/Comments'), extract Value(Purchase Order, '/Purchase Order/Special Instructions')) RETURNING reference INTO reference; INSERT INTO purchaseorder_lineitem (reference, lineno, upc, description, quantity, unitprice) SELECT reference, extract Value(value(li), '/Line Item/@Item Number'), extract Value(value(li), '/Line Item/Part/@Id'), extract Value(value(li), '/Line Item/Description'), extract Value(value(li), '/Line Item/Part/@Quantity'), extract Value(value(li), '/Line Item/Part/@Unit Price') FROM table(XMLSequence(extract(Purchase Order, '/Purchase Order/Line Items/Line Item'))) li; END;/ Procedure created.SELECT reference, userid, shiptoname, specialinstructions FROM purchaseorder_table; REFERENCE USERID SHIPTONAME SPECIALINSTRUCTIONS -------------------------------- -------- ------------------------------------------------ ------------------- SBELL-2002100912333601PDT SBELL Sarah J. SELECT reference, lineno, upc, description, quantity FROM purchaseorder_lineitem; REFERENCE LINENO UPC DESCRIPTION QUANTITY ------------------------- ------ ------------ ---------------------------------- -------- SBELL-2002100912333601PDT 1 715515009058 A Night to Remember 2 SBELL-2002100912333601PDT 2 37429140222 The Unbearable Lightness Of Being 2 SBELL-2002100912333601PDT 3 715515011020 Sisters 4 3 rows selected.The XPath language is a W3C Recommendation for navigating XML documents. It provides a rich set of operations that walk this tree and apply predicates and node-test functions.

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)); Table created.

Each of these functions applies an XPath-expression argument to input XML data and returns a modified to modify database data.

Each of these functions can be used on XML documents that are either schema-based or non-schema-based.

If the proper return type cannot be determined, then Oracle XML DB returns a SELECT extract Value(OBJECT_VALUE, '/Purchase Order/Reference') "REFERENCE" FROM purchaseorder WHERE exists Node(OBJECT_VALUE, '/Purchase Order[Special Instructions="Expedite"]') = 1; REFERENCE ------------------------------------------------------------ AMCEWEN-20021009123336271PDT SKING-20021009123336321PDT AWALSH-20021009123337303PDT JCHEN-20021009123337123PDT AWALSH-20021009123336642PDT SKING-20021009123336622PDT SKING-20021009123336822PDT AWALSH-20021009123336101PDT WSMITH-20021009123336412PDT AWALSH-20021009123337954PDT SKING-20021009123338294PDT WSMITH-20021009123338154PDT TFOX-20021009123337463PDT 13 rows selected.

INSERT INTO purchaseorder VALUES (XMLType(bfilename('XMLDIR', 'SMCCAIN-2002091213000000PDT.xml'), nls_charset_id('AL32UTF8'))); 1 row created.

Search for updating multiple table:

updating multiple table-65

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “updating multiple table”

  1. Our Service Team manually checks every single user profile to ensure that you don’t waste your time with scammers or those only looking for one night stands.

  2. They love having full control of weak men like you so remember to enter into these live rooms and start your online BDSM cam training today we have the very best in BDSM chat from the top Mistresses worldwide They thrive on all types of live fetish play from smoking to foot smothering as well as slapping and spitting You can be watched by Our Mistresses by clicking the cam to cam sex sessions here Online Mistress for real bdsm cam chats " data-medium-file="https://i2com/livebdsmcams.net/wp-content/uploads/2015/09/kinky-cam-bdsm-live-chat.jpeg?