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?
- FOR XML AUTO
- FOR XML RAW
- FOR XML AUTO, ROOT
- 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)
64 Comments. Leave new
Correct Answer is Option 3
Explanation :
suppose we have two tables tblorganization,tblemployee and for each organization there could be number of employees now if we apply the following join in these two tables
select org.orgname,emp.employeename,emp.employeeadress from employee as emp inner join organization as org on emp.employeeid=org.employeeid
for xml auto,root(‘organizations’)
this query will prompt us to a well organized xml where for each orgganization there will be employee names and addresses with root as organizations.
Thanks
Vishal Patwarhdhan
Indore(India)
the correct option is option 3.i.e.
Option 3 FOR XML AUTO, ROOT
as this allows our stream to be in well-formed XML.
India
The correct answer is Option 3.
3. FOR XML AUTO, ROOT
Reason: it returns all records with proper root node.Its an well formed xml.
Hiren Bavishi
India
The answer is :
3.FOR XML AUTO, ROOT
Sudeepta,
India.
The correct answer is #3 – FOR XML AUTO, ROOT
Country of residence: India
Correct answer is option 3
Thanks
Prasad
India
HI,
Correct answer #3
3.FOR XML AUTO, ROOT
Somnath Desai
India
Answer option 3
FOR XML AUTO, ROOT – it returns all records with proper Root node
Nikhildas
Cochin
INDIA
Ans : 3
(3) FOR XML AUTO, ROOT
Explanation
==========
it 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.
Mitesh Modi
(India)
Option # 3
Sreeram
India
Option 3. FOR XML AUTO, ROOT is the right code to append to the SQL statement 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.
(Sale, Nigeria)
Option 3 will result in the desired well-formed XML stream.
Country: United States
Option #3
USA
Mike Michalicek
Correct answer: 3
3.FOR XML AUTO, ROOT
Country: India
Answer is:
3.FOR XML AUTO, ROOT
Vinay
Pune, India.
The correct and obvious answer is option no #3)
FOR XML AUTO, ROOT
because
Every row will be populated in the same name as the first element and in well formed xml format manner as nicely explained.
Diljeet Kumari
Country : INDIA
Correct Answer – 3. FOR XML AUTO, ROOT
This will creat a well formed XML stream with the attributes nested and enclosed within opening and closing tags.
Ron A. Farris – USA
Anwer is #3
3. FOR XML AUTO, ROOT
all records will be returned under a proper root node and is well formed xml.
USA
Hi Sir,
Option 3 “FOR XML AUTO, ROOT”
is the correct answer as this will create a well formed XML stream
and the output is attribute-based and nests the results in a table
from the first field of the select list.
Regards,
P.Anish Shenoy,
INDIA, Bangalore, Karnataka
Correct answer is option #3
3.FOR XML AUTO, ROOT
Thanks.
Country – USA