Re: Question about XML and Oracle.
From: John Peterson <j0hnp_at_comcast.net>
Date: Wed, 1 Dec 2010 11:04:58 -0700
Message-ID: <6MidnTesco0CE2vRnZ2dnUVZ5r6dnZ2d_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:04:58 -0700
Message-ID: <6MidnTesco0CE2vRnZ2dnUVZ5r6dnZ2d_at_giganews.com>
"Gerard H. Pille" <ghp_at_skynet.be> wrote in message news:4ce6b41f$0$14255$ba620e4c_at_news.skynet.be...
> 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!
>>
>>
>
> SELECT XMLROOT ( XMLElement("OuterElement",
> "<AllMyQueryResultsXML/>"
> ), VERSION '1.0', STANDALONE YES)
> AS "XMLROOT" FROM DUAL;
>
>
Thanks Gerald -- but I can't seem to figure out how to structure the query inside of the outer query. Maxim's reply seems to be (sort of) working for me, so I'll probably go that route. Received on Wed Dec 01 2010 - 12:04:58 CST