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!

John Peterson wrote:
SELECT XMLROOT ( XMLElement("OuterElement",
"<AllMyQueryResultsXML/>"
), VERSION '1.0', STANDALONE YES)
AS "XMLROOT" FROM DUAL;

On 18.11.2010 21:30, John Peterson wrote:
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

"Maxim Demenko" <mdemenko (AT) gmail (DOT) com> wrote

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!

"Gerard H. Pille" <ghp (AT) skynet (DOT) be> wrote

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.

John Peterson wrote:
Pourtant, c'est simple comme bonjour:


XMLROOT
--------------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0" standalone="yes"?>
<OuterElement>
<Topic>
<TOPICID>1</TOPICID>
<TOPICDESC>This is the first topic</TOPICDESC>
<AREA>0</AREA>
</Topic>
<Topic>
<TOPICID>2</TOPICID>
<TOPICDESC>This is the second topic</TOPICDESC>
<AREA>0</AREA>
</Topic>
<Topic>
<TOPICID>3</TOPICID>
<TOPICDESC>This is the third topic</TOPICDESC>
<AREA>1</AREA>
</Topic>
<Topic>
<TOPICID>4</TOPICID>
<TOPICDESC>This is the fourth topic</TOPICDESC>
<AREA>1</AREA>
</Topic>
</OuterElement>


SQL> l
1 SELECT XMLROOT (
2 XMLElement(
3 "OuterElement",
4 xmlagg(
5 XMLElement(
6 "Topic",
7 xmlelement("TOPICID",TOPICID),
8 xmlelement("TOPICDESC",TOPICDESC),
9 xmlelement("AREA",AREA)
10 )
11 )
12 ), VERSION '1.0', STANDALONE YES)
13* AS "XMLROOT" FROM faq.topic