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.

Geek event aggregator

Click me!

I've written up an aggregator script (Python, of course) that browses an assortment of event announcement webpages and parses out the name/city/state/date information. Then I made an HTML DB application to serve it up according to your state or province (sorry, non-North-Americans).

No, I haven't written a brilliant AI to figure this out. It's just a bunch of regular expressions.

To do:

1. Mine from more event sources
2. Refactor the source code so it doesn't embarrass me and I can post it
3. Debug multiple hits for some events (InOUG especially)
4. Add non-North-American region support
5. Automate the mining (currently I kick it off and upload to HTML DB by hand)
6. get somebody to host the app in some more prominent site
7. Provide access to the pure XML as generated by the aggregator, so others don't have to go through the heck of parsing I do

Python for Oracle Geeks

IOUG posted my article, Python for Oracle Geeks, and referenced it in their 7/20/2005 "5 MINUTE BRIEFING: Oracle" e-mail. Ah, the fame, the adoring crowds, the paparazzi...