posted by | on Uncategorized | No comments

SQL SERVER – XML Query Techniques

August 10, 2012 by rickmorelan

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 query in the figure below 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 as Loc, and the Employee table is aliased as Emp.

SELECT *
FROM Location AS Loc
INNER JOIN Employee AS Emp
ON Loc.LocationID = Emp.LocationID

Let’s stream this query into an XML mode, called Path. By default, the Path, Raw and Auto XML modes, have no root element and each top-level element has a markup tag named <row>. However, unlike the Raw and Auto modes, the Path mode default is to place all its data into elements.

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

If you click on the XML field of the results, another tab will open that displays the elements in an easy to read format. The three itemized fields from the query (City, FirstName, LastName) are shown as elements nested together. By default, each group of elements is enclosed within a set of <row> tags.

Our next goal is to modify the query, so that XML results will include a root tag named Employees. To do this, a simple modification to the FOR XML PATH clause is necessary. The following code sample will add an <Employees> root tag to the XML output.

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

Custom Attributes

If you are using an XML Path stream, then by default all values will be shown as elements. However, it is possible to pick one or more elements to be shown with an attribute(s) as well.

Let’s try creating a new element tag named Employee and move the <City> element inside the <Employee> tag as an attribute. Using the [@Fieldname] construct will be necessary to to accomplish this task.

This construct requires each field to be converted to an attribute, be aliased with a name that begins with an ampersand and is enclosed in brackets i.e., [@City]. One easy way to think of this code syntax is to remember that @ (a.k.a., the “at” sign) goes with “ATtributes”. City is now an attribute of Employee, instead of a separate element.

For illustrative purposes, the City attribute will be aliased as “CityName”. This is to show that XML queries, like other T-SQL queries, have the freedom of being aliased with more user-friendly names to meet the end user needs.

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

Question: Is it possible to create a root tag and give it your own unique name?
This lesson is a condensed excerpt from Chapter 8:
SQL Architecture Basics: Core Architecture Concepts (SQL Exam Prep Series 70-433 Volume 5 of 5)

Welcome to Joes2Pros
Joes2Pros Academy
Joes2Pros Website