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