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!
Read Me
Post a Comment