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?
- SELECT Price, ProductName
FROM CurrentProducts AS ProductExport
FOR XML PATH(‘Product’) - SELECT Price, ProductName
FROM CurrentProducts
FOR XML AUTO, ROOT(‘ProductExport’) - SELECT Price [@Price], ProductName AS [*]
FROM CurrentProducts AS ProductExport
FOR XML AUTO, ELEMENTS - 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)
53 Comments. Leave new
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
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
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.
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.
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.
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
Correct answer is option 4.
Thank You
Uday
USA
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.
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
The correct answer is option 4
SELECT Price [@Price], ProductName AS [*]
FROM CurrentProducts
FOR XML PATH(‘Product’), ROOT(‘ProductExport’)
Mahmad Khoja
Country : INDIA
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
##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)
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
It looks correct answer should be option #4.
4.SELECT Price [@Price], ProductName AS [*]
FROM CurrentProducts
FOR XML PATH(‘Product’), ROOT(‘ProductExport’)
New Delhi
India
Question 32
Ans — #4 —-
SELECT Price [@Price], ProductName AS [*]
FROM CurrentProducts
FOR XML PATH(‘Product’), ROOT(‘ProductExport’)
Chennai, INDIA
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
Hi,
Correct answer is 4
select price[@Price],ProductName as [*]
from CurrentProducts
for xml path(‘Product’),root(‘ProductExport’)
I am from India
The correct answer is:
4.SELECT Price [@Price], ProductName AS [*]
FROM CurrentProducts
FOR XML PATH(‘Product’), ROOT(‘ProductExport’)
Sudeepta,
India
Correct Answer is option 4
Thanks,
Basavaraj
India
Correct answer is option # 4
SELECT Price [@Price], ProductName AS [*]
FROM CurrentProducts
FOR XML PATH(‘Product’), ROOT(‘ProductExport’)
Thanks
Alpesh Gorasia
(India)