Returning vertical data in one horizontal column

Having data in a normalized form is great. It helps eliminate redundant data, and therefore helps save space in the database. In this blog post, I want to discuss how you can query a normalized database to return denormalized data – that is, return data from many rows in a single column. I’ve used this technique a number of times, and below a couple of the ways I’ve put it to use.

  • At my current company we have a custom service which runs on a nightly basis and sends emails for various reasons. Some emails are sent to the user in question, but also copies in their manager(s). If the user has multiple managers, we need to put both of their email addresses into a single “CC” column, separated by a semicolon, as well as the employees email address in the “To” column.
  • Over the last 6 or so months, I’ve been building my company a Holiday & Sick Day management tool in ASP.Net & C# as a means to teach myself a coding language. In the tool, a manager can view sick days on a calendar by hovering over a highlighted day (indicating there was a sickness on that day), and a tool tip will appear with each person who was ill on that day. The tool tip came straight from a stored procedure in one column, making it much easier for me to utilize in C#.

Now that I’ve given you a small taster of what it’s used for, I should probably show you some SQL!

Using the second example given above, imagine we have the below query:

SELECT S.SicknessDate,
	   P.Name   
 
FROM Person P
	 JOIN Sickness S ON P.PersonId = S.PersonId

Which gives us this dataset:

Returning vertical data in one horizontal column

 

 

 

Great, we have data! But, we only want 2 rows, one for ’2012-01-01′ containing Zach Stagers and Some Guy, and another for ’2012-03-01′ for Other Fella.

How do we do that? Through the use of a correlated subquery making use of FOR XML PATH and the [text()] column name. Here’s the new code:

SELECT DISTINCT 
	   S.SicknessDate,
	   (
		SELECT Name + ', ' [text()]
 
		FROM Person P1
			 JOIN Sickness S1 ON P1.PersonId = S1.PersonId
 
		WHERE S1.SicknessDate = S.SicknessDate
 
		FOR XML PATH('')
	   ) [Names]
 
FROM Person P
	 JOIN Sickness S ON P.PersonId = S.PersonId

And the new, easier to utilize, data set:

Returning vertical data in one horizontal column_FIXED

 

 

 
Perfecto! Just what we wanted! The FOR XML PATH subquery kindly does all of the work for us, and the distinct gets rid of duplicate rows caused when multiple people are ill on the same day. In the subquery, we use where the sickness date of the inner query equals the sickness date of the outer query, turn the result into XML (FOR XML PATH(”)), and tell it to just output it as plain text ([text()]).

I hope you find as useful an implementation for this as I have!

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Returning Tabular Data as XML with FOR XML PATH and Nested XML Queries

FOR XML PATH is the favorite amongst most Database Developers due to its ease of use, and the fact that you can build almost any XML structure with it.

I’ll also be discusing nested XML Queries in this post, which kind of goes hand in hand with FOR XML PATH. More on this later.

I’ll start off with an example, then explain what’s going on afterwards:

SELECT P.Name AS "@Name",
       P.Age AS "@Age",
       'PersonId = ' + CAST(P.PersonId AS VARCHAR(5)) AS "comment()",
       (
        SELECT O.OrderId AS "@OrderId",
               O.Item AS "@Item"
	FROM Orders O
	WHERE O.PersonId = P.PersonId
	FOR XML PATH('Order'), TYPE
       ) 
 
FROM Person P
 
WHERE P.PersonId IN (1, 2)
 
FOR XML PATH('Person'), ROOT('People'), ELEMENTS XSINIL

As with each of the other XML Modes (Raw, Auto, and Explicit), you’re able to change the name of your elements from the default. To achieve this use FOR XML PATH(‘Person’), this will give your rows the name “Person”.

ROOT(‘People’) specifies the root element to be wrapped around you’re XML. The keyword ELEMENTS in this instance doesn’t do a whole lot as the structure is placed into elements and attributes depending on the names given to the columns, but XSINIL cannot be specified without it. XSINIL is used to give you’re XML a namespace, as well as to handle NULL values.

The actual output of the query used aboved is as below:

<People xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Person Name="Zach Stagers" Age="22">
    <!--PersonId = 1-->
    <Order OrderId="1" Item="Bike" />
    <Order OrderId="2" Item="Laptop" />
    <Order OrderId="3" Item="Phone" />
    <Order OrderId="4" Item="Keyboard" />
    <Order OrderId="5" Item="Mouse" />
    <Order OrderId="10" Item="Pizza" />
  </Person>
  <Person Name="Libbie Coulson" Age="24">
    <!--PersonId = 2-->
  </Person>
