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.

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.

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

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

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”

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.

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.

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

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

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

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

  1. Correct answer seems to be No. 4, because we need to build an XML document whose root says “ProductoExport” and each top element says “Product”, plus an element (Price). What is being difficult for me to buy is the “ProductName AS [*]” piece.
    Rene Castro
    El Salvador

    Like

  2. Hi,

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

    Thanks

    Sudhir Chawla
    New Delhi, INDIA

    Like

  3. Correct Answer is # 4.

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

    Here,

    => Root Element
    => Top Level Element
    => Element
    @Price is selected as Attribute.

    Root

    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.

    Path mode defaults to putting data in Elements.

    If you are using an XML Path stream, then by default all of the values will be shown as elements.

    Gopalakrishnan Arthanarisamy
    Unisys, Bangalore, India.

    Like

  4. Correct Answer is # 4.

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

    Here,

    – Root Element
    – Top Level Element
    – Element
    @Price is selected as Attribute.

    Root

    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.

    Path mode defaults to putting data in Elements.

    If you are using an XML Path stream, then by default all of the values will be shown as elements.

    Gopalakrishnan Arthanarisamy
    Unisys, Bangalore, India.

    Like

  5. Correct Answer is # 4.

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

    Here,

    ProductExport – Root Element
    Product – Top Level Element
    Price – Element
    @Price is selected as Attribute.

    Root

    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.

    Path mode defaults to putting data in Elements.

    If you are using an XML Path stream, then by default all of the values will be shown as elements.

    Gopalakrishnan Arthanarisamy
    Unisys, Bangalore, India.

    Like

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

    as explained in this article. Using @FieldName attribute for Price we make the price to be the attribute. With the Path(‘Product’) we change default Row to Product. Using [*] we removed tag and so our data are shows without an extra tag.

    This is very helpful article!

    I am from USA

    Like

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

    Article was really very good .. I joined your community two days back and found it very useful.

    Like

  8. Correct Answer : Option 4

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

    The root element to each top level element tagged as using this above query.

    Chennai, TamilNadu, India

    Like

  9. The correct answer is option 4

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

    Mahmad Khoja
    Country : INDIA

    Like

  10. Correct answer option 4

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

    Using @FieldName attribute for Price we make the price to be the attribute. With the Path(‘Product’) we change default Row to Product. Using [*] we removed tag and so our data are shows without an extra tag.

    Nikhildas
    Cochin
    INDIA

    Like

  11. ##Correct Answer Option 4.

    Because: Option 1 and 2 are incorrect because it does not have alias as [@Price], now in Option 3 XML Path and Root is not decalred. So Option No 4 is the Correct Answer.
    “SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts
    FOR XML PATH(‘Product’), ROOT(‘ProductExport’)”

    Thanks…
    Rajneesh Verma
    (INDIA)

    Like

  12. Correct answer is option 4:

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

    City: Baroda
    Country: INDIA

    Thanks,
    GurjitSingh

    Like

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

    As we need ‘ProductExport’ as root and “Product” as path, we can achieve this by above query only.

    Mansi Shah
    India

    Like

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

    Sudeepta,
    India

    Like

  15. Correct answer is option # 4

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

    Thanks

    Alpesh Gorasia
    (India)

    Like

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

    Explanation
    ===========
    DECLARE @CurrentProducts TABLE (productName VARCHAR(10),price int)
    INSERT INTO @CurrentProducts(ProductName,price) SELECT ‘Product1′,99
    INSERT INTO @CurrentProducts(ProductName,price) SELECT ‘Product2′,199
    INSERT INTO @CurrentProducts(ProductName,price) SELECT ‘Product3′,299
    INSERT INTO @CurrentProducts(ProductName,price) SELECT ‘Product4′,399

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

    Detail Explanation:
    ===============
    Root (ProductExport) – Add the Root Element ProductExport
    Path(‘Product’) – change default Row to Product.
    Using [*] -removed tag

    Mitesh Modi
    (India)

    Like

  17. Hi sir,

    correct answer is option no 4

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

    which gives the following output

    Product1
    Product2
    Product3
    Product4

    Regards,
    P.Anish Shenoy
    INDIA

    Like

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

    We have to make an xml with root node name ProductExport and with top level element tag Product .To do that we must have to use xml path and though the top level element tag is Product so we have to use path(‘Product’) . Now we have to add a attribute price in top level element tag so we have alias Price as [@Price] .Now we have make the root node as ProductExport.So we have to use Root(‘ProductExport’). All these criteria can be fulfill by option 4 only .
    So the correct ans is option 4.

    Partha,
    India

    Like

  19. It seems that the correct answer is Option #4

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

    Explanation:

    using [@Price] Field we will get Price=”” beside Product tag.

    Using the Path(‘Product’) we will get the default Row as Product.

    Using [*] we removed tag and so our dataSet will come without an extra tag hence our XML will we in the same way as required.

    Diljeet Kumari
    Country : INDIA

    Like

  20. Correct Answer is 4

    Explanation :

    Option 1 will not produce a ROOT Element and attribute so it is not correct

    Option 2 will consider “CurrentProducts” as TagName and all fields as attribute. Although it will create a Root Element. So this is also not correct.

    Option 3 will not create Root Element and take “ProductExport” as TagName. It also not create any attribute.

    Option 4 will create a Root Element as “ProductExport”,TagName as “Product” and will also consider “price” as attribute. So this is correct.

    Thanks,
    Geetanjali Agarwal
    India, Noida

    Like

  21. Answer is 4
    The @price construct moved priceName inside the tag as an attribute
    [*] attribute eliminated the productname tag retaining the content

    Kerala,India

    Like

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

  23. Hi Pinal,

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

    Product1
    Product2
    Product3
    Product4

    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’)

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

    Explanation:
    Choice #1 is aliasing the table CurrentProductas as ProductExport. This won’t give a result stream with a root node of ProductExport. This choice is incorrect.

    Choice #2 provides a root node of ProductExport, but doesn’t provide the Product tag. This choice is incorrect.

    Choice #3 doesn’t provide a root node. This choice is incorrect.

    Choice #4 provides the output that was requested. This is the correct choice.

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

    Like

  24. correct Answer is:
    4. SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts
    FOR XML PATH(‘Product’), ROOT(‘ProductExport’)
    Because we want to show both attributes and elements we will use XML PATH,
    change the ROOT Node for opening and closing tags to ProductsExport. Include the @ price to add to the Products tag.

    Ron A. Farris – USA

    Like

  25. The correction is Option 4

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

    Option 1 and 3 don’t state ROOT(‘ProductExport’)
    Option 2 doesn’t declare the custom attribute Price

    Country of Residence:USA

    Like

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

    Kevin Scharnhorst
    United States

    Like

  27. the correct answer is option no 4 i.e.

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

    which will give you desired output…

    India

    Like

  28. Hi,

    The correct answer is Option 4

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

    Explanation is –

    As per the question the requirements are-

    Root node name – ProductExport
    Top level element tag – Product .
    Attribute – alias Price as [@Price] .

    Thanks,
    Hema Chaudhry,
    Delhi, India

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

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

  37. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

Leave a Reply

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

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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s