求助 XML查询

Question

xml select

Answer 1

A.exits('/EBPCaseDetailType/responseHistory/activity')>0
  条件是不是写错了？？

Answer 2

create table tj(id int,字段A xml)insert into tj(id,字段A)
select 101,
'<EBPCaseDetailType>
  <openReason xmlns="http://www.ebay.com/etplace/resolution/v1/services">Item not received</openReason>
  <decision xmlns="http://www.ebay.com/etplace/resolution/v1/services">OTHER</decision>
  <FVFCredited xmlns="http://www.ebay.com/etplace/resolution/v1/services">false</FVFCredited>
  <globalId xmlns="http://www.ebay.com/etplace/resolution/v1/services">EBAY_UK</globalId>
  <responseHistory xmlns="http://www.ebay.com/etplace/resolution/v1/services">
    <author>
      <role>BUYER</role>
    </author>
    <activity>contactCustomerSupport</activity>
    <creationDate>2013-07-02T10:13:48Z</creationDate>
  </responseHistory>
  <responseHistory xmlns="http://www.ebay.com/etplace/resolution/v1/services">
    <author>
      <role>EBAY</role>
    </author>
    <activity>systemExpireGrace</activity>
    <creationDate>2013-06-30T16:00:54Z</creationDate>
  </responseHistory>
  <responseHistory xmlns="http://www.ebay.com/etplace/resolution/v1/services">
    <note>i have not recived the item yet</note>
    <author>
      <role>BUYER</role>
    </author>
    <activity>create</activity>
    <creationDate>2013-06-23T01:26:25Z</creationDate>
  </responseHistory>
  <agreedRefundAmount xmlns="http://www.ebay.com/etplace/resolution/v1/services">0</agreedRefundAmount>
  <detailStatus xmlns="http://www.ebay.com/etplace/resolution/v1/services">3</detailStatus>
  <initialBuyerExpectation xmlns="http://www.ebay.com/etplace/resolution/v1/services">103</initialBuyerExpectation>
</EBPCaseDetailType>'
-- 查询存在的节点 <activity>contactCustomerSupport</activity>
select * 
 from tj 
 where 字段A.exist('
 declare namespace NS="http://www.ebay.com/etplace/resolution/v1/services";
 /EBPCaseDetailType/NS:responseHistory/NS:activity[.="contactCustomerSupport"]')=1/*
id          字段A
----------- ----------------------------------------
101         <EBPCaseDetailType><openReason ......(1 row(s) affected)
*/
-- 查询存在的节点 <activity>create</activity>
select * 
 from tj 
 where 字段A.exist('
 declare namespace NS="http://www.ebay.com/etplace/resolution/v1/services";
 /EBPCaseDetailType/NS:responseHistory/NS:activity[.="create"]')=1/*
id          字段A
----------- ----------------------------------------
101         <EBPCaseDetailType><openReason ......(1 row(s) affected)
*/
-- 查询不存在的节点 <activity>1122334</activity>
select * 
 from tj 
 where 字段A.exist('
 declare namespace NS="http://www.ebay.com/etplace/resolution/v1/services";
 /EBPCaseDetailType/NS:responseHistory/NS:activity[.="1122334"]')=1/*
id          字段A
----------- ---------------(0 row(s) affected)
*/