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 along with the LET clause and the count function. Return the count in XMLformat.
B.
Use an XML index. Aggregate the number of vacation days for each employee, and then return the totalcount in XML format.
C.
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.
D.
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.
Explanation:
XQuery is a language that can query structured or semi-structured XML data. With the xml data type support provided in the Database Engine, documents can be stored in a database and then queried by using XQuery.