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

转载于:https://www.cnblogs.com/bulege/archive/2011/08/17/2143395.html