catch values from string that is XMLTYPE

[Former Member]

hello i have a table that contains an XMLTYPE field...

CREATE TABLE Z_XML_TABLE OF XMLType;

and then i insert my XML file with this:

INSERT INTO Z_XML_TABLE VALUES (XMLType(bfilename('XML_DIR', 'purchaseOrder.xml'),

nls_charset_id('AL32UTF8')));

and now i want to certain values from the XML table and put in other table. i want to do this with a stored procedure...could someone help me??

regards

[Former Member]

You can transfer certain values from one table to other table using

INSERT INTO OTHER_TABLE SELECT * FROM Z_XML_TABLE WHERE (filter for certain values)

or you can write stored procedure as

Create PROC xml_CopyDataOver

as

BEGIN

INSERT INTO other_table (field1, field2)

SELECT bfilename, field2

From Z_XML_TABLE

WHERE your condition

END