title: Parse XML and generate new rows through SQL Query
body: <p>I've the input data in SQL table in below format:</p>
  
  <pre><code>ID    Text
   1    &lt;Key&gt;&lt;Name&gt;Adobe&lt;/Name&gt;&lt;Display&gt;Ado&lt;/Display&gt;&lt;/Key&gt;&lt;Key&gt;.....&lt;/Key&gt;
   2    &lt;Key&gt;&lt;Name&gt;&lt;/Name&gt;&lt;Display&gt;Microsoft&lt;/Display&gt;&lt;Version&gt;1.1&lt;/Version&gt;&lt;/Key&gt;
  </code></pre>
  
  <p>There can be multiple keys for each ID.There could be several thousand rows in a table in above format. I've to generate the final sql output in below format</p>
  
  <pre><code>ID  Name  Display    Version
  1   Adobe Ado       
  1   xyz   yz         1.2
  2         Microsoft  1.1
  </code></pre>
  
  <p>I am using the below query to parse Text column, but getting all data in one row. How can I split that data in multiple rows as indicated above.</p>
  
  <pre><code>SELECT 
      CAST(CAST(Text AS XML).query('data(/Key/Name)') AS VARCHAR(MAX)) AS Name,
      CAST(CAST(Text AS XML).query('data(/Key/Display)') as VARCHAR(MAX)) AS DisplayName,
      CAST(CAST(Text AS XML).query('data(/Key/Version)') AS VARCHAR(MAX)) AS Version
  FROM 
      ABC where ID = 1
  </code></pre>
  
  <p>Currently I am running this query for each ID at a time. Is there a way to run for all ID's together. Also, is there any other efficient way to get the desired output.</p>