title: Postgresql substring to extract between angle brackets and quotes
body: <p>I have a field that has xml. I'm trying to extract data from an attribute which is followed by the <code>&gt;</code> that resides at the end of an element's start tag.</p>
  
  <p>E.g.</p>
  
  <p>I require the 250 from the scale attribute.</p>
  
  <pre><code>&lt;Request id=""1111"" order=""123456"" scale=""250""&gt;&lt;Project file=""test.txt""/&gt;&lt;/Request&gt;
  </code></pre>
  
  <p>I wish to extract it using substring. So I did:</p>
  
  <pre><code>select substring(myxml from '%scale="#"%#""&gt;&lt;Pro%' for '#') from mytable
  </code></pre>
  
  <p>This returns 250 which is what I want.</p>
  
  <p>But, the problem is that the next element for other records doesn't always start with <code>&lt;Pro</code></p>
  
  <p>So I tried this</p>
  
  <pre><code>select substring(myxml from '%scale="#"%#""&gt;&lt;%' for '#') from mytable
  </code></pre>
  
  <p>It returns:</p>
  
  <pre><code>250"&gt;&lt;Project file="test.txt"/&gt;&lt;/Request&gt;
  </code></pre>
  
  <p>Where am I going wrong? Is there a better way? 
  Many thanks.</p>