title: sql setting xml property through a loop
body: <p>trying desperately to combine 2 simple answers into specifically what i need.</p>
  
  <p><a href="https://stackoverflow.com/questions/4940368/sql-loop-and-set-properties">sql loop and set properties</a></p>
  
  <p><a href="https://stackoverflow.com/questions/4939901/sql-to-set-an-xml-value">sql to set an xml value</a></p>
  
  <pre><code>SET @I := 0;
  SELECT *,
  @I := @I + 1
  SET xml = UpdateXML(xml,'comic/pageNumber', '&lt;pageNumber&gt;'.@I.'&lt;/pageNumber&gt;')
  FROM `comics`
  ORDER BY ExtractValue(xml,'comic/pageNumber')+100000 ASC
  </code></pre>
  
  <p>this is as close as i have come,  i know the SELECT / ORDER BY works separate from trying to SET the xml property.</p>
  
  <blockquote>
    <p>side note: the +100000 is a work
    around to treat the value as numeric
    for sorting.  otherwise 11 &lt; 2  but
    100011 > 100002</p>
  </blockquote>
  
  <p>i have also tried this</p>
  
  <pre><code>SET @I := 0;
  UPDATE comics,
  @I := @I + 1 AS newPageNumber
  SET xml = UpdateXML(xml,'comic/pageNumber', '&lt;pageNumber&gt;'.@I.'&lt;/pageNumber&gt;')
  WHERE 1
  ORDER BY ExtractValue(xml,'comic/pageNumber')+100000 ASC
  </code></pre>
  
  <p>i think i just don't know how to combine the SELECT and UPDATE</p>