Re: Question about XML and Oracle.
From: John Peterson <j0hnp_at_comcast.net>
Date: Wed, 1 Dec 2010 11:03:56 -0700
Message-ID: <GZCdnayPoMj_E2vRnZ2dnUVZ5jadnZ2d_at_giganews.com>
>> 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!
>>
>>
Date: Wed, 1 Dec 2010 11:03:56 -0700
Message-ID: <GZCdnayPoMj_E2vRnZ2dnUVZ5jadnZ2d_at_giganews.com>
"Maxim Demenko" <mdemenko_at_gmail.com> wrote in message news: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
Dear Maxim,
Thanks so much for the tip! This seems to work great!
I wish there were some way to eliminate the embedded <ROWSET><ROW><XMLRESULT> tags -- but I guess it's simple enough to search/replace "after the fact".
The crux of what I was missing (I think) was that nifty "cursor" keyword. Very interesting...
Thanks again! Received on Wed Dec 01 2010 - 12:03:56 CST