You have a table named Products.Product. The table has columns ProductID, Name, Size, and Category.
You have a variable named @XML with following XML value:

<Root>
<Category Name="Socks" />
<Category Name="Pants" />
<Category Name="Shirts" />

</Root>

You are tasked to write a query that lists the products in Products.Product that match the categories listed in the XML document. You need to write a query to accomplish the task. Which query should you write?

A.
SELECT p.ProductID, p.Name, p.Size, p.Category
FROM Production.Product p CROSS APPLY @XML.nodes(‘//Category’) as x(s)

B.
SELECT p.ProductID, p.Name, p.Size, p.Category
FROM Production.Product p OUTER APPLY @XML.nodes(‘//Category’) as x(s)

C.
WITH XMLTable AS (
SELECT s.value(‘@Name’,’varchar(20)’) as Category FROM @XML.nodes(‘//Category’) as x(s) )
SELECT p.ProductID, p.Name, p.Size, p.Category FROM Production.Product p
INNER JOIN XMLTable x ON p.Category = x.Category

D.
WITH XMLTable AS (
SELECT s.value(‘@Category’,’varchar(20)’) as Category FROM @XML.nodes(‘//Category’) as x(s) )
SELECT p.ProductID, p.Name, p.Size, p.Category FROM Production.Product p INNER JOIN XMLTable x ON p.Category = x.Category