reading ms sql's "for auto" xml into oracle

Hi Folks,
I've noticed that MS SQLServer and Oracle seem to have a different idea of how to format XML. For example, I have a table:

test(
 val1 varchar2(10),
 val2 varchar2(10)
);

When I do a quick query against SQLServer using the "for xml auto" tag, I get rows like this:

<test val1="one" val2="x" />
<test val1="two" val2="y" />
<test val1="three" val2="z" />

However, I have been unable to get Oracle to parse XML like this. Instead Oracle seems to like XML formatted like this: <test>
  <row>
<val1>"one"</val1>
<val2>"x"</val2>

  </row>
  <row>
<val1>"two"</val1>
<val2>"y"</val2>

  </row>
  <row>
<val1>"three"</val1>
<val2>"z"</val2>

  </row>
</test>

So, the question is how to I reconcile these two formats? I'd like to get Oracle to read SQLServer's format, but I'm open to other ideas.

And just in case it's not obvious, I'm not particularly well-versed in XML. I'm using Oracle 9.2.0.3 and SQLServer 2000, both on a Windows 2000 platform. I've been using DBMS_XMLSAVE.insertXML() for the insertions.

Thanks for any advice you can give.
ry Received on Wed Oct 08 2003 - 16:44:08 CDT