SQL查找XML里面符合某个条件的用法

表里面点开一个XML字段的值如下

<MasterxRow>
    <ActivityType>2</ActivityType>
    <ProductType>21</ProductType>
    <RefNo>222222222222222222222</RefNo>
    <ContactResult>0</ContactResult>
    <Reason />
    <OtherReason />
    <OtherActivityType />
    <OtherProductType />
    <ReferrerID />
    <ReferrerType />
    <CampaignCode>889</CampaignCode>
    <CampaignName>70</CampaignName>
    <IsNull>false</IsNull>
    <AcceptReason>合作愉快</AcceptReason>
    <AcceptReasonText>99</AcceptReasonText>
  </MasterxRow>

搜索所有符合ProductType=21的数据

select * from tblContactLogs
where ProductResponse.exist('//ProductType[.=3]')=1

获取节点里面的值

SELECT ProductResponse.value ('(//ProductType)[1]' , 'varchar(max)' ) as ProductType From table