non-random ramble

Ok, so you create a table which has an xml blob.

THen you create a view onto that table which maps elements inside the XML blob to columns in the view, thus “expanding” out the xml to look like a normal table.

To do this you use the extract() method in oracle (see other posts) .

Now you want to filter the view based on nodes which are inside the xml data but oracle wont let you do it directly because the columns arent real columns.

SO

You need to include a unique key in the view – then you can do the select on the original table and JOIN the view to it! bit convoluted but it works! as long as there is a unique key of course – so you might have to design your xml table with this in mind but even if this is arbitrary it will still work.

here is an example :

SELECT tbview.* FROM TABLE_ONE tb, TABLE_ONE_VIEW tbview
WHERE
tbview.UNIQUE_ID=tb.UNIQUE_ID
AND
tb.XML_COLUMN.existsNode(‘/some/expath[with_test_node="TEST_2"]‘)=1;

Sweet!

eg :

WHERE tb.XML_COLUMN.existsNode(‘/some/xpath[with_test="Jeff"]‘)=1

have to use this “existsNode” the xpath can then do the filter – if you want to filter on the value of a node you do the above, otherwise use @ for attributes.

So im playing with oracle and have forgotten everything i know about it.

W3C shool for SQL!

SELECT table_name FROM all_tables WHERE owner = USER;

this gives me all my tables.

cant even remember the syntax for a CREATE table!!

create table test (column1 varchar(20), column2 int);

insert into test values (‘jim001′, 34);

delete from test where column1=’jim001′;

update test set column1=’jim003′, column2=’56′ where column1=’jim001′;

this will go in my wikki when i work out how to work that!:)

woohoo!! toad is so beautiful! i think its had a revamp…

http://www.toadsoft.com/lic_agree.html

an faq