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