SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.Employee (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(80)
);
CREATE TABLE dbo.SpecificObjective (
    ObjectiveId INT IDENTITY(1,1) PRIMARY KEY,
    AppraiseeId INT FOREIGN KEY REFERENCES Employee(ID),
    Description NVARCHAR(80),
    Objective NVARCHAR(80),
    Expectation NVARCHAR(80),
    Measure NVARCHAR(80),
    Comment NVARCHAR(80)
);
CREATE TABLE dbo.Competence (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    AppraiseeId INT FOREIGN KEY REFERENCES Employee(ID),
    CompetenceAreaDetailsId INT,
    CompetenceAreaDetailsDescription NVARCHAR(80),
    CompetenceAreaDetailsExpectation NVARCHAR(80)
);
INSERT INTO dbo.Employee (FullName) VALUES ('John Doe');

INSERT INTO dbo.SpecificObjective (AppraiseeId, Description, Objective, Expectation, Measure, Comment) VALUES
(1,'Description 1','Objective 1','Expectation 1','Measure 1','Comment 1'),
(1,'Description 2','Objective 2','Expectation 2','Measure 2','Comment 2'),
(1,'Description 3','Objective 3','Expectation 3','Measure 3','Comment 3'),
(1,'Description 4','Objective 4','Expectation 4','Measure 4','Comment 4');

INSERT INTO dbo.Competence (AppraiseeId, CompetenceAreaDetailsId, CompetenceAreaDetailsDescription, CompetenceAreaDetailsExpectation) VALUES
(1,1,'Skill Description','Skill Expectation'),
(1,2,'Knowledge Description','Knowledge Expectation'),
(1,3,'Behaviour Description','Behaviour Expectation');
8 rows affected

CREATE VIEW PlanningReport
AS
SELECT 
 emp.ID AS EmpID,
 emp.FullName AS EmpFullName,
 (
       SELECT
         obj.ObjectiveId AS "@ID",
         obj.Objective AS "Objective",
         obj.Expectation AS "Expectation",
         obj.Measure AS "Measure",
         obj.Comment AS "Comment"
        FROM dbo.SpecificObjective AS obj
        WHERE obj.AppraiseeId = emp.ID
        FOR XML PATH('Objective'), ROOT('Objectives'), TYPE
 ) AS Objectives,
 (
        SELECT
        comp.ID AS "@ID",
        comp.CompetenceAreaDetailsDescription,
        comp.CompetenceAreaDetailsExpectation
        FROM dbo.Competence AS comp
        WHERE comp.AppraiseeId = emp.ID
        FOR XML PATH('Competence'), ROOT('Competences'), TYPE
 ) AS Competences
FROM dbo.Employee emp;
select * 
from PlanningReport 
where EmpID = 1;


EmpID
EmpFullName
Objectives
Competences


1
John Doe
<Objectives><Objective ID="1"><Objective>Objective 1</Objective><Expectation>Expectation 1</Expectation><Measure>Measure 1</Measure><Comment>Comment 1</Comment></Objective><Objective ID="2"><Objective>Objective 2</Objective><Expectation>Expectation 2</Expectation><Measure>Measure 2</Measure><Comment>Comment 2</Comment></Objective><Objective ID="3"><Objective>Objective 3</Objective><Expectation>Expectation 3</Expectation><Measure>Measure 3</Measure><Comment>Comment 3</Comment></Objective><Objective ID="4"><Objective>Objective 4</Objective><Expectation>Expectation 4</Expectation><Measure>Measure 4</Measure><Comment>Comment 4</Comment></Objective></Objectives>
<Competences><Competence ID="1"><CompetenceAreaDetailsDescription>Skill Description</CompetenceAreaDetailsDescription><CompetenceAreaDetailsExpectation>Skill Expectation</CompetenceAreaDetailsExpectation></Competence><Competence ID="2"><CompetenceAreaDetailsDescription>Knowledge Description</CompetenceAreaDetailsDescription><CompetenceAreaDetailsExpectation>Knowledge Expectation</CompetenceAreaDetailsExpectation></Competence><Competence ID="3"><CompetenceAreaDetailsDescription>Behaviour Description</CompetenceAreaDetailsDescription><CompetenceAreaDetailsExpectation>Behaviour Expectation</CompetenceAreaDetailsExpectation></Competence></Competences>