Showing posts with label Fetching value from XML. Show all posts
Showing posts with label Fetching value from XML. Show all posts

Tuesday, November 7, 2017

Retreiving field value in XML using cross apply

1)  First we will cast the field data in a table to type xml from type text

2) you need to use with operator if namespace is defined ;WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/Psv.Domain.Models.PexaNoa')

3) consider ContentXML as 

     '<Name>

         <First>Jacob</First>

         <Last>Sebastian</Last>

     </Name>'

Begin

 SELECT ContentXML INTO #tab12
FROM 
 (Select  
 Cast(data as xml) AS ContentXML
 from ABC
 ) a



 ;WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org')

 SELECT t.value('(./First)[1]','NVARCHAR(MAX)') as FirstName
  FROM #tab12
Cross Apply ContentXML.nodes('/Name') x(t) 

 Drop table #tab12
End