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.