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 shown below:-

SELECT CandidateID, hr.FullName(JobResume) AS FullName FROM hr.CandidateNames;