为什么值与select不同，查询输出和插入查询输出

问题描述

我执行了以下查询，

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?

推荐答案

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