2
Answers

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

Sharan Kiran

Sharan Kiran
4y
1.5k
1
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. 
  1. <MethodDetail>  
  2. <MethodDetail>  
  3. <DisbursementMethod>1XCC</DisbursementMethod>  
  4. <MethodDetails>  
  5. <CardPaymentDetails>  
  6. <CardHolderName>hgfhgfhfhgfhg</CardHolderName>  
  7. <CardNumber>15151515151</CardNumber>  
  8. <CardType>VS</CardType>  
  9. </CardPaymentDetails>  
  10. </MethodDetails>  
  11. </MethodDetail>  
  12. </MethodDetail>  
  13. or  
  14. <MethodDetail>  
  15. <MethodDetail>  
  16. <Check>  
  17. <CheckNumber>1010101010</CheckNumber>  
  18. </Check>  
  19. </MethodDetail>  
  20. </MethodDetail> 
Expected Output :- 
 Reference,CheckNumber, CardNumber, Amount
    123434      1010101010                                10
    655252                                15151515151   200
 
Kindly advise
Answers (2)
Next Recommended Forum