SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Using Root With Auto XML Mode – Day 31 of 35

Answer simple quiz at the end of the blog post and –

Every day one winner from India will get Joes 2 Pros Volume 5.

Every day one winner from United States will get Joes 2 Pros Volume 5.

Using Root With Auto XML Mode

Now let’s add a root element (also called root node), so that our stream will be well-formed XML. Using the ROOT keyword in combination with the Auto mode produces the same result as it does with the Raw mode:  your XML stream will contain a root (named <root> by default). To specify a name for the root, put this name in the parentheses inside single quotes right after the ROOT keyword in your code.

Our XML result appears the same as in the prior result, except that now all of our data is enclosed within the opening and closing tags named <root>.

Let’s take our same code and give the root node a more meaningful name. To specify a name for the root, put the name inside single quotes, within parentheses, right after the ROOT keyword in your code. We’ll name it <JproCo> after the database which both of our tables come from.

Sorting Nested Elements

XML offers a great deal of flexibility in how you can choose to organize your result output. For example, since eight JProCo employees are listed under Seattle, then there is really no need to see Seattle listed multiple times. By sorting on the higher level element, you can put all related child elements under the same parent. This way you don’t have to repeatedly list the parent element. Let’s combine our results together in a more efficient and readable manner by grouping the Seattle employees together. The figure below shows Seattle employees are scatterd and not clusterd in our XML stream.

Instead of grouping the results by the natural sort of the table (i.e., by EmployeeID), we would prefer to have the the results grouped by city.  An ORDER BY clause will help achieve our goal.

This result is very readable. We now see each city (Boston, Seattle, and Spokane) listed once along with the employees working in each one.

Each of the JProCo employees from Boston is listed as a child element below the top level element <Location> which has a City attribute with a value of “Boston”. We similarly see the Seattle employees nested beneath the Location with a City attribute of “Seattle”.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLInteropChapter1.0Setup.sql script from Volume 5.

Question 31

You have a query which joins tables. You want to create a well-formed XML stream, which is attribute-based and nests the results in the table from the first field of the select list. Which code do you append to the SQL statement?

1. FOR XML AUTO
2. FOR XML RAW
3. FOR XML AUTO, ROOT
4. FOR XML RAW, ROOT

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 5.
Every day one winner from United States will get Joes 2 Pros Volume 5.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)