| 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
