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
3.FOR XML AUTO, ROOT
Gordon Kane
Allen TX
USA
The correct option is option #3
FOR XML AUTO, ROOT
Well formed XML contains a single root, so adding the ROOT clause after FOR XML AUTO satisfies this condition. Option 1 and 2 don’t have the ROOT statement so they are incorrect. RAW mode transforms each row in the query result set into an XML element that has the generic identifier so option 4 is incorrect. Option 3 gives us what we need.
Country of Residence: USA
Option 3 is correct
Robin Thomas
India
Option 3
Kevin Scharnhorst
United States
Correct option is 3) FOR XML AUTO, ROOT
Because this is going 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.
Dilip Kumar Jena
Country : India
Options 3 and 4 are correct. When we refer to the yesterdays post regarding XML it conveys that option 4: “FOR XML RAW, ROOT” creates a well formed XML. This tutorial explains that “OPTION 3.FOR XML AUTO, ROOT” is used to create a well formed xml.
Vaishali Jain
Hyderabad, India
The correct answer is option 3.
Ramdas
USA
Option 1 & 3
Explanation :
FOR XML RAW produces what we call attribute-based XML. FOR XML RAW essentially creates a flat representation of the data where each row returned becomes an element and the returned columns become the attributes of each element. FOR XML RAW also doesn’t interpret joins in any special way.
FOR XML AUTO produces attribute-based XML by default and can create nested results based on the tables in the query’s join clause.
So, using FOR XML AUTO and FOR XML AUTO ROOT, we can fulfill our requirement for creating a well formed, attribute based XML.
-Richa
Country – USA
Correct Answer is: Option 3
Thanks,
Basavaraj
Correct Answer is: Option 3
Thanks,
Basavaraj
India
Hi Pinal,
Challenge:
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
Correct Answer:
The correct choice is #3: FOR XML AUTO, ROOT
Explanation:
Adding a root element or root node in combination with the auto mode with create a well-formed XML stream.
Country:
United States
Thanks for the knowledge!
Regards,
Bill Pepping
Hi sir The answer is option 3: FOR XML AUTO
Thanks
Shekhar Bahuguna
Delhi India
Ans : 3
3. FOR XML AUTO, ROOT
–India
Answer : option (3) FOR XML AUTO, ROOT
country : India
Thanks
SachinKulshreshtha
Correct Answer is Option : 3 as
3.FOR XML AUTO, ROOT
because it returns all records with proper Root node and it is well-formed XML stream
Correct Answer is Option : 3 as
3.FOR XML AUTO, ROOT
because it returns all records with proper Root node and it is well-formed XML stream
Varinder Sandhu (India)
Pinal Dave,
The answer is option 3.
David
USA
3) 3rd option is correct
JD Varu
Ahmedabad – India
Correct Ans is: 3. FOR XML AUTO, ROOT
because The AUTO mode employs a heuristic to infer a simple, one element name-per-level hierarchy based on the lineage information and the order of the data in a SELECT statement, and ROOT will create root element for that xml
—
Haresh Ambaliya (INDIA)
Option 3
3.FOR XML AUTO, ROOT
Country : India