You are a database developer. You plan to design a database solution by using SQL Server
2008. A database contains a table named Employee_Vacation. You are given an updated
list of employee vacations used. The list is in an XML formatted file. The extract of the XML
format is written in the following manner. <Company Name =”ABC Company Pvt Ltd”>
<EmployeeLeave> <Employee ID = “1” Name=”Jim Reeves” /> <Leaves> <Leave
Date=”2008-02-12″ /> <Leave Date=”2008-02-13″ /> <Leave Date=”2008-02-14″ />
</Leaves> </EmployeeLeave> You plan to import the data and update the
Employee_Vacation table. You design a query to calculate the number of vacation days
used by each employee. You need to ensure that vacation days are accurately counted for
each employee. What should you do?
A.
Use an XQuery expression to write the information from XML format to a SQL Server
table. Aggregate the number of vacation days from the tables, and then return the count in
XML format.
B.
Use the OPENXML function to convert XML data into a standard table format. Execute
the Transact-SQL count function on the vacation days, and then return the count in XML
format.
C.
Use an XQuery expression along with the LET clause and the count function. Return the
count in XML format.
D.
Use an XML index. Aggregate the number of vacation days for each employee, and then
return the total count in XML format.