Re: Question about XML and Oracle.

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 19 Nov 2010 18:45:42 +0100
Message-ID: <4CE6B7C6.4080107_at_gmail.com>

On 18.11.2010 21:30, John Peterson wrote:
> I'm not sure if this is the correct forum, but I'll give it a shot.
>
> I'm using the Oracle built-in XML functions to format a result set into
> an XML representation.
>
> However, I can't quite figure out how to "wrap" my results with a
> header/footer.
>
> For example, I want my output to be:
>
> <?xml version="1.0" encoding="UTF-8" ?>
> <OuterElement>
> <AllMyQueryResultsXML/>
> </OuterElement>
>
> I've successfully created a multi-row query that generates the
> <AllMyQueryResultsXML>. But, I can't figure out how to get the final
> step of wrapping those results in the version and <OuterElement> aspects.
>
> I know I can use XMLRoot for the version aspect. But the <OuterElement>
> eludes me. I've tried doing stuff like:
>
> select xmltype("<OuterElement>") from dual
> union all
> ....MyMultiRowQueryThatReturnsXMLTYPE...
> union all
> select xmltype("</OuterElement>") from dual
>
> But that doesn't work, because XMLTYPE expects well-formed XML. I've
> tried variations on the theme with XMLSERIALIZE and XMLCONCAT, but I
> can't seem to get it to work.
>
> Any help that anyone can provide would be *much* appreciated.
>
> Thanks!
>
>

You may try something like this:

SQL> select xmlroot(

   2           xmlelement("OuterElement",
   3              xmltype(cursor(select loc from dept))
   4                     )
   5           , version '1.0').extract('/') xml

   6 from dual
   7 /

XML

---

<?xml version="1.0"?>
<OuterElement>

   <ROWSET>

     <ROW>
       <LOC>NEW YORK</LOC>
     </ROW>
     <ROW>
       <LOC>DALLAS</LOC>
     </ROW>
     <ROW>
       <LOC>CHICAGO</LOC>
     </ROW>
     <ROW>
       <LOC>BOSTON</LOC>
     </ROW>

   </ROWSET>
</OuterElement>

where xmltype(cursor(...)) should represent your data like in "MyMultiRowQueryThatReturnsXMLTYPE"
Note, that extract('/') is added only to make pretty print of produced xml, it isn't necessary for your purpose.

Best regards

Maxim Received on Fri Nov 19 2010 - 11:45:42 CST