Originally posted by julian
So, has anybody used/tried the XMLtype datatype?
Julian, just downloaded 9i Rel 2 and installed it on my old PII laptop and it's working fine. I just did a run through of my XMLType example and it was fine:

SQL> create user tim identified by tim default tablespace users;

User created.

SQL> grant connect, resource to tim;

Grant succeeded.

SQL> grant execute on sys.xmltype to tim;

Grant succeeded.

SQL> conn tim/tim@tsh1
Connected.
SQL> CREATE TABLE tab1
2 (col1 SYS.XMLTYPE);

Table created.

SQL> DECLARE
2 v_xml SYS.XMLTYPE;
3 v_doc CLOB;
4 BEGIN
5 -- XMLTYPE created from a CLOB
6 v_doc := '' || Chr(10) || ' <TABLE_NAME>MY_TABLE</TABLE_NAME>';
7 v_xml := sys.xmltype.createXML(v_doc);
8
9 INSERT INTO tab1 (col1) VALUES (v_xml);
10
11 -- XMLTYPE created from a query
12 SELECT SYS_XMLGen(table_name)
13 INTO v_xml
14 FROM user_tables
15 WHERE rownum = 1;
16
17 INSERT INTO tab1 (col1) VALUES (v_xml);
18
19 COMMIT;
20 END;
21 /

PL/SQL procedure successfully completed.

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

A.COL1.GETSTRINGVAL()
----------------------------------------------------------------------------------------------------
<?xml version="1.0"?>
<TABLE_NAME>MY_TABLE</TABLE_NAME>

<?xml version="1.0"?>
<TABLE_NAME>TAB1</TABLE_NAME>


2 rows selected.

SQL> SELECT a.col1.extract('//TABLE_NAME/text()').getStringVal() AS "Table Name"
2 FROM tab1 a
3 WHERE a.col1.existsNode('/TABLE_NAME') = 1;

Table Name
----------------------------------------------------------------------------------------------------
MY_TABLE
TAB1

2 rows selected.

SQL>

I had to manually alter the output a little to prevent the XML tags from disappearing when viewed on the browser. Sorry if I've made any typos.

Cheers

[Edited by TimHall on 05-20-2002 at 11:42 AM]