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
Hi,
Option 3 FOR XML AUTO, ROOT is correct as this allows our stream to be in well-formed XML.
Thanks
Sudhir Chawla
New Delhi, India
Correct answer is option #3
3.FOR XML AUTO, ROOT
Thanks.
Country – India
Option 3 and 4 are th corre`ct answer. Using the ROOT keyword in combination with the Auto mode produces the same result as it does with the Raw mode:
Uday Bhoopalam
USA
Correct Answer Option : 3
3.FOR XML AUTO, ROOT
Option 1 : FOR XML AUTO – It arrange the tags withour Root Node
Option 2 : FOR XML RAW – It simples returns values with Row Tags
Option 3 : FOR XML AUTO ROOT – it returns all records with proper Root node
Option 4 : FOR XML RAW ROOT – It returns records in Row tag wirh Root node
Chennai, Tamilnadu, India
Correct Answer is # 3 & 4.
3.FOR XML AUTO, ROOT
4.FOR XML RAW, ROOT
Because using the ROOT keyword with RAW mode produces the same result as with AUTO mode.
Using root element (also called root node), 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.
Gopalakrishnan Arthanarisamy
Unisys, Bangalore, India.
Correct answer is No 3:
FOR XML AUTO, ROOT
This is because using the ROOT keyword in combination with the Auto mode produces a well-formed XML
Rene Castro
El Salvador
I believe that the correct answer is #3
FOR XML AUTO, ROOT
Also, this article demonstrated nice nested output especially when the result was ordered.
I am from USA
HI,
Correct answer is option #3
3.FOR XML AUTO, ROOT
I am from India.
Question 31
Ans : #3—-FOR XML AUTO, ROOT
Chennai, INDIA
3) is the correct answer
This allows the stream to be in well-formed XML.
Leo Pius
USA
Correct Answer :3)FOR XML AUTO, ROOT
krishan kumar mishra
India
The correct answer is Option 3.
FOR XML AUTO, ROOT
Pranav
Hyderabad
Answer is Option# :: 3
ANS is 3.FOR XML AUTO, ROOT
FOR XML AUTO – It arrange the tags without root node its not an well formed xml.
FOR XML RAW – It simples returns values with Row Tags and it has no root node too.
FOR XML AUTO, ROOT – it returns all records with proper root node.Its an well formed xml.
FOR XML RAW, ROOT – It returns records in row tag with root node.
Partha,
India
Correct Option is 3, i.e.
3. FOR XML AUTO, ROOT
Reason:
The above option offers organizes the result output in a more efficient and readable manner by grouping the result from a table’s first field in a select statement.
Regards,
Geetika Bhambri
Ahmedabad(Gujarat)-INDIA
Correct Answer is:
3)FOR XML AUTO, ROOT
City: Baroda
Country: INDIA
Hi,
Option 3 and 4 are close to Answer but as in RAW user in option 4 it says “RAW mode generates single elements, which are named row, for each row returned.”(From MSDN). And if i go in details as describe here ” 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”
So Option 3 is the correct Answer “FOR XML AUTO, ROOT”
Thanks….
Rajneesh Verma
(INDIA)
Option 3 is correct Answer.
Description:
When we use AUTO, it will include an attribute and a node name for child elements just after the root node. But when we include RAW then all child elements named as ROW.
Vinay Kumar
Jaipur, India
Correct answer should be option #3.
3. FOR XML AUTO, ROOT
New Delhi
India
option 1 & 3.
if we dont want to form it in root then we can use 1 and if we want it to be formed in root then we can use 3.
india
ghanshyam