CREATE TABLE pet (name VARCHAR2(22), species VARCHAR2(22), weight_kg NUMBER);
I want to query up results like
My first thought was to use an XDB's XMLType View of my table. Okay... that looks... sort of intimidating. But I want to be able to query from it with the same old SQL I'm used to; I want to grab the XML for all rows<pet>
WHERE species = 'cat' OR (weight_kg > 100 AND name LIKE 'J%')
Now it looks really intimidating. I don't want to go back to school for a Master's of Science in XPath right now, and neither does my coworker.
Then I thought, "I know! I'll just use XMLForest, like this!"
SELECT XMLForest(name, species, weight_kg) FROM pet
On my platform (Windows 2003 / SQL*Plus 10.1.0.2.0 / Oracle 10.1.0.4.0 ), this produces
"Oracle SQL*Plus has encountered a problem and needs to close."
Attempted workarounds - like
EXEC SELECT XMLForest(name, species, weight_kg) INTO :petxml FROM pet
give me results like
"PLS-00801: Internal error [*** ASSERT at file pdw4.c, line 782; Cannot coerce between type 43 and type 30; _anon__2C9F5D70__AB[1, 7]]"
Okay, I give up - this XDB stuff is not mature enough for me today.
My handmade solution is fairly simple to use.
SELECT xml FROM pet_xmlvw WHERE species = 'cat' OR (weight_kg > 100 AND name LIKE 'J%')
Setting up views like pet_xmlvw is a headache and a half, but my ugly code will do it for you. (Can you hear PL/SQL crying? Can you hear it crying, "Somebody write Cheetah for me!"?)
Now runningCREATE OR REPLACE FUNCTION sf_columns_xml(table_name IN VARCHAR2)
result CLOB := '';
FOR c IN ( SELECT utc.column_name
FROM user_tab_columns utc
WHERE utc.table_name = UPPER(sf_columns_xml.table_name) )
column_name := lower(c.column_name);
if length(result) > 0
result := result || chr(10);
result := result ||
' <' ||
column_name || '> '' || ' || column_name || ' || ''</' ||
column_name || '>';
CREATE OR REPLACE FUNCTION sf_table_xml(table_name IN VARCHAR2)
'CREATE OR REPLACE VIEW ' || lower(substr(table_name,1,24)) || '_xmlvw AS
<' || lower(table_name) || '>
' || to_char(sf_columns_xml(table_name)) ||
</' || lower(table_name) || '>'' xml,
' || table_name || '.*
FROM ' || table_name;
EXEC execute immediate TO_CHAR(sf_table_xml('PET'))will generate the view pet_xmlvw, whose column xml contains what I want.
To generate such views for all the tables in my schema, I run
(Hey, I didn't use q'| |')! Well, you may not all be on Oracle 10g.)spool buildXMLViews.sql
select 'exec execute immediate to_char(sf_table_xml(''' || table_name || '''));'
where dropped = 'NO';
select 'exec execute immediate to_char(sf_table_xml(''' || view_name || '''));'
Sometimes, it's a sausage factory in here. Don't look at how it gets done, just pass the ketchup.