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
kowalsky
Messages: 37
Registered: May 2003
Member
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:sequence>
</xs:complexType>
</xs:element>
- <xs:complexType name="siteType">
- <xs:sequence>
<xs:element name="nameEn" type="xs:string" />
<xs:element name="nameFr" type="xs:string" />
</xs:sequence>
<xs:attribute name="code" type="xs:string" />
</xs:complexType>
</xs:schema>


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,
Thanks,
kowalsky
Previous Topic: couldn't connect to database
Next Topic: unconventional XML- extracting data at two levels
Goto Forum:
  


Current Time: Sun Nov 23 23:32:05 CST 2014

Total time taken to generate the page: 0.10515 seconds