How Do I select Multiple XML Tag values in in SQL query

Question

Hi Team,

PLease let me know how do I select multiple values in XML Tag in SQL query.

 

For example

select Reference,NULLIF(MethodDetail.value('(//CheckNumber)[1]','nvarchar(20)'),'') as CheckNumber,Amount from RefundPayment where reference ='123434'

 

Here  MethodDetail Column value could be either of one of the mentioned below XML :-

 So in that case please let me know If some time some policy will have value as CheckNumber (which I am already retreiving) some time Card number as mentioned below, please let me know how do I choose either of one value while doing select statement

 

Note:- for the Policy its always either of on evalue will be present , it will never have both the values. 

<MethodDetail>
<MethodDetail>
<DisbursementMethod>1XCC</DisbursementMethod>
<MethodDetails>
<CardPaymentDetails>
<CardHolderName>hgfhgfhfhgfhg</CardHolderName>
<CardNumber>15151515151</CardNumber>
<CardType>VS</CardType>
</CardPaymentDetails>
</MethodDetails>
</MethodDetail>
</MethodDetail>
or
<MethodDetail>
<MethodDetail>
<Check>
<CheckNumber>1010101010</CheckNumber>
</Check>
</MethodDetail>
</MethodDetail>

Expected Output :- 

 Reference,CheckNumber, CardNumber, Amount

    123434      1010101010                                10

    655252                                15151515151   200

 

Kindly advise