XML Type In Oracle

Table Creation

Create Table CallInfo (msg SYS.XMLTYPE,msisdn Varcjar2(10));

Insert values to Table

DECLARE
v_xml SYS.XMLTYPE;
v_doc CLOB;
BEGIN

CLOB v_doc := '' Chr(10) ' MY_TABLE'; v_xml := sys.xmltype.createXML(v_doc);

INSERT INTO tab1 (col1) VALUES (v_xml);
SELECT SYS_XMLGen(table_name) INTO v_xml FROM
user_tables WHERE rownum = 1;

INSERT INTO tab1 (col1) VALUES (v_xml);
COMMIT;
END;

Extract Value From Table

SET LONG 1000
SELECT a.col1.getStringVal() FROM tab1 a;

Extract Values from Selected Tags

SELECT a.col1.extract('//TABLE_NAME/text()').getStringVal() AS "Table Name"
FROM tab1 aWHERE a.col1.existsNode('/TABLE_NAME') = 1;