sqlxmlxml-parsingsqlxml

How to get value of xml column in SQL Server

How to get XML value of column ?

 SELECT PaymentMethodDetail.value('(/paymentMethodDetail/EFTPaymentDetails/AccountType/text())[0]','varchar(30)') as AccounType,* FROM paymentTable

<PaymentMethodDetail>
  <EFTPaymentDetails paymentMethodCode="">
    <AccountName>RVQNX BASAD</AccountName>
    <AccountType>S</AccountType>
  </EFTPaymentDetails>
</PaymentMethodDetail>

It is returning null value instead of S

Solution

  • Try this:

    CREATE TABLE paymentTable (
    PaymentMethodDetail XML
);

INSERT INTO paymentTable (PaymentMethodDetail)
VALUES
('<PaymentMethodDetail>
  <EFTPaymentDetails paymentMethodCode="">
    <AccountName>RVQNX BASAD</AccountName>
    <AccountType>S</AccountType>
  </EFTPaymentDetails>
</PaymentMethodDetail>')

SELECT PaymentMethodDetail.value('(/PaymentMethodDetail/EFTPaymentDetails/AccountType/text())[1]', 'varchar(30)') as AccountType, * 
FROM paymentTable

    change the index from [0] to [1] in your XPath expression