Return data in XML format in SQL Server

if you want to return you data in XML you can just simply put this line in the end after you query

 FOR XML RAW ('ABC'), ELEMENTS ;  

get field value form XMl in SQL

if you need to fetch field from XML in SQL you can use this query.Right now my XML look like this
i need to fetch the value of Expiry DateTime .


  DECLARE @Expiry_DateTime AS DATE
SELECT @Expiry_DateTime=XMLDATA.value('(/CustomerInfo//field//value//node())[3]', 'nvarchar(max)')
FROM KYC WHERE  UserName='test11FN'   and ID=4112532

XMLDATA is my column name where my XML store, 3 represent index which value you want to get , value represent this   <value>2017-05-31</value>. if i need to get the label value i just replace this query with  XMLDATA.value('(/CustomerInfo//field//label//node())[3]', 'nvarchar(max)')



<CustomerInfo>
  <field>
    <label>CustomerId</label>
    <type>hidden</type>
    <value>3323</value>
  </field>
  <field>
    <label>ID Number</label>
    <type>text</type>
    <value>23</value>
  </field>
  <field>
    <label>Expiry DateTime</label>
    <type>text</type>
    <value>2017-05-31</value>
  </field>
  <field>
    <label>Customer Name</label>
    <type>hidden</type>
    <value>test11FN</value>
  </field>
  <field>
    <label>Customer Email</label>
    <type>hidden</type>
    <value>testa11@mailinator.com</value>
  </field>
  <field>
    <label>Picture Image</label>
    <type>image</type>
    <value>https://www.abccom/public/photoid/3323/</value>
  </field>
</CustomerInfo>