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

Andy Hassall <andy_at_andyh.co.uk> wrote in message news:<pc49ov0c6c4c868rorrh7i5lbuae5dcanf_at_4ax.com>...
> On Wed, 8 Oct 2003 21:54:19 GMT, Brian Peasland <dba_at_remove_spam.peasland.com>
> wrote:
>
> >> 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" />
> >
> >I haven't used this in SQL Server, but what you've written above is
> >*not* XML format. What you have above are name/value pairs, and XML is
> >not name/value pairs like this. The example you provided as Oracle's
> >output is XML output.
>
> Why do you say that's not XML? They're both valid XML, with the differences
> being just that the Oracle example has the data as the character data contents
> of elements named for the columns; and the one above has the data in attributes
> named for the columns.
>
> You can get either out if you want.
>
> SQL> SELECT SYS_XMLAGG(value(e), xmlformat('test')).getStringVal()
> 2 FROM TABLE(XMLSequence(cursor (SELECT val1 "val1", val2 "val2" FROM test),
> 3 xmlformat('row'))) e;
>
> SYS_XMLAGG(VALUE(E),XMLFORMAT(
> --------------------------------------------------------------------------------
> <?xml version="1.0"?>
> <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>
>
> SQL> select xmlelement("test",
> 2 xmlattributes(val1 "val1", val2 "val2")).getStringVal()
> 3 from test;
>
> XMLELEMENT("TEST",XMLATTRIBUTE
> --------------------------------------------------------------------------------
> <test val1="one" val2="x"></test>
> <test val1="two" val2="y"></test>
> <test val1="three" val2="z"></test>
>
> Oracle hasn't bothered contracting the empty elements down to <test />, but
> they're equivalent.

This is a lot more than I knew before, but I'm more interested in knowing how to take the attribute format(like immediately above) and load it into an Oracle table. The parsers included in Oracle only seem to read other format. Any ideas there?

Thanks again,
ry Received on Thu Oct 09 2003 - 10:34:54 CDT