You are a database developer. You plan to create a database by using SQL Server 2008.
The database has a table named Employees. The table contains records of employees and their managers.
The table includes the EmployeelD and ManagerlD fields. The EmployeelD values are unique. The value in the ManagerlD field is the employee ID of the employee’s manager.
A Web site requires XML formatted output of all managers and employees to be displayed as a tree diagram.
You need to produce the required output by querying the database without using system stored procedures.
What should you do?
A. Create a table-valued function by using the hierarchyid data type.
B. Create a scalar-valued function by using the FOR XML PATH clause and the TYPE directive.
C. Create a table-valued function by using a common table expression (CTE).
D. Create a scalar-valued function by using the OPENXML() function.
Correct Answer: B
Explanation/Reference:
You should create a scalar-valued function by using the FOR XML PATH clause and the TYPE directive. All other options don’t produce XML at all 🙂
Here is an example:
CREATE TABLE dbo.Employees
(
EmployeeID int NOT NULL PRIMARY KEY, FirstName nvarchar(30) NOT NULL,
FirstName nvarchar(30) NOT NULL, LastName nvarchar(40) NOT NULL,
LastName nvarchar(40) NOT NULL, Title nvarchar(50) NOT NULL,
Title nvarchar(50) NOT NULL, ManagerID int NULL,
ManagerID int NULL, CONSTRAINT FK_Employees FOREIGN KEY (ManagerID)
REFERENCES dbo.Employees (EmployeeID)
);
GO
INSERT INTO dbo.Employees VALUES
(1, N’Ken’, N’Sánchez’, N’Chief Executive Officer’,NULL)
,(2, N’Brian’, N’Welcker’, N’Vice President of Sales’,1)
,(3, N’Stephen’, N’Jiang’, N’North American Sales Manager’,2)
,(4, N’Michael’, N’Blythe’, N’Sales Representative’,3)
,(5, N’Linda’, N’Mitchell’, N’Sales Representative’,3)
,(6, N’Syed’, N’Abbas’, N’Pacific Sales Manager’,2)
,(7, N’Lynn’, N’Tsoflias’, N’Sales Representative’,6)
,(8, N’David’,N’Bradley’, N’Marketing Manager’, 7)
,(9, N’Mary’, N’Gibson’, N’Marketing Specialist’, 7);
GO
CREATE FUNCTION GetSubordinates(@ManagerID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
RETURN (
SELECT FirstName AS "@FirstName"
,LastName AS "@LastName"
,Title AS "@Title"
,dbo.GetSubordinates(EmployeeID)
FROM dbo.Employees
WHERE ManagerID = @ManagerID
FOR XML PATH(‘Employee’), TYPE)
END
GO
SELECT FirstName AS "@FirstName"
,LastName AS "@LastName"
,Title AS "@Title"
,dbo.GetSubordinates(EmployeeID)
FROM Employees
WHERE ManagerID IS NULL
FOR XML PATH(‘Employee’), ROOT (‘EmployeeTree’)
GO
The query returns the following:
<EmployeeTree>
<Employee FirstName="Ken" LastName="Sánchez" Title="Chief Executive Officer">
<Employee FirstName="Brian" LastName="Welcker" Title="Vice President of Sales">
<Employee FirstName="Stephen" LastName="Jiang" Title="North American Sales Manager">
<Employee FirstName="Michael" LastName="Blythe" Title="Sales Representative" />
<Employee FirstName="Linda" LastName="Mitchell" Title="Sales Representative" />
</Employee>
<Employee FirstName="Syed" LastName="Abbas" Title="Pacific Sales Manager">
<Employee FirstName="Lynn" LastName="Tsoflias" Title="Sales Representative">
<Employee FirstName="David" LastName="Bradley" Title="Marketing Manager" />
<Employee FirstName="Mary" LastName="Gibson" Title="Marketing Specialist" />
</Employee>
</Employee>
</Employee>
</Employee>
</EmployeeTree>
