You are the database developer for a Microsoft SQL Server 2008 database that contains tables named order and product. The tables have the following definitions:
CREATE TABLE [order]
(OrderID INT,
ProductID INT,
CustomerID INT,
OrderDate DATETIME);
CREATE TABLE product
(ProductID INT,
ProductName VARCHAR(100),
SalePrice money,
ManufacturerName VARCHAR(100));
You need to write a query that will extract a valid XML result set of all ordered products. You also need to ensure that the query conforms to the following schema:
<?xml version="1.0" encoding="utf-16"?>
<xsd:schema attributeFormDefault="unqualified" elementFormDefault="qualified" version="1.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="OrderedProducts">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ProductID" type="xsd:int" />
<xsd:element name="ProductName" type="xsd:string" />
<xsd:element name="SalePrice" type="xsd:decimal" />
<xsd:element name="ManufacturerName" type="xsd:string" />
<xsd:element name="OrderDate" type="xsd:dateTime" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Which SQL query should you use? (Choose Two – complete answers)
A. SELECT p.ProductID,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate
FROM product p
INNER JOIN[order] o ON p.ProductID = o.ProductID
FOR XML AUTO (‘OrderedProducts’);
B. SELECT p.ProductID,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate
FROM product p
INNER JOIN[order] o ON p.ProductID = o.ProductID
FOR XML AUTO;
C. SELECT p.ProductID,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate
FROM product p
INNER JOIN[order] o ON p.ProductID = o.ProductID
FOR XML PATH (‘OrderedProducts’);
D. SELECT ‘<OrderedProducts>’,p.ProductID,p.ProductName,p.SalePrice,p.ManufacturerName,
o.OrderDate,'</OrderedProducts>’
FROM product p
INNER JOIN[order] o ON p.ProductID = o.ProductID
FOR XML PATH;
E. SELECT 1 as Tag,0 as Parent,
p.ProductID as [OrderedProducts!1!ProductID!ELEMENT],
p.ProductName as [OrderedProducts!1!ProductName!ELEMENT],
p.SalePrice as [OrderedProducts!1!SalePrice!ELEMENT],
p.ManufacturerName as [OrderedProducts!1!ManufacturerName!ELEMENT],
o.OrderDate as [OrderedProducts!1!OrderDate!ELEMENT]
FROM product p
INNER JOIN[order] o ON p.ProductID = o.ProductID
FOR XML EXPLICIT;
F. SELECT 1 as Tag,0 as Parent,
p.ProductID as [OrderedProducts!1!ProductID!ELEMENT],
p.ProductName as [OrderedProducts!1!ProductName!ELEMENT],
p.SalePrice as [OrderedProducts!1!SalePrice!ELEMENT],
p.ManufacturerName as [OrderedProducts!1!ManufacturerName!ELEMENT],
o.OrderDate as [OrderedProducts!1!OrderDate!ELEMENT]
FROM product p
INNER JOIN[order] o ON p.ProductID = o.ProductID
FOR XML EXPLICIT (‘OrderedProducts’);
G. SELECT p.ProductID,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate
FROM product p
INNER JOIN [order] o ON p.ProductID = o.ProductID
FOR XML RAW;
H. SELECT p.ProductID,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate
FROM product p
INNER JOIN[order] o ON p.ProductID = o.ProductID
FOR XML RAW (‘OrderedProducts’);
