What stored procedure you create to create a stored procedure that will return a list of sample chapters from the Samples table?

You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. The network contains a database server named Certkiller -DB01 that hosts a database named CK_Books. The CK_Books database has a table named SampleChapters that is used to store sample chapters from books published by Domain.com. The schema for the SampleChapters table is shown in the following exhibit:

The text for the sample chapters are stored in a column named SampleText. The SampleText column is defined as an xml data type column. The XML schema for the SampleText column is shown below:
<SampleChapter>
<BookTitle>title of book</BookTitle>
<Author>author of book</Author>
<ChapterText>
<ChapterTitle>title of chapter</ ChapterTitle >
<Intro>introduction or synopsis</Intro>
<Topic Title=”topic title”>topic text</Topic>
<Topic Title=”next topic title”>next topic text</Topic> </ChapterText>
</SampleChapter>
You need to create a stored procedure that will return a list of sample chapters from the Samples table. The returned data must include the book title and the sample chapter title but must not return any XML tags.

What stored procedure you create?

A.
SELECT SampleText.nodes(‘SampleChapterBookTitle’, ‘varchar’), SampleText.nodes(‘SampleChapterChapterTextChapterTitle’, ‘varchar’) FROM Samples

B.
SELECT SampleText.query(‘SampleChapterBookTitle’), SampleText.query(‘SampleChapterChapterTextChapterTitle’) FROM Samples

C.
SELECT SampleText.value(‘SampleChapterBookTitle[1]’, ‘varchar’), SampleText.value(‘SampleChapterChapterTextChapterTitle[1]’, ‘varchar’) FROM Samples

D.
SELECT SampleText.query (‘child:BookTitle’),
SampleText.query (‘child:child:ChapterTitle’)
FROM Samples

Explanation:
You must use the value method to query XML data in an xml data type column. The correct syntax for the value method is column_name.value(‘XQuery’, ‘data_type’). The ‘[1]’ in the XQuery indicates that a single value is being returned.
Incorrect Answers:
A: The nodes method is used to create relational data from an xml column. It is not used to return scalar values.
B, D: The query method returns the XML tags together with the requested data.