You work for a company that provides marketing data to other companies.
You have the following TransactSQL
statement:
DECLARE @CustomerDemographics XML
SET @CustomerDemographics=N’
<CustomerDemographics>
<Customer CustomerID=”1″ Age=”21″ Education=”High School”>
<IsCoffeeDrinker>0</IsCoffeeDrinker>
</Customer>
<Customer CustomerID=”2″ Age=”27″ Education=”College”>
<IsCoffeeDrinker>1</IsCoffeeDrinker>
<IsFriendly>1</IsFriendly>
</Customer>
<Customer CustomerID=”3″ Age=”35″ Education=”Unknown”>

<IsCoffeeDrinker>1</IsCoffeeDrinker>
<IsFriendly>1</IsFriendly>
</Customer>
</CustomerDemographics>’
DECLARE @OutputAgeOfCoffeeDrinkers XML
SET @OutputAgeOfCoffeeDrinkers = @CustomerDemographics.query(‘
for $output in /child::CustomerDemographics/child::Customer[
( child::IsCoffeeDrinker[1] cast as xs:boolean )]
return
<CoffeeDrinkingCustomer>
{ $output/attribute::Age \}
</CoffeeDrinkingCustomer>’)
SELECT @OutputAgeOfCoffeeDrinkers
You need to determine the result of the query.
What result should you expect?

You have a table named Stores that has an XML column named OpenHours. This column contains
the opening and closing times.
<hours dayofWeek=“Monday” open=”8:00” closed=”18:00” />
<hours dayofWeek=“Tuesday” open=”8:00” closed=”18:00” />
…
<hours dayofWeek=“Saturday” open=”8:00” closed=”18:00” />
You need to write a query that returns a list of stores and their opening time for a specified day.
Which code segment should you use?

You have the following XML document that contains Product information.
DECLARE @prodList xml =’
<ProductList xmlns=”urn:Wide_World_Importers/schemas/Products”>
<Product Name=”Product1″ Category=”Food” Price=”12.3″ />
<Product Name=”Product2″ Category=”Drink” Price=”1.2″ />
<Product Name=”Product3″ Category=”Food” Price=”5.1″ />
…
</ProductList>’;
You need to return a list of products that contains the Product Name, Category, and Price of each
product.
Which query should you use?

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?

Your company exchanges information with other companies by using XML and Web services. Your
manager asks you to remove a schema collection that is no longer used. Before dropping the
schema,
you should confirm that it is no longer in use.
You need to use a catalog view to determine if the schema collection is being used. Which catalog
view
should you use?

You have an XML schema that you must use to validate XML data in your database. You need to
store this XML schem
a. Which code segment should you use?

You have a table named Customers that has an XML column named CustomerDat
a. There are
currently no indexes on the table.
You use the following WHERE clause in a query:
WHERE CustomerData.exist (‘/CustomerDemographic/@Age[.>=”21″]’) = 1
You need to create indexes for the query.
Which TransactSQL
statements should you use?

You need to capture the execution plan for a query. Which statement should you use?

You are troubleshooting query performance on SQL Server 2008. You are tasked to create an
estimated execution plan by using TransactSQL.
You should be able to view the plan graphically in SQL
Server Management Studio. You need to ensure that the execution plan can be saved as a .sqlplan
file.
Which TransactSQL
setting should you use?

You are troubleshooting query performance on SQL Server 2008. You are tasked to capture a
graphical execution plan. You need to save the plan to a file that can be used by SQL Server
Management
Studio to display the graphical execution plan.
Which file extension should you use?