1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

query an xmlType column

Discussion in 'SQL PL/SQL' started by kowalsky, Jan 27, 2009.

  1. kowalsky

    kowalsky Guest

    hi all,
    I have a XMLType table with one column - I have presently one row, in my column xmlsitedata I have stored one large xml file.The schema definition is given below:
    Code (Text):
    <?xml version="1.0" encoding="UTF-8" ?>
    - <xs:schema elementFormDefault="qualified" attributeFormDefault="unqualified" xmlns:xs="">
    - <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 have executed the query below:
    Code (Text):
    select x.XMLSITEDATA.extract('/siteList/site/nameEn/text()').getCLOBVal() "stName" from wsitelist x;
    and I get all english names of some 200 locations, however, there is 1 row selected and all names show up on one row. How do I split them into 200 or whatever rows?

    Thanks,
    Kowalsky
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India