为什么值与select不同，查询输出和插入查询输出 [英] why values differ from select into query output and insert into query output

问题描述

我执行了以下查询，

I executed the following query,

DECLARE @xmlfile xml set @xmlfile=' <Services>
<ServiceInfo>
    <ServiceName>AeLookupSvc</ServiceName>
    <ServiceState>1</ServiceState>
    <HardwareProfiles>
      <HardwareProfile>
        <ServiceID>2</ServiceID>
        <Profile>Undocked Profile</Profile>
        <State>1</State>
      </HardwareProfile>
     </HardwareProfiles>



  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;

这里 servicemaster 是一个表，其中包含 serviceid 和 servicename

here servicemaster is a table which contains serviceid and servicename

serviceid servicename
1001       AelookupSvc
1002       ALG

以上查询输出为

the above query output is

assetid serviceid   state   profileid
1          1001         1     1

当我将所选值插入现有表时它返回

when i inserted the selected values to existing table it returns

insert into dbo.AssetServiceState

 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])

此查询给出了

this query gives the

assetid  serviceid   state   profileid
     1      1            1     1

为什么Serviceid值为1？请帮我解决这个问题？

Why the Serviceid value is resulted as 1 ? please help me to resolve this?

推荐答案

我的猜测是有多个名为'AeLookupSvc'的服务，它们有不同的ID，所以你得到哪一个，可以变化。

My guess would be that there's more than one service named 'AeLookupSvc' and they have different ids, so which one you get, can vary.

这篇关于为什么值与select不同，查询输出和插入查询输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！