SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Using Root With Auto XML Mode – Day 32 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.

XML Path Mode

The XML Raw and Auto modes are great for displaying data as all attributes or all elements – but not both at once. If you want your XML stream to have some of its data shown in attributes and some shown as elements, then you can use the XML Path mode.

The following Select statement shows us all locations and the employees who work in each location. There are 13 matching records in this inner join between the Location and Employee tables. The Location table is aliased “Loc”, and the Employee table is aliased “Emp.”  Recall that in SQL the AS keyword is recommended when aliasing tables, but it is optional in this figure we alias the Location table as Loc and the Employee table as Emp while omitting the optional AS keyword.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Using Root With Auto XML Mode - Day 32 of 35 j2p_32_1

Let’s stream this query in an XML mode, called Path. Much like Raw and Auto, each top level element has tag named “row” and by default there is no root element.  However, unlike the previous modes we’ve seen, the Path mode defaults to putting all its data in elements.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Using Root With Auto XML Mode - Day 32 of 35 j2p_32_2

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Using Root With Auto XML Mode - Day 32 of 35 j2p_32_3

Now we’ll itemize the fields in our query. The result appears much the same. We see “row” as the default name of the opening and closing tags.  We also see nested within each “row”, elements for each of the three fields we itemized in our query (City, FirstName, LastName).

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Using Root With Auto XML Mode - Day 32 of 35 j2p_32_4

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Using Root With Auto XML Mode - Day 32 of 35 j2p_32_5

Our next goal is to modify our SQL query, so that our XML result will include an <Employees> root tag.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Using Root With Auto XML Mode - Day 32 of 35 j2p_32_6

We simply need to modify our FOR XML Path clause to include ROOT(‘Employees’). In the result, we see that we now have our root, which is called “Employees”

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Using Root With Auto XML Mode - Day 32 of 35 j2p_32_7

The root element <Employees> is now visible in our XML Path stream. Let’s change our current stream to have each top level element tagged as <Employee> instead of <row>. Our stream is looking much more readable and organized.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Using Root With Auto XML Mode - Day 32 of 35 j2p_32_8

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Using Root With Auto XML Mode - Day 32 of 35 j2p_32_9

Custom Attributes

If you are using an XML Path stream, then by default all of the values will be shown as elements. However, you can pick one or more of your elements to instead be shown as an attribute(s). Use the [@Fieldname] syntax to do this.

Our next goal is to move City inside the <Employee> tag as an attribute (see figure below).  The [@Fieldname] construct will help us accomplish this.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Using Root With Auto XML Mode - Day 32 of 35 j2p_32_10

To make our code reflect this change, all we need to do is alias the City field as @City.  (For illustrative purposes, we will alias the attribute as “CityName” instead of “City.”  Just as when we have previously aliased fields and tables in our SQL queries, with our XML queries we also have the freedom to alias the field City using any name we choose.) One easy way to think of this code syntax is to remember that @ (a.k.a., the “at” sign) goes with “ATtributes”.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Using Root With Auto XML Mode - Day 32 of 35 j2p_32_11

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Using Root With Auto XML Mode - Day 32 of 35 j2p_32_12

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 32

You need to generate the following XML document from your CurrentProducts table:

<ProductExport>
<Product Price=”99″>Product1</Product>
<Product Price=”199″>Product2</Product>
<Product Price=”299″>Product3</Product>
<Product Price=”399″>Product4</Product>
</ProductExport>

Which query should you use?

  1. SELECT Price, ProductName
    FROM CurrentProducts AS ProductExport
    FOR XML PATH(‘Product’)
  2. SELECT Price, ProductName
    FROM CurrentProducts
    FOR XML AUTO, ROOT(‘ProductExport’)
  3. SELECT Price [@Price], ProductName AS [*] FROM CurrentProducts AS ProductExport
    FOR XML AUTO, ELEMENTS
  4. SELECT Price [@Price], ProductName AS [*] FROM CurrentProducts
    FOR XML PATH(‘Product’), ROOT(‘ProductExport’)

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)

,
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Using Root With Auto XML Mode – Day 31 of 35
Next Post
SQLAuthority News – Programming & Development For Microsoft SQL Server 2008

Related Posts

53 Comments. Leave new

  • Correct answer is option 4.

    This query includes xml clause for root element (ProductExport), and each xml element(Product) within it. It also has @Price attribute defined for Price column which matches all the criteria defined for generating the xml displayed

    Vaishali Jain
    Country of Residence: Hyderabad, India.

    Reply
  • Correct Ans is: #4

    SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts
    FOR XML PATH(‘Product’), ROOT(‘ProductExport’)

    Because, we want path as product and root element as ProductExport.
    In alternet querys there is not path name, root name specified.


    Haresh Ambaliya (INDIA)

    Reply
  • Correct option for the above question is Option 4 .

    4. SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts
    FOR XML PATH(‘Product’), ROOT(‘ProductExport’)

    Why ?
    because provides the output that was requested. This is the correct choice.

    Option #1 is aliasing the table CurrentProductas as ProductExport. This is not going give a result stream with a root node of ProductExport. Hence INVALID

    Option #2 Gives a root node of ProductExport, but doesn’t provide the Product tag.Hence invalid.

    Option #3 It doesn’t provide a root node. Hence Invalid

    DILIP KUMAR JENA
    Country: INIDA

    Reply
  • The answer is # 4.

    SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts
    FOR XML PATH(‘Product’), ROOT(‘ProductExport’)

    The desired output is

    Product1
    Product2
    Product3
    Product4

    so you need

    [@Price] to get price to show up as an attribute
    For the tag to appear as “Product” and not row you need the Path(‘Product’)
    For the root level tag to read “ProductExport” you need ROOT(‘ProductExport’)

    #4 is the only one that has all these pieces that will get your xml to appear as you requested

    Deb -USA

    Reply
  • the correct option is 4

    I’m from INDIA

    Reply
  • Correct Option is : 4

    As per requirement Root element ProductExport and have to set attribute product price on product element.

    Option 4 is the only answer to get it.

    Thanks,

    Reply
  • Nakul Vachhrajani
    September 2, 2011 10:36 am

    Correct answer is #4.
    Reasons are very well explained in the article.

    Country of residence: India

    Reply
  • Sreejith Vijayan
    September 2, 2011 12:47 pm

    Correct answer is #4.
    SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts
    FOR XML PATH(‘Product’), ROOT(‘ProductExport’)

    Sreejith Vijayan
    Kerala
    India

    Reply
  • Option 4

    4.SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts
    FOR XML PATH(‘Product’), ROOT(‘ProductExport’)

    Country:India

    Reply
  • Rajesh Mohanrangan
    September 2, 2011 4:43 pm

    Correct Answer is # 4.

    4. SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts
    FOR XML PATH(‘Product’), ROOT(‘ProductExport’)

    Regards
    Rajesh
    From india

    Reply
  • A.) 4. SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts
    FOR XML PATH(‘Product’), ROOT(‘ProductExport’)

    Winner from USA: Kevin Scharnhorst

    Winner from India: Geetanjali Agarwal

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

    Reply
  • Option 4 is Correct.
    Country
    Pakistan

    Reply
  • Correct Answer is 4.

    4. SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts
    FOR XML PATH(‘Product’), ROOT(‘ProductExport’)

    ROOT(‘ProductExport’) will add “ProductExport” root tag and FOR XML PATH(‘Product’) will replace “Row” tag with “Product” for each row.

    @Price will be added as attribute and ProductName value will placed directly in “Product” tag.

    Regards
    Mahesh

    From India.

    Reply

Leave a Reply

Menu