querying XML without fighting with XDB

My coworker and I want to query some nice, simple XML from my Oracle table. For example, from the table
CREATE TABLE pet (name VARCHAR2(22), species VARCHAR2(22), weight_kg NUMBER);
I want to query up results like

  <pet>
    <name>Martin Luther</name>
    <species>cat</species>
    <weight_kg>6.2</weight_kg>
  </pet>
  <pet>
    <name>Jordache</name>
    <species>horse</species>
    <weight_kg>450</weight_kg>
  </pet>

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
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!"?)

CREATE OR REPLACE FUNCTION sf_columns_xml(table_name IN VARCHAR2)
RETURN CLOB
IS
  result CLOB := '';
  column_name VARCHAR2(30);
BEGIN
  FOR c IN ( SELECT utc.column_name
             FROM   user_tab_columns utc
             WHERE  utc.table_name = UPPER(sf_columns_xml.table_name) )
  LOOP
    column_name := lower(c.column_name);
    if length(result) > 0 
    then
      result := result || chr(10);
    end if;
    result := result || 
              '    <' || 
              column_name || '> '' || ' || column_name || ' || ''</' || 
              column_name || '>';
  END LOOP;
  RETURN result;
END;
/

CREATE OR REPLACE FUNCTION sf_table_xml(table_name IN VARCHAR2)
RETURN CLOB
IS
BEGIN
  RETURN 
    'CREATE OR REPLACE VIEW ' || lower(substr(table_name,1,24)) || '_xmlvw AS
SELECT ''
  <' || lower(table_name) || '>
' || to_char(sf_columns_xml(table_name)) ||
'
  </' || lower(table_name) || '>'' xml,
  ' || table_name || '.*
FROM   '  || table_name;
END;
/

Now running 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

spool buildXMLViews.sql
 select 'exec execute immediate to_char(sf_table_xml(''' || table_name || '''));'
 from user_tables
 where dropped = 'NO';
 select 'exec execute immediate to_char(sf_table_xml(''' || view_name || '''));'
 from user_views;
 spool off
 @buildXMLViews 

(Hey, I didn't use q'| |')! Well, you may not all be on Oracle 10g.)

Sometimes, it's a sausage factory in here. Don't look at how it gets done, just pass the ketchup.