title: How to select from XML using T-SQL and XQuery
body: <p>I tried to do the xml parsing with sql.</p>
  
  <p>And I tried to select from the <code>Skill</code> tag but only one row is showing please any one help thanks in advance</p>
  
  <p>My XML format is as below</p>
  
  <pre><code>declare @UserDetails XML
  set @UserDetails='
  &lt;Registration&gt;
    &lt;objUserBasicData&gt;
      &lt;User_Id&gt;0&lt;/User_Id&gt;
      &lt;UserType&gt;0&lt;/UserType&gt;
      &lt;UserName&gt;test@test6.com&lt;/UserName&gt;
      &lt;Password&gt;user123&lt;/Password&gt;
      &lt;LastLoginTime&gt;2015-08-08T18:22:53.9953905+05:30&lt;/LastLoginTime&gt;
      &lt;IsActive&gt;true&lt;/IsActive&gt;
      &lt;CreatedBy&gt;0&lt;/CreatedBy&gt;
      &lt;CreatedOn&gt;2015-08-08T18:22:53.9953905+05:30&lt;/CreatedOn&gt;
      &lt;ModifiedBy&gt;0&lt;/ModifiedBy&gt;
      &lt;ModifiedOn&gt;2015-08-08T18:22:53.9953905+05:30&lt;/ModifiedOn&gt;
    &lt;/objUserBasicData&gt;
    &lt;objUserDetails&gt;
      &lt;UserDetails_Id&gt;0&lt;/UserDetails_Id&gt;
      &lt;User_Id&gt;0&lt;/User_Id&gt;
      &lt;FirstName&gt;test&lt;/FirstName&gt;
      &lt;MiddleName /&gt;
      &lt;LastName&gt;test&lt;/LastName&gt;
      &lt;ContactName /&gt;
      &lt;Email&gt;test@test6.com&lt;/Email&gt;
      &lt;AlternativeEmail /&gt;
      &lt;RegisterDate&gt;2015-08-08T18:22:54.0164133+05:30&lt;/RegisterDate&gt;
      &lt;Address1 /&gt;
      &lt;Address2 /&gt;
      &lt;Country&gt;0&lt;/Country&gt;
      &lt;State&gt;0&lt;/State&gt;
      &lt;City&gt;1&lt;/City&gt;
      &lt;PostCode /&gt;
      &lt;HomePhone /&gt;
      &lt;Mobile&gt;1231231232&lt;/Mobile&gt;
      &lt;Fax /&gt;
      &lt;Marketing&gt;false&lt;/Marketing&gt;
      &lt;HasCV&gt;false&lt;/HasCV&gt;
      &lt;CVName&gt;564195d7-4dfa-42e0-abc1-ef611be588a2.doc&lt;/CVName&gt;
      &lt;HasJobsByEmail&gt;false&lt;/HasJobsByEmail&gt;
      &lt;Website /&gt;
      &lt;CreatedBy&gt;0&lt;/CreatedBy&gt;
      &lt;CreatedOn&gt;2015-08-08T18:22:54.0164133+05:30&lt;/CreatedOn&gt;
      &lt;ModifiedBy&gt;0&lt;/ModifiedBy&gt;
      &lt;ModifiedOn&gt;2015-08-08T18:22:54.0164133+05:30&lt;/ModifiedOn&gt;
      &lt;DateOfBirth&gt;2015-08-10T00:00:00&lt;/DateOfBirth&gt;
      &lt;CurrentJobTitle&gt;test&lt;/CurrentJobTitle&gt;
      &lt;Career_Id&gt;2&lt;/Career_Id&gt;
      &lt;PrefferedLocation&gt;1&lt;/PrefferedLocation&gt;
      &lt;ExperienceInYears&gt;5&lt;/ExperienceInYears&gt;
      &lt;Gender&gt;1&lt;/Gender&gt;
    &lt;/objUserDetails&gt;
    &lt;Skills&gt;
      &lt;int&gt;2&lt;/int&gt;
      &lt;int&gt;1&lt;/int&gt;
    &lt;/Skills&gt;
  &lt;/Registration&gt;'
  </code></pre>
  
  <p>I used the query to select the data is </p>
  
  <pre><code>SELECT
      TEMPTABLE.UserData.value('int[1]','int') AS int
  FROM
      @UserDetails.nodes('/Registration/Skills')AS TEMPTABLE(UserData)
  </code></pre>
  
  <p>it gets result as </p>
  
  <pre><code>int
  -----------
  2
  </code></pre>
  
  <p>the <code>1</code> is missing</p>
  
  <p>The I used </p>
  
  <pre><code>    DECLARE @XMLDocPointer INT    
      EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @UserDetails    
  
  
     SELECT *
     FROM OPENXML(@XMLDocPointer,'/Registration/Skills',3)    
     WITH  ([int] INT)     
  
     EXEC sp_xml_removedocument @XMLDocPointer  
  </code></pre>
  
  <p>Again the result is the same </p>
  
  <pre><code>int
  -----------
  2
  </code></pre>
  
  <p>what do i have to do get the result as </p>
  
  <pre><code>int
  -----------
  2
  1
  </code></pre>