Re: How to Generate XML and call a query on it?

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 15 Jul 2005 09:14:19 -0700
Message-ID: <1121444059.119525.280000@g44g2000cwa.googlegroups.com>

I have recently started looking into XML in the database, and was amazed (and confused) by the range of options. There are a set of queries called SQLX queries that will do what you want (couple with the build in type for xml XMLTYPE). I will try to post an example that does what you want, and then you will have to look in the XML Developers Guide for the details.

create table testxml (regionnum varchar2(10), regionname varchar2(10),
 people varchar2(50) );

insert into testxml values ('reg1','place a','A Person'); insert into testxml values ('reg2','place b','B Person');

select

	             XMLELEMENT( "RegionNum", regionnum ),
	             XMLELEMENT( "RegionName", regionname ),
	             XMLELEMENT( "Person", people )

from
(
select regionnum, regionname, people
from testxml
)

XMLELEMENT("REGIONNUM",REGIONNUM)

---

XMLELEMENT("REGIONNAME",REGIONNAME)

---

XMLELEMENT("PERSON",PEOPLE)

---

<RegionNum>reg1</RegionNum>
<RegionName>place a</RegionName>
<Person>A Person</Person>

<RegionNum>reg2</RegionNum>
<RegionName>place b</RegionName>
<Person>B Person</Person>

So the SQLX function xmlelement produces tags with your desired tag name, and a value of you choice.
To add the regions 'wrapper' tag use this query:

select XMLELEMENT ( "REGION",

	             XMLELEMENT( "RegionNum", regionnum ),
	             XMLELEMENT( "RegionName", regionname ),
	             XMLELEMENT( "Person", people ) )

from
(
select regionnum, regionname, people
from testxml
)

XMLELEMENT("REGION",XMLELEMENT("REGIONNUM",REGIONNUM),XMLELEMENT("REGIONNAME",RE

---

<REGION><RegionNum>reg1</RegionNum><RegionName>place
a</RegionName><Person>A Per
<REGION><RegionNum>reg2</RegionNum><RegionName>place
b</RegionName><Person>B Per

Now, to add a wrapper <REGIONS> around all the rows of the set use the XMLAGG function which is kind of like group by, as it returns all rows aggrigated:-

SQL> select XMLELEMENT ( "REGIONS",

  2           XMLAGG ( XMLELEMENT ( "REGION",
  3                  XMLELEMENT( "RegionNum", regionnum ),
  4                  XMLELEMENT( "RegionName", regionname ),
  5                  XMLELEMENT( "Person", people ) )
  6              )
               )  7

  8 from
  9 (
 10 select regionnum, regionname, people  11 from testxml
 12 );

XMLELEMENT("REGIONS",XMLAGG(XMLELEMENT("REGION",XMLELEMENT("REGIONNUM",REGIONNUM

---

<REGIONS><REGION><RegionNum>reg1</RegionNum><RegionName>place
a</RegionName><Per

SQL> So that gets you the XML you want, and in PLSQL it returns it as an XMLTYPE variable, meaning you can use the XML query functions on it existsnode, getstringval, etc that take XPath expressions, however why do you want to generate XML and then filter it this way? Surely you query should filter out the rows you dont want and just return the xml as you want it:

select XMLELEMENT ( "REGIONS",

         XMLAGG ( XMLELEMENT ( "REGION",
	             XMLELEMENT( "RegionNum", regionnum ),
	             XMLELEMENT( "RegionName", regionname ),
	             XMLELEMENT( "Person", people ) )
	         )
	       )

from
(
select regionnum, regionname, people
from testxml
where --filter out the stuff i dont want ... ) Received on Fri Jul 15 2005 - 11:14:19 CDT