You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by kiran vuppla <ki...@yahoo.com> on 2008/05/10 19:03:13 UTC
XML Parsing using Oracle10g

Hi,

I need to write a SQL to get the output shown below from XML file (sample below). I have written an SQL but it is giving an exception "ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence". Can some one help me.


The SQL Which I have tried:

select tbl."img" as image, tbl."altText" as altText, tbl."url" as url, tbl."text" as text 
from site_module m 
, XMLTABLE ('//show" PASSING m.configuration_data 
COLUMNS "img" varchar2(1024) PATH '//image', "altText" varchar2(1024) PATH '//imageAltText',
"url" varchar2(1024) PATH '//links/link/linkUrl', "text" varchar2(1024) PATH '//links/link/linkText') tbl 

Sample XML File That I am Reading:

<content>
   <feature>
    <body>Sample Text</body>
    <links>
      <link>
        <linkUrl>http://www.planetgreen.discovery.com</linkUrl>
        <linkText>Home Page</linkText>
      </link>
      <link>
        <linkUrl>http://www.google.com</linkUrl>
        <linkText>Google</linkText>
      </link>
      <link>
        <linkUrl>en.wikipedia.org/wiki/Cron</linkUrl>
        <linkText>Cron</linkText>
      </link>
    </links>
  </feature>
  <showList>
    <show>
      <image>path/to/Image</image>
      <imageAltText>Alternate</imageAltText>
      <showLink>Link to Show</showLink>
      <headline>Lorem Ipsum Dolor Sit Amet</headline>
      <links>
        <link>
          <linkUrl>en.wikipedia.org/wiki/Cron/test.html</linkUrl>
          <linkText>Cron2</linkText>
        </link>
        <link>
            <linkUrl>http://www.google.com</linkUrl>
            <linkText>Google</linkText>
        </link>
      </links>
    </show>
  </showList>
  <showList>
    <show>
      <image>path/to/Image2</image>
      <imageAltText>Alternate2</imageAltText>
      <showLink>Link to Show2</showLink>
      <headline>Lorem Ipsum Dolor Sit Amet</headline>
      <links>
        <link>
          <linkUrl>en.wikipedia.org/wiki/Cron/tes2t.html</linkUrl>
          <linkText>Cron4</linkText>
        </link>
      </links>
    </show>
  </showList>
</content>

Output should be:

          IMAGE
         ALTTEXT
         URL
         TEXT
             path/to/Image
         Alternate
         en.wikipedia.org/wiki/Cron/test.html
         Cron2
             path/to/Image
         Alternate
         http://www.google.com
         Google
             path/to/Image2
         Alternate2
         en.wikipedia.org/wiki/Cron/tes2t.html
         Cron4
        
 
       
---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile.  Try it now.