How to transform XML-data to a relational view?

From: <ralbox_at_gmail.com>
Date: 26 Jan 2006 00:27:21 -0800
Message-ID: <1138264041.295137.213140@g49g2000cwa.googlegroups.com>

Hi all experienced XML-programmers!

I need some help on defining a relational view named "XMLVIEW" for transforming and denormalizing an XML-struture stored in a column "SYS_NC_ROWINFO$" of type SYS.XMLTYPE in a table named "XMLTAB".

This is a sample of my xml-structure:
<root>

	<level id="ABCD" type="01">
		<item no="123">


			<url>http://url1.com</url>

			<desc>This is link1</desc>

		</item>
		<item no="234">


			<url>http://url2.com</url>

			<desc>This is link2</desc>

		</item>
	</level>
	<level id="BCDE" type="01">
		<item no="123">


			<url>http://url3.com</url>

			<desc>This is link3</desc>

		</item>
	</level>

</root>

And this is the desired denormalized output from a "select * from XMLVIEW" :

root	levelid	leveltype itemno	url		desc
	ABCD	01	123	http://url1.com	This is link1
	ABCD	01	234	http://url2.com	This is link2
	BCDE	01	123	http://url3.com	This is link3

(the root element may be ommitted)

How can I define a view that gives me this result? I have tried lots of variants over "create view XMLVIEW as select extractValue(value(x.SYS_NC_ROWINFO$),..... from XMLTAB x;" but without success :-(
Can anybody show me how to transform my xml-structure to a relational view? Received on Thu Jan 26 2006 - 02:27:21 CST