</People>

The first two columns in the select are specified as “@Name” and “@Age”, this assigns them as attributes to the Person Element.

Specifying the column name as “comment()” makes the value a comment within the XML, note that this is case sensitive – “comment()” is not the same as “Comment()”.

One column name that I haven’t used in the example is “text()”, this sets the value as the text of the element, instead of an attribute.. for example, using this against P.Name turns the value into:

<Person Age="22">Zach Stagers</Person>

Nested XML Queries

Nested XML Queries are what gives the FOR XML PATH mode its structure. To do this, as in the example above, you would use a correlated subquery. Here, you link the subquery to the outer query on the PersonId – linking the person to their orders, and therefore listing the orders beneath the correct person.

The subqueries FOR XML statement must have TYPE specified. This translates the queries results into true XML, Microsoft did this for backwards compatibility reasons.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Returning Tabular Data as XML with FOR XML EXPLICIT

FOR XML EXPLICIT can be a little awkward to use to say the least, but it has it uses. The good thing about EXPLICIT, is that you can mold it into almost any XML structure you like.

I actually found that reviewing the results as tabular data is what really helped me understand what the FOR XML EXPLICIT query is doing.

Below is the example code that I’m using:

SELECT
	1 AS Tag,
	NULL AS Parent,
	Name AS "Person!1!Name",
	PersonId AS "Person!1!PersonId",
	NULL AS "Orders!2!Item"
FROM Person
 
UNION ALL
 
SELECT
	2 AS Tag,
	1 AS Parent,
	NULL AS "Person!1!Name",
	PersonId AS "Person!1!PersonId",
	Item AS "Orders!2!Item"
 
FROM Orders
 
ORDER BY "Person!1!PersonId"
 
FOR XML EXPLICIT, ROOT('People')

The first thing to note with this query, is the two columns “tag” and “parent”. You must specify these columns in order to use FOR XML EXPLICIT. These columns specify the hierarchy of your results. The first query will have a “tag” of 1, and a NULL “parent”, as it is the first object declared. Each subsequent “tag” and “parent” will then go up by increments of 1.

Another thing to note, is the column names. The value listed before the first exclamation in the column name specifies the Element Name. The value listed before the second exclamation is the “tag” that it relates to. Then the attribute name is specified. ElementName!TagNumber!AttributeName.

Lastly, it’s the ORDER BY “Person!1!PersonId” that really gives the final result its correct structure.

The XML outputted by the query specified above is as follows:

<People>
  <Person Name="Zach Stagers" PersonId="1">
    <Orders Item="Bike" />
    <Orders Item="Laptop" />
    <Orders Item="Phone" />
    <Orders Item="Keyboard" />
    <Orders Item="Mouse" />
    <Orders Item="Pizza" />
  </Person>
  <Person Name="Libbie Coulson" PersonId="2" />
</People>

As with RAW and AUTO, the ROOT() simply specifies the name of the root element. This is optional.

Without the aforementioned ORDER BY clause, all of the orders for person “Zach” would appear under “Libbie” as she is last in the list, and the UNION ALL will just tack the results from the second query to the end of the result. Ordering by the key between the two tables ensures that the orders against PersonId 1 appear under the person with PersonId 1.

As I mentioned earlier in this post, what really helped me understand what was happening with FOR XML EXPLICIT was seeing the results in tabular form. To do this, simply remove the FOR XML line and re-run the code. In the below tabular example, I’ve removed FOR XML EXPLICIT, ROOT(‘People’) from the query:

FOR XML EXPLICIT Tabular

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Returning Tabular Data as XML with FOR XML AUTO

About a week ago I posted on FOR XML RAW, this time it’s FOR XML AUTO‘s turn.

The two are actioned via very similar syntax, but operate slightly differently. Auto is probably named “Auto” because it performs a lot of the XML node naming for you. There are also a couple of other differences which I’ll explain in this post.

With FOR XML AUTO, you don’t have the ability to name your row’s like you do in RAW. This is because their names are derived from the aliases given to the tables in your query. For example, having a table named Person, and using FOR XML AUTO against this table would give your rows a name of “Person”. This is the same as using FOR XML RAW(‘Person’), see below for a literal example of this:

SELECT * FROM Person
FOR XML AUTO
SELECT * FROM Person
FOR XML RAW('Person')

Both of the above examples would return the below XML:

<Person PersonId="1" Name="Zach Stagers" Age="22" />

As with RAW, you can specify your root element in AUTO:

