SQL读取XML字段类型的信息

USE CSOS_NEW_2
GO

--(1)定义临时表
DECLARE @table TABLE(id INT IDENTITY(1,1),XMLDetail XML)
DECLARE @xml XML
SET @xml='<EBPCaseDetailType>
  <openReason xmlns="http://www.ebay.com/marketplace/resolution/v1/services">Item not received</openReason>
  <decisionReason xmlns="http://www.ebay.com/marketplace/resolution/v1/services">11002</decisionReason>
  <decisionDate xmlns="http://www.ebay.com/marketplace/resolution/v1/services">2013-06-25T18:09:19Z</decisionDate>
  <decision xmlns="http://www.ebay.com/marketplace/resolution/v1/services">SELLER_FAULT</decision>
  <FVFCredited xmlns="http://www.ebay.com/marketplace/resolution/v1/services">false</FVFCredited>
  <notCountedInBuyerProtectionCases xmlns="http://www.ebay.com/marketplace/resolution/v1/services">false</notCountedInBuyerProtectionCases>
  <globalId xmlns="http://www.ebay.com/marketplace/resolution/v1/services">EBAY_UK</globalId>
  <responseHistory xmlns="http://www.ebay.com/marketplace/resolution/v1/services">
    <author>
      <role>EBAY</role>
    </author>
    <activity>agentResolve</activity>
    <creationDate>2013-06-25T18:10:03Z</creationDate>
  </responseHistory>
  <responseHistory xmlns="http://www.ebay.com/marketplace/resolution/v1/services">
    <author>
      <role>BUYER</role>
    </author>
    <activity>contactCustomerSupport</activity>
    <creationDate>2013-06-25T12:24:53Z</creationDate>
  </responseHistory>
  <responseHistory xmlns="http://www.ebay.com/marketplace/resolution/v1/services">
    <author>
      <role>EBAY</role>
    </author>
    <activity>systemExpireGrace</activity>
    <creationDate>2013-06-24T16:01:13Z</creationDate>
  </responseHistory>
  <responseHistory xmlns="http://www.ebay.com/marketplace/resolution/v1/services">
    <note>Nothing has yet been received, if the item can be sent this week then please send it. If not then a refund please. Thankyou</note>
    <author>
      <role>BUYER</role>
    </author>
    <activity>create</activity>
    <creationDate>2013-06-17T05:34:49Z</creationDate>
  </responseHistory>
  <agreedRefundAmount xmlns="http://www.ebay.com/marketplace/resolution/v1/services">0</agreedRefundAmount>
  <paymentDetail xmlns="http://www.ebay.com/marketplace/resolution/v1/services">
    <moneyMovement id="M.1">
      <type>REFUND</type>
      <fromParty>
        <role>SELLER</role>
      </fromParty>
      <toParty>
        <role>BUYER</role>
      </toParty>
      <amount currencyId="GBP">4.19</amount>
      <paymentMethod>PAYPAL</paymentMethod>
      <paypalTransactionId>5NE10254S0169263L</paypalTransactionId>
      <status>SUCCESS</status>
      <transactionDate>2013-06-25T18:09:18Z</transactionDate>
    </moneyMovement>
  </paymentDetail>
  <detailStatus xmlns="http://www.ebay.com/marketplace/resolution/v1/services">4</detailStatus>
  <initialBuyerExpectation xmlns="http://www.ebay.com/marketplace/resolution/v1/services">103</initialBuyerExpectation>
</EBPCaseDetailType>';


--(2)创建测数据
INSERT @table (XMLDetail )
VALUES  (
          @xml  -- XMLDetail - xml
          )
          
SELECT * 
FROM @table


--(3)读取XML字段的数据
;
WITH XMLNAMESPACES('http://www.ebay.com/marketplace/resolution/v1/services' AS xs)
SELECT  id,XMLDetail.value('(EBPCaseDetailType/xs:paymentDetail/xs:moneyMovement/xs:type)[1]','nvarchar(max)') AS 'Paymentype',
XMLDetail.value('(EBPCaseDetailType/xs:paymentDetail/xs:moneyMovement/xs:fromParty/xs:role)[1]','nvarchar(max)') AS 'Refundrole',
XMLDetail.value('(EBPCaseDetailType/xs:paymentDetail/xs:moneyMovement/xs:paypalTransactionId)[1]','nvarchar(max)') AS 'paypalTransactionId',
XMLDetail.value('(EBPCaseDetailType/xs:openReason)[1]','nvarchar(max)') AS 'openReason'
FROM @table

 
 

 

显示结果如下：

id  Paymentype  Refundrole    paypalTransactionId    openReason
1   REFUND       SELLER         5NE10254S0169263L  Item not received