by

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 http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. 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

    Reply

  3. 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

    Reply

  4. 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

    Reply

  5. 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.

    Reply

  6. 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

    Reply

  7. 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

    Reply

  14. 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

    Reply

  15. 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

    Reply

  17. 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)

    Reply

  18. 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

    Reply

  20. 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

    Reply

  21. 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)

    Reply

  23. 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

    Reply

  29. 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)

    Reply

  31. 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)

    Reply

  32. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  37. 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

    Reply

  40. 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

    Reply

  43. 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

    Reply

  46. 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

    Reply

  47. 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

    Reply

  49. 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

    Reply

  52. 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

    Reply

  60. 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)

    Reply

  66. Pingback: SQL SERVER – A Guide to Integrating SQL Server with XML, C#, and PowerShell – Book Available for SQL Server Certification Journey to SQLAuthority

  67. Pingback: SQL SERVER – Understanding XML – Contest Win Joes 2 Pros Combo (USD 198) – Day 5 of 5 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out / Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out / Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s