title: Query @xml variable to get a rowset
body: <p>I have an xml data that looks like below.</p>
  
  <pre><code>DECLARE @XmlContent XML
  SET @XmlContent = 
  '&lt;Entities&gt;
  &lt;Entity type = "5"&gt;
        &lt;item id ="1"/&gt;
        &lt;item id ="2"/&gt;
        &lt;item id ="2"/&gt;
  &lt;/Entity&gt;
  &lt;Entity type = "6"&gt;
        &lt;item id ="3"/&gt;
        &lt;item id ="4"/&gt;
        &lt;item id ="5"/&gt;
  &lt;/Entity&gt;
  &lt;/Entities&gt;'
  </code></pre>
  
  <p>I want to select data from this and insert into a table in the following format -</p>
  
  <pre>
  ------------
  Type Id
  ------------
  5     1
  5     2
  5     2
  6     3
  6     4
  6     5
  </pre>
  
  <p>Can some one help me to write query for this in sql server?</p>