SELECT * FROM Person
FOR XML AUTO, ROOT('People')

The above would return the exact same XML data, but with the specified root element wrapped around it.

<People>
 <Person PersonId="1" Name="Zach Stagers" Age="22" />
</People>

The attributes of the person row can further be split out into their own elements by specifying ELEMENTS, and can also be given a namespace and the ability to handle SQL NULL‘s by specifying XSINIL

SELECT * FROM Person
FOR XML AUTO, ROOT('People'), ELEMENTS, XSINIL

The above example would return:

<People xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <Person>
  <PersonId>1</PersonId>
  <Name>Zach Stagers</Name>
  <Age>22</Age>
 </Person>
</People>

There are a couple of other things to remember when using FOR XML AUTO, and that is that the order of your XML elements is down the order in which your columns are specified. For example, executing the below two queries would give you the same information, but using them with FOR XML AUTO will give you completely different XML:

SELECT Person.Name,
       Orders.Item
 
FROM Person 
     JOIN Orders ON Orders.PersonId = Person.PersonId
 
FOR XML AUTO, ROOT('PeopleWithOrders')

Returns:

<PeopleWithOrders>
 <Person Name="Zach Stagers">
  <Orders Item="Bike" />
  <Orders Item="Laptop" />
  <Orders Item="Phone" />
 </Person>
</PeopleWithOrders>
SELECT Orders.Item,
       Person.Name
 
FROM Person 
     JOIN Orders ON Orders.PersonId = Person.PersonId
 
FOR XML AUTO, ROOT('PeopleWithOrders')

The above query, with the columns swapped over will return:

<PeopleWithOrders>
 <Orders Item="Bike">
  <Person Name="Zach Stagers" />
 </Orders>
 <Orders Item="Laptop">
  <Person Name="Zach Stagers" />
 </Orders>
 <Orders Item="Phone">
  <Person Name="Zach Stagers" />
 </Orders>
</PeopleWithOrders>

So, you just need to remember that the order of the columns is what specifies the output of your XML, not the order of the tables in the FROM clause. The names given to the tables, however, specifies the names given to the XML nodes.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Returning Tabular Data as XML with FOR XML RAW

Working with XML in SQL isn’t something that I’ve done much of in the past. Over the last few weeks, however, I have been called upon to produce data in XML form – which has lead to a bit of research and reading on my part, and I thought I’d post on my findings.

FOR XML RAW appears to be the easiest of the 4 available XML modes in SQL. I will post on the other 3 (Auto, Explicit, and Path) at some point, but for now I’ll explain Raw.

I’ll start with an example, and work my way through it with explanations afterwards.

SELECT 1 AS [Column1]
UNION
SELECT 2 AS [Column2]
UNION
SELECT NULL AS [Column3]
FOR XML RAW('Test'), ROOT('Rows'), ELEMENTS XSINIL

Running the above SQL will return the below XML:

<Rows xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Test>
    <Column1 xsi:nil="true" />
  </Test>
  <Test>
    <Column1>1</Column1>
  </Test>
  <Test>
    <Column1>2</Column1>
  </Test>
</Rows>

Everything after the keyword RAW is optional in the query, but it all helps tidy up your result set, as I’m about to explain.

Specifying FOR XML RAW simply turns your tabular data into an XML Fragment. An XML Fragment is essentially part of a full XML structure, it doesn’t contain a root element – such as “Rows” in the example XML. Each of the 3 select’s in the example would create a single fragement line such as the below:

<row />
<row Column1="1" />
<row Column1="2" />

Specifying (‘Test’) applies the name Test to each of the XML Fragement Elements. In the above XML Fragment example, each occurrence of the word “row” would be replaced with “Test”.

Adding ROOT(‘Rows’) places root tags around your XML Fragment, turning the output into this:

<Rows>
  <Test />
  <Test Column1="1" />
  <Test Column1="2" />
</Rows>

Next we have the keyword Elements, which transforms your XML output into XML Elements, rather than XML Attributes:

<Rows>
  <Test />
  <Test>
    <Column1>1</Column1>
  </Test>
  <Test>
    <Column1>2</Column1>
  </Test>
</Rows>

Lastly theres XSINIL. This is how XML handles SQL NULL’s, which are known as NIL in XML. If this isn’t specified and NULL is returned in your result set, an empty element will be listed – such as the self closing Test tag in the last XML example. Using this option will add an XML namespace as an attribute of the root, and add xsi:nil=”true” as an attribute to any NULL / NIL records. Note that this option can only be specified if ELEMENTS is used.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)