Anyone know of an app that can do this...

I need to be able to load this sort of thing

<entity>
<att1>dfdffffd</att1>
<att2>rararar</att2>
<att3>qqqq</att3>
</entity>
<entity>
<att1>dfdffffd</att1>
<att2>rararar</att2>
<att3>qqqq</att3>
<att4>qqqq</att4>
</entity>
<entity>
<att2>rararar</att2>
<att3>qqqq</att3>
<att4>qqqq</att4>
</entity>

into a table
col1
col2
col3
col4
col5
col6
col7

i want to be able to map
<att1> to col1
<att2> to col2
etc

but when i parse the xml and an att is not there specify a default for the col

so
defaults when att missing
col1=null
col2="i"
col3=17.2

so that when it parses it doesn't crash out when an xml attribute is missing.

and i want this to be batchable too

any apps out there that will do this for me?

 

What you're looking for is a simple 'ETL' tool... (Extract, Transform & Load).

Microsoft DTS which comes with SQL Server can do this

 

what about Oracle?

 

I believe Microsoft DTS can talk to Oracle too. Do you have SQL 2005?

 

If you're using Oracle learn how to use sqlldr, it's very powerful. I don't know if it'll transform the XML for you but it'll do everything else. Transforming that XML is a two minute Perl job.