non-random ramble

http://forums.oracle.com/forums/forum.jspa?forumID=34

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.

now bloody wordpress seems to be playing up – i dont seem to see my new post!

welll safari 1.3.2 (v312.5) seems to be having some serious caching problems!

In order to see the new post i had to clear the cache AND restart safari!!

then i could see them!!!

AAAAAAAAAAAARRRRRRRRRGGGGGGGGGHHHHHHHH!!!!!!!!!!

Now i cant see the changes at all!!!

hmmm – it seems to be showing me the previous edit!!

well ive been having a few teething problems with getting my site working properly so am going to post the fixes.

All of the problems were with Safari, not dreamhost which works fine.

Basically i had to reset safari completely after downloading a new version which i guess makes quite some sense as maybe its not quite compatible with th ecookies etc.

i was still seeing a weird behaviourwith the wikki main page though – it was showing me a really old cached version of th eindex page and refused to update it :

its very wierd though – safari must do some odd caching which it doesnt reset when getting an index page.

so:

I go to the URL http://www.planet-ix.com/mindweb/

and its supposed to loade the file called index.php right ?

this it does but it seems to be loading an old cached version which means it doesnt redirect properly to the main page which has the url

http://www.planet-ix.com/mindweb/index.php?title=Main_Page

right so i tried to edit the index.php and put a different string in there to proove this.

I still see the old page.

So i thought i will replace the file completely and created a new empty file called index.html which should override the index .php.

sure enough after a couple of clicks and refreshes (it didnt recognise it straight away), i get it to return a blank page at index.html

then by some miracle when i rename the index.html, and refresh the page, everything works !!

who knows but maybe it would be worth a tip somewhere on the faq incase anyone else comes accros it.

now i am happy dreamhost customer all my web work:)