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