title: Oracle 10g: Reading XML values from Clob using XMLSequence and ExtractValue
body: <p>I'm starting to feel a bit dim. I've read a lot of web pages and tried a number of methods to do something that seems relatively simple.</p>
  
  <p>I've some XML stored a table. The table contains an ID and the XML in a CLOB. Something like:</p>
  
  <pre><code>ID = 1
  
  &lt;?xml version="1.0" encoding="UTF-8" ?&gt;
  &lt;CricketGame xmlns="http://www.somewhere.com/cricket/2002/09" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Version="2002A" xsi:type="CricketGame" &gt;
  &lt;TeamInfo TeamId="WestCountry" /&gt;
  &lt;SessionInfo SessionId="XhRya5m999988888" RestartSession="false" /&gt;
  &lt;Errors&gt;
  &lt;Error ErrorText="Generic Error" ErrorCode="700" SupplierErrorText="Connection: DECLINED" /&gt;
  &lt;Error ErrorText="Generic Error" ErrorCode="701" SupplierErrorText="Account Error" /&gt;
  &lt;/Errors&gt;
  &lt;/CricketGame&gt;
  </code></pre>
  
  <p>I have been trying to use a combination of extract and XMLSequence to create a Table and then extractvalue to get the specific values from ErrorText, ErrorCode and SupplierErrorText. My SQL:</p>
  
  <pre><code>SELECT 
  extractvalue(value(p), '/Error/@ErrorText') as errText,
  extractvalue(value(p), '/Error/@ErrorCode') as errCode,
  extractvalue(value(p), '/Error/@SupplierErrorText') as supErrText
  FROM gamestable  s, 
    Table(
      XMLSequence(
      extract(xmltype(s.xml), '/CricketGame/Errors/Error')
      )
    ) p
  where 
  s.gameID = 1
  </code></pre>
  
  <p>The SQL doesn't return any errors, but it doesn't return the data either.</p>
  
  <p>Can anyone see what I'm doing wrong?  Am I going about this totally wrong?  Is it perhaps something to do with the xmlns?</p>
  
  <p>Many thanks in advance!</p>