title: MySQL use ExtractValue(XML, 'Value/Values') to get all multiple values (split one column into rows)
body: <p>I have a non-normal field containing multiple values because it is Xml data that wasn't intended to be queried, until now. Can MySQL split this xml column into multiple rows?</p>
  
  <p>Table</p>
  
  <pre><code>NameA   |   &lt;Xml&gt;&lt;Values&lt;Value&gt;1&lt;/Value&gt;&lt;Value&gt;2&lt;/Value&gt;&lt;Value&gt;3&lt;/Value&gt;&lt;/Values&gt;&lt;/Xml&gt;
  NameB   |   &lt;Xml&gt;&lt;Values&lt;Value&gt;1&lt;/Value&gt;&lt;Value&gt;2&lt;/Value&gt;&lt;/Values&gt;&lt;/Xml&gt;
  NameC   |   &lt;Xml&gt;&lt;Values&lt;Value&gt;1&lt;/Value&gt;&lt;Value&gt;2&lt;/Value&gt;&lt;Value&gt;3&lt;/Value&gt;&lt;Value&gt;4&lt;/Value&gt;&lt;/Values&gt;&lt;/Xml&gt;
  </code></pre>
  
  <p>I want</p>
  
  <pre><code>NameA   |   1
  NameA   |   2
  NameA   |   3
  NameB   |   1
  </code></pre>
  
  <p>Like this MSSQL/TSQL solution</p>
  
  <pre><code>SELECT
      I.Name,
      Value.value('.','VARCHAR(30)') AS Value 
  FROM
      Item AS I
  CROSS APPLY
      Xml.nodes('/Xml/Values/Value') AS T(Value)
  WHERE
      I.TypeID = 'A'
  </code></pre>
  
  <p>But in MySQL I can only get </p>
  
  <pre><code>NameA   |   123
  NameB   |   12
  NameC   |   1234
  </code></pre>
  
  <p>with </p>
  
  <pre><code>SELECT
      I.`Name`,
      ExtractValue(Xml,'/Xml/Values/Value') AS ListOfValues
  FROM
      Item AS I
  WHERE
      I.TypeID = 'A'
  ;
  </code></pre>
  
  <p>Are there any elegant ways to split xml in MySQL?</p>