title: why values differ from select into query output and insert into query output
body: <p>I executed the following query,</p>
  
  <pre><code>DECLARE @xmlfile xml set @xmlfile=' &lt;Services&gt;
  &lt;ServiceInfo&gt;
      &lt;ServiceName&gt;AeLookupSvc&lt;/ServiceName&gt;
      &lt;ServiceState&gt;1&lt;/ServiceState&gt;
      &lt;HardwareProfiles&gt;
        &lt;HardwareProfile&gt;
          &lt;ServiceID&gt;2&lt;/ServiceID&gt;
          &lt;Profile&gt;Undocked Profile&lt;/Profile&gt;
          &lt;State&gt;1&lt;/State&gt;
        &lt;/HardwareProfile&gt;
       &lt;/HardwareProfiles&gt;
  </code></pre>
  
  <p>'</p>
  
  <pre><code>  select 
      1 as assetid,
       (select dbo.ServiceMaster.serviceid from dbo.ServiceMaster where dbo.ServiceMaster.servicename=[profile].query('../../ServiceName').value('.', 'varchar(max)'))as serviceid,
       (select [profile].query('State').value('.','bit'))as state,
       1 as profileid
       into #temp
      from 
      @xmlfile.nodes('Services/ServiceInfo/HardwareProfiles/HardwareProfile')as Profiles([profile])
      select * from #temp;
  
      drop table #temp;
  </code></pre>
  
  <p>here servicemaster is a table which contains serviceid and servicename</p>
  
  <pre><code>serviceid servicename
  1001       AelookupSvc
  1002       ALG
  </code></pre>
  
  <p>the above query output is</p>
  
  <pre><code>assetid serviceid   state   profileid
  1          1001         1     1
  </code></pre>
  
  <p>when i inserted the selected values to existing table it returns</p>
  
  <p>EDIT: 
  insert into dbo.AssetServiceState</p>
  
  <pre><code> select 
          1 as assetid,
           (select dbo.ServiceMaster.serviceid from dbo.ServiceMaster where dbo.ServiceMaster.servicename=[profile].query('../../ServiceName').value('.', 'varchar(max)'))as serviceid,
           (select [profile].query('State').value('.','bit'))as state,
           1 as profileid
   from 
          @xmlfile.nodes('Services/ServiceInfo/HardwareProfiles/HardwareProfile')as Profiles([profile])
  </code></pre>
  
  <p>this query gives the </p>
  
  <pre><code>   assetid  serviceid   state   profileid
          1      1            1     1
  </code></pre>
  
  <p>Why the Serviceid value is resulted as 1 ? please help me to resolve this?</p>