XML Path Mode in SQL Server is a powerful feature that allows you to display data in both attributes and elements. In this blog post, we will explore how to use XML Path Mode and its various functionalities.

Let’s start by understanding the basics of XML Path Mode. By default, the XML Raw and Auto modes display data either as attributes or elements. However, if you want to display some data as attributes and some as elements, you can use the XML Path mode.

Consider the following example:

SELECT Loc.City, Emp.FirstName, Emp.LastName
FROM Location AS Loc
INNER JOIN Employee AS Emp ON Loc.LocationID = Emp.LocationID
FOR XML PATH('row')

In this example, we are retrieving all locations and the employees who work in each location. The result is displayed in XML Path mode, where each top-level element is tagged as “row” and all data is shown as elements.

To modify the XML result and include a root tag, we can use the ROOT(‘Employees’) clause:

SELECT Loc.City, Emp.FirstName, Emp.LastName
FROM Location AS Loc
INNER JOIN Employee AS Emp ON Loc.LocationID = Emp.LocationID
FOR XML PATH('row'), ROOT('Employees')

Now, the XML result will have a root element called “Employees”.

If we want to change the top-level element tag from “row” to “Employee”, we can modify the query as follows:

SELECT Loc.City, Emp.FirstName, Emp.LastName
FROM Location AS Loc
INNER JOIN Employee AS Emp ON Loc.LocationID = Emp.LocationID
FOR XML PATH('Employee'), ROOT('Employees')

This makes the XML stream more readable and organized.

In XML Path mode, all values are shown as elements by default. However, if you want to display a specific value as an attribute, you can use the [@Fieldname] syntax. For example, to display the “City” field as an attribute, you can alias it as @City:

SELECT Loc.City AS [@City], Emp.FirstName, Emp.LastName
FROM Location AS Loc
INNER JOIN Employee AS Emp ON Loc.LocationID = Emp.LocationID
FOR XML PATH('Employee'), ROOT('Employees')

By using the [@Fieldname] construct, you can easily control which values are displayed as attributes.

Now, let’s test your understanding with a quiz:

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

Please leave your answer in the comment section below with the correct option, explanation, and your country of residence. Every day, one winner will be announced from the United States and one winner from India. The contest is open until the next blog post is published.

Stay tuned to my Facebook page for the winners’ names and the correct answer.

Remember, XML Path Mode in SQL Server is a powerful tool that allows you to customize the display of your data in XML format. By understanding its functionalities, you can create more organized and readable XML streams.