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

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Wed, 08 Oct 2003 23:59:53 +0100
Message-ID: <pc49ov0c6c4c868rorrh7i5lbuae5dcanf@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.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Received on Wed Oct 08 2003 - 17:59:53 CDT