Re: [xml-dev] Shredding XML

[Cecil New <cecil.new@gmail.com>]

Possibly a generic schema would work... by that I mean design where the tables are "vertical" instead of "horizontal".  In a vertical table approach, you have a parent column, child column, type column, and a value column.  For xml shredding, add the actual element names for the child.  The parent/child columns are generated keys that store the parent/child elements.  Plus you'd need a table to store the attribute values: child key, attrib name, attrib type (if needed), attrib value.

 

The value column is always a string, which must be converted to the associated type.

 

It will take a bit of logic to do the shredding, but once written, it won't need any modification for new element nodes or new attributes.

 

Use recursion to recover the xml hierarchical structure and element values.  Join to the attribute table to pick up the attribute value (you'll need to combine the two with logic - you can't do this with a single sql statement). [Note: Oracle recursion maintains parent/child row relationships - not sure that DB2 does this; I know Teradata cannot.  If DB2 does not maintain parent/child row relationships, then you'll need to write recursive logic to do this instead of a simple SQL statement.]

 

In the end, each row in the parent/child table represents an element node (the child).  And each row in the attrib table represents one attrib and value for the corresponding child element.

 

Finally, you'll need a fake node to store the root.  Hope this makes sense; sort of designed this on the fly while writing.

 

Cheers.