XML questions and Query Samples

Question 1

Based on the XML in the right half of the following figure, answer the questions in the left side of the figure.

Answer 1

A1: 3
A2: 3
A3: 1
A4: Invoice 43861
A5: SalesOrderHistory
A6: Invoice 43862

Question 2

Based on the XML in the right half of Figure 1.18, answer the questions in the left side of the figure.

Answer 2

A1: Manic Monday
A2: 13160
A3: Red Red Wine by Neil Diamonde / Pretty Woman by Roy Orbison
A4: Manic Mondy
A5: Music
A6: 2

Question 3

Create an XML stream using the RAW option to get the ClassID and ClassName fields from the MgmtTraining table. Each top-level element should be named after the table. When you’re done, your screen should resemble Figure 1.43.

Answer 3

SELECT ClassID, ClassName
FROM MgmtTraining
FOR XML RAW(‘MgmtTraining’)

Question 4

Create an XML stream using the RAW option to get the GrantName and Amount fields from the Grant table. Make sure you have a root element named <root> and each top-level element should be named after the table. All the data should be in attributes inside the top-level element. When you are done, your result should resemble Figure 1.44.</root>

Answer 4

SELECT GrantName, Amount
FROM [Grant]
FOR XML RAW(‘Grant’), ROOT

Question 5

Create an XML stream using the RAW option to get all fields from the contractor table. Make sure you have a root element named <root> and each top-level element should be named after the table. The data should be contained in sub-elements of the top element. When you are done, your result should resemble Figure 1.45.</root>

Answer 5

SELECT *
FROM Contractor
FOR XML RAW(‘Contractor’), ROOT, ELEMENTS

Question 6

Create an XML stream using the RAW option to get all fields from the PayRates table. Make sure you have a root element named <root> and each top-level element should be named <pr>. The data should be contained in sub-elements of the top element. Elements must be present even if their value is null. When you are done, your result should resemble Figure 1.46.</pr></root>

Answer 6

SELECT * FROM Payrates
FOR XML RAW(‘PR’), ROOT, ELEMENTS XSINIL

Question 7

What do text files, XML streams, and SQL databases all have in common?

a. They all run only on Windows servers.
b. They all contain data.
c. They all contain metadata.

Answer 7

Because Text files and XML streams do not run on Windows servers, (a) is incorrect. Text files do not contain metadata, therefore (c) is incorrect. Because text files, XML streams and SQL databases all contain data, (b) is the correct answer.

Question 8

Your root tag is named in your well-formed XML stream. How should the ending root tag be named?

a. < / Joes2Pros >

b < \ Joes2Pros >

c <–Joes2Pros>

d. < Joes2Pros / >
e. < Joes2Pros \ >

Answer 8

A valid opening tag is between < > and a valid closing tag will always be betweenTherefore (a) is the correct answer.

Question 9

What do you call data inside of a tag like the example seen below? <team></team>

a. Data fragment
b. XML fragment
c. Element
d. Attribute

Answer 9

The data contained is data, not a data fragment, so (a) is an incorrect answer. An XML fragment is an XML string that is not well formed, so (b) is incorrect. An Element describes what it contains and is not the content, making (c) an incorrect answer. An attribute cannot contain other elements and provides information about the element and will always be in quotation marks, so (d) is the correct answer.

Question 10

RAW is the only XML mode.

a. True
b. False

Answer 10

XML RAW is only one of several modes including XML AUTO and XML Path. The answer is (b), False.

Question 11

RAW automatically adds the root element tag in SQL Server.

a. True
b. False

Answer 11

RAW is the simplest mode where data is all considered to be at the same level with no nesting. The FOR XML RAW command by default does not create a root. The answer is (b), False.

Question 12

If you don’t specify any option, then XML RAW will have your data streamed in as:

a. Element text.
b. Attributes.

Answer 12

XML RAW will store its data as attributes by default, so (b) is the correct answer.

Question 13

Without XSINIL, what happens to null values from your result set?

a. They error out since XSINIL does not allow nulls.

b They appear as empty tags.

c. No tags are present for null values.

Answer 13

XSINIL allows you to force a tag(s) to be present even if the underlying data is NULL, so (a) and (b) are incorrect. Without XSINIL no tags are present for null values, making (c) the correct answer.

Question 14

You are using FOR XML RAW to get your relational data. After testing this, you notice that not all items from the query appear in the XML stream. You notice that this is happening when fields have a NULL value. Only those employees who have values for all elements appear. You need to modify your T-SQL statement so that all employees’ tags appear in the XML document. What should you do?

a. Add a HAVING clause to the query.
b. Remove the RAW option and let SQL pick the default.
c. Add the XSINIL argument to the ELEMENTS option in the query.
d. Add the replace value to the clause of the query.

Answer 14

To force a tag(s) to be present even if the underlying data is NULL the XSINIL must be added to the element option in the query. This means (c) is the correct answer.