Calculating Computed Column in Sql Server For XML data Using Function
Lets work on 2 operations in this post. 1.Using Function in Sql Server 2.Calculating Computed Column in Sql Server For XML data Using Function I have used AdventureWorks Database in this example. Table Used := FROM HumanResources.JobCandidate Table contains Resume in XML format. First get candidates into new table hr.CandidateNames. CREATE Schema hr SELECT TOP 10 JobCandidateID As CandidateID, Resume As JobResume INTO hr.CandidateNames FROM HumanResources.JobCandidate Now Create Function to parse through XML nodes. Function will return concatenated FullName for each candidate. CREATE Function hr.FullName(@name XML) RETURNS NVARCHAR(60) AS BEGIN RETURN @name.value('declare namespace ns= "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; concat((/ns:Resume/ns:Name/ns:Name.First)[1], " ", (/ns:Resume/ns:Name/ns:Name.Last)[1])','nvarchar(60)') END Now Just Query Function to get Result as