Tuesday, September 26, 2017

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>

No comments:

Post a Comment