Home » Developer & Programmer » JDeveloper, Java & XML » query an xmltype column/table (Oracle 10g)
query an xmltype column/table [message #382958] Mon, 26 January 2009 14:01
Messages: 37
Registered: May 2003
hi all,
I have a whole xml document stored in 1 row in a table called siteList, the column is called xmlsitedata; the xsd of the xml file is given below:
<?xml version="1.0" encoding="UTF-8" ?>
- <xs:schema elementFormDefault="qualified" attributeFormDefault="unqualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
- <xs:element name="siteList">
- <xs:complexType>
- <xs:sequence>
<xs:element name="site" type="siteType" minOccurs="0" maxOccurs="unbounded" />
- <xs:complexType name="siteType">
- <xs:sequence>
<xs:element name="nameEn" type="xs:string" />
<xs:element name="nameFr" type="xs:string" />
<xs:attribute name="code" type="xs:string" />

I am executing the select below:

select x.XMLSITEDATA.extract('/siteList/site/nameEn/text()').getCLOBVal() "stName" from wsitelist x;

and I get a string with all site names concatenated - my question is how do I modify the query so that I split the names and get the site names one by one,
Previous Topic: couldn't connect to database
Next Topic: unconventional XML- extracting data at two levels
Goto Forum:

Current Time: Sat Nov 28 11:08:39 CST 2015

Total time taken to generate the page: 0.07179 seconds