Bootstrap Docs Sidebar Explained

Do you really want to make a side navbar look just like the one from the Bootstrap Documentation pages? Then this is the article for you.

In addition to really liking the bootstrap library, I have to say that I really enjoy the presentational style of their documentation page. I think it’s a great idea to show off the big picture of a document, while automatically expanding each section when appropriate. It’s especially helpful for long documents that require a lot of scrolling. Unfortunately, they don’t specifically outline how their documentation pages are put together. But since they’re just delivering HTML/CSS/JS, we can reverse engineer how they put it together. Here are the results of dissecting Bootstrap’s Doc’s side nav bar.

Simple Content

This really works best with some content to scroll through, so let’ just start by making a bunch of blocks with unique ID tags, that are nested into sections, that we can visually see.

Here’s how:
Create a few blocks that look like this. Copy and paste it, but every time you see the letter A, replace it with B, C, and so on.

<section id="GroupA" class="group">
    <h3>Group A</h3>
    <div id="GroupASub1" class="subgroup">
        <h4>Group A Sub 1</h4>
    </div>
    <div id="GroupASub2" class="subgroup">
        <h4>Group A Sub 2</h4>
    </div>
</section>

Then style the blocks to give them a little bit of spacing. This is just so we don’t have to wade through hundreds of lines of lorem ipsum (yuk!).

.group {
    background: yellow;
    width: 200px;
    height: 500px;
}
.group .subgroup {
    background: orange;
    width: 150px;
    height: 200px;
}

Simple Layout

Now we need to create a two column layout for our page. We can put the sample body content on the right and the navbar on the left. We can do this with the Bootstrap Grid System, by placing both columns inside of a div with class='row' and specifying the column widths for all devices with col-xs-*. Finally when we scroll down, we want the content to freely scroll, but have the navigation bar remain in the same place, so we’ll use position: fixed to pin it to the top. It should look something like this:

<div class="row">
    <!--Nav Bar -->
    <nav class="col-xs-3">
        <div class="fixed">
            Nav Placeholder<br/>
            Stays on Top!
        </div>
    </nav>
    <!--Main Content -->
    <div class="col-xs-9">
        <section id="GroupA" class="group"></section>
        <section id="GroupB" class="group"></section>
        <section id="GroupC" class="group"></section>
    </div>
</div>

Simple Navlist

Before we even add any bootstrap, we need a bare bones list of links that will navigate to content on the page. This provides a nice tree structure, but no formatting. The links have bulky bullets in front of them and are very brightly colored.

<ul id="sidebar">
    <li>
        <a href="#GroupA">Group A</a>
        <ul>
            <li><a href="#GroupASub1">Sub-Group 1</a></li>
            <li><a href="#GroupASub2">Sub-Group 2</a></li>
        </ul>
    </li>
    <!-- Same for Group B & C -->
</ul>

Converting List to Nav

In order to make the links look more like navigation controls, and less like a list of groceries, we can use the set of Nav classes provided by Bootstrap. In order to use any nav class, you must also attach the .nav base class to that element as well. To make sure the list stays vertical, we want to also add the class .nav-stacked to each ul element.

<ul class="nav nav-stacked fixed" id="sidebar">
    <li>
        <a href="#GroupA">Group A</a>
        <ul class="nav nav-stacked" >
            <li><a href="#GroupASub1">Sub-Group 1</a></li>
            <li><a href="#GroupASub2">Sub-Group 2</a></li>
        </ul>
    </li>
    <!-- Same for Group B & C -->
</ul>

Let’s pause to look at just these changes because a lot has changed visually even though we haven’t added much code. The set of nav classes help strip out some of the default formatting associated with unordered lists and instead renders the links much like menu bars. The elements now all align all the way on the left because .nav sets padding-left: 0;. We’ve removed the underline with text-decoration: none;, removed some of the list formatting with list-style: none;, and softened the colors a bit with color: #428bca;. The nav-stacked floats all the elements to the left so they ‘stack’ on top of each other.

Formatting the Nav Bar

The final change to the markup is just to add the class bs-docs-sidebar to the top nav column div to help identify it in CSS. We can do the rest in CSS and JavaScript.
First, let’s give the navbar a little breathing room by giving it margins on the top, left, and bottom:

/* sidebar */
.bs-docs-sidebar {
    padding-left: 20px;
    margin-top: 20px;
    margin-bottom: 20px;
}

Next, we’d like to be able to apply different formating to parent level links and child links. CSS does not currently have a Parent Selector which could be used to differentiate the top level links with those nested below them. Instead, we can apply a style to all links inside of bs-docs-sidebar and then override that style for any list items that are children of two ul.nav elements.

/* all links */
.bs-docs-sidebar .nav>li>a {
    color: #999;
    padding: 4px 20px;
    font-size: 13px;
    font-weight: 400;
}

/* nested links */
.bs-docs-sidebar .nav .nav>li>a {
    padding-top: 1px;
    padding-bottom: 1px;
    padding-left: 30px;
    font-size: 12px;
}

For all links we’ll apply a grey color schema and a font-weight of 400. All links will be padded in at least 20 pixels, but those nested under two .nav elements will be indented 30px. Top level links will be slightly larger at 13px. And nested links will have much less padding on the top and bottom.

Using Scrollspy

To do the rest of the styling we’ll need to know whether a link is active. In order to do this, we can use a scroll spy on the page which will apply the .active class to the navigation list whenever a given element is scrolled into view.
Scroll spy is called on the element whose scrolling activity you want to monitor. Since you will probably be scrolling through the entire page, this should go on the body element.
The target of scrollspy is:

the ID or class of the parent element of any Bootstrap .nav component.

So we’ll target the #sidebar by passing in it’s parent: .bs-docs-sidebar

The offset represents the pixels to offset from top when calculating position of scroll. We’ll give it a running start of 40, so it can find the first nested child item of each group so that will be set as active as well.

$('body').scrollspy({
    target: '.bs-docs-sidebar',
    offset: 40
});

You are still in charge of styling any elements you would like to display. Scroll spy merely adds the active class. As of right now, it won’t look like it’s doing anything because we haven’t styled the elements yet. As a placeholder, just to see it working, let’s color active links purple. We’ll replace this with more sophisticated stuff next.

.bs-docs-sidebar .nav>.active>a {  color: #563d7c; }

Whenever an element is set to active (due to scrollspy) or is hovered or focused, we’ll apply some styles to the anchor. We’ll color it purple. We’ll make sure that it doesn’t have an underline or a grey box highlighting it. And we’ll add a purple flag on the left to help identify which items are active. To do this, we’ll apply a 2 pixel border to the left of the element.

Note: Because of the way the CSS box model works, when we add a 2px border to the left, the entire element shifts 2 pixels to the right, displaced by the border that previously took up zero pixels. One way to handle this is to shorten the padding we added by 2px every time the element is active. But I think a cooler trick is to just start off with a transparent 2px border so the object does not get resized when adding a colorful border

/* all links */
.bs-docs-sidebar .nav>li>a {
    /*add trasnparent border */
    border-left: 2px solid transparent;
}
/* active & hover links */
.bs-docs-sidebar .nav>.active>a, 
.bs-docs-sidebar .nav>li>a:hover, 
.bs-docs-sidebar .nav>li>a:focus {
    color: #563d7c;                 
    text-decoration: none;          
    background-color: transparent;  
    border-left: 2px solid #563d7c; 
}

Let’s also make active parent links have a very thick weight, and child links less so.
Remember: we’ll use the style we want for parent objects on all the links and then override it for nested links.

/* all active links */
.bs-docs-sidebar .nav>.active>a, 
.bs-docs-sidebar .nav>.active:hover>a,
.bs-docs-sidebar .nav>.active:focus>a {
    font-weight: 700;
}
/* nested active links */
.bs-docs-sidebar .nav .nav>.active>a, 
.bs-docs-sidebar .nav .nav>.active:hover>a,
.bs-docs-sidebar .nav .nav>.active:focus>a {
    font-weight: 500;
}

Collapsing Inactive SubGroups

One of my favorite features of the Bootstrap Navbar is that it automatically collapses subgroups that are not currently in view. This allows a lot of information to be available, but prevents a lot of noise when it’s not in use. To do this we’ll use the active flag on the parent group. To hide all subgroups, we’ll set display:none to all ul.nav elements that are children of other .nav elements. This will collapse all subgroups. Then we need to expand the active group by looking for a parent level nav with an .active child and set display:block on it’s child ul. So it will look like this:

/* hide all (inactive) nested list */
.bs-docs-sidebar .nav ul.nav {
    display: none;           
}
/* show active nested list */
.bs-docs-sidebar .nav>.active>ul.nav {
    display: block;           
}

And behave like this:

Wrap Up

So that’s it. You can have fun applying other styles as well. Bootstrap uses Affix to lock the navbar into place after scrolling past the header.
Also, they use media queries to collapse the navbar if the screen is below a certain size like this:

@media screen and (max-width: 500px){
    .bs-docs-sidebar {
        display:none
    }
}

The impetus for this article was actually a spice website that I was making to catalog my home spices. It uses the bootstrap side bar when space allows, but then converts into a top navbar for smaller screens. You can view the final page here:

http://kylemitofsky.com/Spices/

And you you can browse the source code here if you’re interested in how anything is done:

https://github.com/KyleMit/Spices/tree/gh-pages

Here’s the final fiddle. Feel free to play around with it, fork it, or leave me a comment below.

Update with Top NavBar:

Here’s a quick rundown of how to add a horizontal navbar to the example. The primary difficulty in adding any fixed position navbar to the top of the window is it will break all your anchor tags like so:

The first trick when adding a navbar is to displace everything on the page by the same number of pixels, that way nothing starts off hidden underneath the navbar. The standard implementation (even listed in the docs) is to just offset the entire document by placing a top margin or padding on the body element:

body { margin-top:50px; }

But as you can see from the previous example, this doesn’t solve the issue.

Why is that?

For more information, you can see my Stack Overflow answer to the question When navigating to #id, element is hidden under fixed position header, but here’s the gist of it. When the browser is told to navigate to a fragment identifier (#ID):

your browser always wants to scroll your anchor to the exact top of the window. If you set your anchor where your text actually begins, it will be occluded by your menu bar.

One way to overcome this is to make sure the content of your anchor element starts well after the element begins. To do this, we’ll need a basic understanding of the CSS box model. We’ll give the element some extra height at the top by setting the padding-top to about 50px, but since we don’t actually want each anchor element to have 50 pixels of overhead, we’ll also set the margin-top the the same amount, but negative.

Here’s an example, that hopefully makes the point more concrete:

By adding this CSS

.group, .subgroup {
    padding-top: 50px;
    margin-top: -50px;
}

We make the element grow 50 pixels taller, but ensure that the content stays in exactly the same place. Here’s a look at the example from the chrome developer tools:

PaddingMarginOffset

Now when we scroll the top of the element to the top of the window, it will start 50 pixels before the content. Here’s a full example with a working top navbar

Update with Scrollable Navbar

To make the sidebar scrollable, you can add the following CSS:

.bs-docs-sidebar > ul {
    overflow-y: auto;
    height: 100%;
}

When content overflows it’s container, there are a couple different ways to handle it in css:

overflow: visible | hidden | scroll | auto

The default is to have the content remain visible. However, this poses problems when using an element with position:fixed because you cannot simply scroll through the window to bring the visible element into view.

You can use overflow: scroll to add a scrollbar to the div, but this will always be visible, even if unnecessary, and scrollbars should be avoided unless absolutely necessary. The better option is to use overflow: auto which will provide a scrollbar only if necessary. Since we’d rather wrap long horizontal text than scroll it, we’ll only apply this to the y-axis by using overflow-y: auto.

You’ll notice once this is in place it doesn’t do anything yet. That’s because we need to tell the container how large it is so it knows when any of its contents are taking up more space that it can provide. As a test, you can just throw in height: 100px and you’ll notice that the entire contents fits into a box that is 100px tall and you can scroll to get to the rest of it.

However, we don’t necessarily know how much space we want to allow the sidebar to consume. It’s going to depend on the space available in the window and how you’re site is laid out. In the simplest form, if we alloted the entire screen height to the sidebar, we could use height: 100%.

Note: Whenever you use height: 100% in CSS, you have to next ask yourself, “100% of what?” Often this is the parent element, but fixed position elements break the layout so 100% will automatically refer to the window size. If your sidebar does not start at the top of the window, 100% height will extend past the bottom of the screen and make the scrollbar difficult to manage. You can choose a height <100% or apply your own padding to the element, instead of its parent.

Here’s a demo with a scrollable sidebar:

You can look at my spice project for a demo in production using a scrollable sidebar

Update with Affix

In the spirit of the Bootstrap’s own use of their sidebar, you can use affix to help place the location as you scroll through the page. You’ll just need to add the affix to your sidebar like this:

$("#sidebar").affix({
    offset: {
      top: 60
    }
});

And then set some styling when the .affix class is in place (bootstrap will automatically add position:fixed so we just need to set the height:

#sidebar.affix {
    top: 20px;
}

Here’s a demo with an affixed sidebar:

Insert HTML, CSS, and JS into any Web Page with a Chrome Extension

Overview

Chrome extensions have pretty good documentation and pretty good community support. Naturally, you might expect that between docs and forums that you can get a small project up and running quickly.

Unfortunately, sometimes minor simple tasks can get lost in the weeds. Documentation usually only covers the basic, small, happy flow cases and forums usually only ask about difficult, large problems. Well what about issues of medium size and complexity? Leave that to the bloggers!

Here we’ll take a look at how to create a chrome extension that uses a Content Script to change the background color of any page using jQuery.

Manifest.json

First things first. You need a way to tell chrome what your intentions are, what components come with your extension, and when they apply. To do this, we’ll use a manifest file.

The manifest file contains some JSON formatted data that Google knows how to read. There is a lot of boiler plate info that you can explore, but, for our purposes, we want to pay special attention to the content script section:

{
  "manifest_version": 2,
  "name": "Page Changer",
  "version": "0.1",
  "description": "Page Changer",
  "icons": { "16": "Icons/Logo16.png",
             "32": "Icons/Logo32.png",
             "48": "Icons/Logo48.png",
            "128": "Icons/Logo128.png" },
  "content_scripts": [
    {
      "matches": ["*://*/*"],
      "js": ["jquery.min.js", "script.js"]
    }
  ]
}

The content script must specify two things:

  1. The pages against which to run the code
  2. The external code files to run.

In this case, we’ve specified that we want to our content to run whenever the web url matches the pattern *://*/*, which is to say for all protocols, for all domains, for all pages (in other words, run everywhere).

If you just wanted to target google.com or any of it’s content pages, then you could put that there.

The second thing we’ve specified is which files to run and in which order. First we’ll load jQuery from a local copy that we deploy, and next we’ll run a file called script.js that we’ll look at in a second.

Script.js

In this simplified example, let’s say we just want to change the background color of the current page for a very obvious example of whether or not all the right components have been called. In a real world example, you might want to find all the links in the page and turn them a particular color. Or attach some code to any images on the page to allow you to easily email them. Whatever you want!

For now, let’s just run the following script which will find the body element and use the css() method in jQuery to apply the value blue to the background property:

$(function() {
	$("body").css("background","blue");
});

Note: Just so I don’t get comments about it. If this was the only change you wanted to make to the page, you can and should do this purely with CSS. The idea was to add jQuery to a page in a trivial example so you can implement your own functionality later.

Deployment

To run the extension, do the following steps:

  1. In Chrome, Open the Browser Menu
  2. Click on Tools, and then Extensions
  3. Make sure Developer Mode is checked (usually in the top right)
  4. Click Load Unpacked Extension and select the folder that contains your manfiest and extension files.

That’s it! Go to any affected page and hit refresh to see your changes applied:

Demo

To get started, you can download all the files you need to run this extension from the SkyDrive folder below.


If you’ve made changes and are happy with them, you can even deploy to the Chrome Extension Store for other people to use after getting a developer account.

Here’s a couple of extensions I’ve built for chrome, along with their source code if you’re looking for examples. Feel free to leave comments on GitHub with any suggestions or bugs using GitHub’s issue tracker.

  • Link Finder - Find all links to named anchors within the page so you can create descriptive links to content within a page
  • Copy Tabs - Creates a keyboard shortcut (Ctrl + Shift + C) which copies links for selected tabs

Upcoming: Part 2 - How to Insert HTML, CSS, and JavaScript into any page using a browser action.

SQL Like 'In' Function For .NET

SQL has a wonderful terse and natural syntax for checking if an item is inside of a collection by using the IN keyword. Which can be expressed like this:

expression IN (value_1, value_2, .... value_n)

In .NET, we can preform the same operation with .Contains() method on any enumerable collection which:

Determines whether a sequence contains a specified element

Beginning with VB 2010, we can even do this on the fly by using the Collection Initializers.

For example, to check whether a variable called personName was either "Sally" or "Jenny", we could use the following expression:

{"Sally","Jenny"}.Contains(personName)

However, I think this syntax leaves something to be desired. The verbiage is all wrong. I don’t really care if a collection contains some item. I care if an item is in a collection. Of course, logically, this is performing the same operation, but I want the personName variable to be in the drivers seat. I want personName to be the subject that is verbing against the other items.

For a bit of syntactic sugar, we can add a generic extension method to take in an ParamArray and check if the extended element falls inside that array.

Here’s the In method:

Note: In needs to be inside of square brackets because it is a Protected Keyword.

Visual Basic

''' <summary>
''' Determines if the Item is contained within the listed array
''' </summary>
''' <typeparam name="T">The type of object</typeparam>
''' <param name="item">The calling item</param>
''' <param name="range">An array or comma separated list of the items to check against the calling</param>
''' <returns>True if item is found in list</returns>
''' <remarks>Provides syntatic sugar by reordering the subject of the IEnumerable.Contains method</remarks>
<Extension()>
Public Function [In](Of T)(ByVal item As T, ByVal ParamArray range() As T) As Boolean
    Return range.Cast(Of T).Contains(item)
End Function

C Sharp

public static class Extensions
{
    /// <summary>
    /// Determines if the Item is contained within the listed array
    /// </summary>
    /// <typeparam name="T">The type of object</typeparam>
    /// <param name="item">The calling item</param>
    /// <param name="range">An array or comma separated list of the items to check against the calling</param>
    /// <returns>True if item is found in list</returns>
    /// <remarks>Provides syntatic sugar by reordering the subject of the IEnumerable.Contains method</remarks>
    public static bool In<T>(this T item, params T[] range)
    {
        return range.Contains(item);
    }
}

Throw this inside any module in your assembly, preferably one named something like Utilities or ExtensionMethods. Now we can call like this:

personName.In("Sally","Jenny")

If you’re checking against a predefined list you can pass that in as a parameter and cast back into an array.

Personally, I take this utility method with me wherever I go. I find it incredibly helpful for checking if an item exists within a defined range. Once you start using it, you won’t stop, and I think that’s a good thing! For my money, it substantially improves readability, especially if you find yourself working on older code bases without collection initializers.

List (Of LINQ Enumerable Methods)

Here’s a grouped listing of all the methods available on the IEnumerable Class.
Methods which can be composed using VB Query Syntax are generally listed first and are also highlighted yellow.

Projection Operations

  • Select - Projects each element of a sequence into a new form.
  • SelectMany - Projects each element of a sequence to an IEnumerable(Of T) and flattens the resulting sequences into one sequence.

Partitioning Data

  • Skip - Bypasses a specified number of elements in a sequence and then returns the remaining elements.
  • SkipWhile - Bypasses elements in a sequence as long as a specified condition is true and then returns the remaining elements.
  • Take - Returns a specified number of contiguous elements from the start of a sequence.
  • TakeWhile - Returns elements from a sequence as long as a specified condition is true.

Join Operations

  • Join - Correlates the elements of two sequences based on matching keys. The default equality comparer is used to compare keys.
  • GroupJoin - Correlates the elements of two sequences based on equality of keys and groups the results.

Grouping Data

  • GroupBy - Groups the elements of a sequence according to a specified key selector function.

Filtering Data

  • Where - Filters a sequence of values based on a predicate.
  • OfType - Filters the elements of an IEnumerable depending on their ability to be cast to a specified type.

Sorting Data

  • OrderBy - Sorts the elements of a sequence in ascending order according to a key.
  • OrderByDescending - Sorts the elements of a sequence in descending order according to a key.
  • ThenBy - Performs a subsequent ordering of the elements in a sequence in ascending order according to a key.
  • ThenByDescending - Performs a subsequent ordering of the elements in a sequence in descending order, according to a key.
  • Reverse - Inverts the order of the elements in a sequence.

Aggregation Operations

  • Average - Computes the average of a sequence of values.
  • Count - Returns the number of elements in a sequence.
  • LongCount - Returns an Int64 that represents the total number of elements in a sequence.
  • Max - Returns the maximum value in a sequence of values.
  • Min - Returns the minimum value in a sequence of values.
  • Sum - Computes the sum of a sequence of values.
  • Aggregate - Applies an accumulator function over a sequence.

Set Operations

  • Distinct - Returns distinct elements from a sequence by using the default equality comparer to compare values.
  • Except - Produces the set difference of two sequences by using the default equality comparer to compare values.
  • Intersect - Produces the set intersection of two sequences by using the default equality comparer to compare values.
  • Union - Produces the set union of two sequences by using the default equality comparer.
  • Concat - Concatenates two sequences.
  • Zip - Applies a specified function to the corresponding elements of two sequences, producing a sequence of the results.

Quantifier Operations

  • All - Determines whether all elements of a sequence satisfy a condition.
  • Any - Determines whether a sequence contains any elements.
  • Contains - Determines whether a sequence contains a specified element by using the default equality comparer.

Generation Operations

  • DefaultIfEmpty - Returns the elements of the specified sequence or the type parameter’s default value in a singleton collection if the sequence is empty.
  • Empty - Returns an empty IEnumerable(Of T) that has the specified type argument.
  • Range - Generates a sequence of integral numbers within a specified range.
  • Repeat - Generates a sequence that contains one repeated value.

Element Operations

  • First - Returns the first element of a sequence.
  • FirstOrDefault - Returns the first element of a sequence, or a default value if the sequence contains no elements.
  • Last - Returns the last element of a sequence.
  • LastOrDefault - Returns the last element of a sequence, or a default value if the sequence contains no elements.
  • ElementAt - Returns the element at a specified index in a sequence.
  • ElementAtOrDefault - Returns the element at a specified index in a sequence or a default value if the index is out of range.
  • Single - Returns the only element of a sequence, and throws an exception if there is not exactly one element in the sequence.
  • SingleOrDefault - Returns the only element of a sequence, or a default value if the sequence is empty; this method throws an exception if there is more than one element in the sequence.

Equality Operations

  • SequenceEqual - Determines whether two sequences are equal by comparing the elements by using the default equality comparer for their type.

Converting Data Types

  • Cast - Casts the elements of an IEnumerable to the specified type.
  • AsEnumerable - Returns the input typed as IEnumerable(Of T).
  • AsQueryable - Converts a (generic) IEnumerable to a (generic) IQueryable.
  • ToArray - Creates an array from a IEnumerable(Of T).
  • ToDictionary - Creates a Dictionary(Of TKey, TValue) from an IEnumerable(Of T)according to a specified key selector function.
  • ToList - Creates a List(Of T) from an IEnumerable(Of T).
  • ToLookup - Creates a Lookup(Of TKey, TElement) from an IEnumerable(Of T)according to a specified key selector function.

Explanation of using FOR XML PATH('') to Concatenate Rows in SQL

I was recently shown a cool way to concatenate an unlimited number of rows into a single cell in SQL, but was left wondering how it worked under the hood. The trick involves using FOR XML PATH('') to build multiple values and then cast them back into a single string. Starting from a simple query, and getting increasingly more complex, let’s look at how this actually works.

I’ll do everything on SQLFiddle. You can follow along by starting your own fiddle or clicking the results link for each step. Let’s start off by creating a basic table called Person with two columns and three rows of data:

Create A Table

Start by making a table:

CREATE TABLE Person 
(
  FirstName varchar(20), 
  LastName varchar(20)
);
INSERT INTO Person
  (FirstName, LastName)
VALUES
  ('Kyle', 'Mit'),
  ('Bob', 'Builder'),
  ('Jimi', 'Hendrix');

Plain SQL Query

This is just a run of the mill SQL query returning 3 rows of data:

SELECT FirstName, LastName
FROM Person 

Results:

FirstName LastName
Kyle Mit
Bob Builder
Jimi Hendrix

SQL Query as XML Grouped by ‘Person’

Here, the output of the Query is formatted into XML with each row represented as a Root node named ‘Person’ and each column as a child element:

SELECT FirstName, LastName
FROM Person 
FOR XML PATH('Person')

Results:

<Person>
    <FirstName>Kyle</FirstName>
    <LastName>Mit</LastName>
</Person>
<Person>
    <FirstName>Bob</FirstName>
    <LastName>Builder</LastName>
</Person>
<Person>
    <FirstName>Jimi</FirstName>
    <LastName>Hendrix</LastName>
</Person>

XML Query With Custom Named Child Nodes

The name of each child node under person is determined by the final column name (this is important later). When columns are selected, those are used by default, however you can manually give any column a specific name.
Note: The name of the child nodes has changed based on our select statement:

SELECT FirstName AS First, LastName AS Last
FROM Person 
FOR XML PATH('Person')

Results:

<Person>
    <First>Kyle</First>
    <Last>Mit</Last>
</Person>
<Person>
    <First>Bob</First>
    <Last>Builder</Last>
</Person>
<Person>
    <First>Jimi</First>
    <Last>Hendrix</Last>
</Person>

XML Query with comma and NAMED child node

In SQL, when you perform any kind of aggregation or selection function to a column, it no longer applies a default name. We’d like to concatenate our fields with a comma, so we’ll add a comma to the select. As an intermediary step, we’ll explicitly specify a name just to show that the comma itself isn’t modifying the result set:

SELECT ',' + LastName AS Last
FROM Person 
FOR XML PATH('Person')

Results:

<Person>
    <Last>,Mit</Last>
</Person>
<Person>
    <Last>,Builder</Last>
</Person>
<Person>
    <Last>,Hendrix</Last>
</Person>

XML Query with comma and UNNAMED child node

By removing the explicit namimg, SQL isn’t able to guess the column name. Consequently, the data is just stuffed into the Person Element.
From the FOR XML PATH documentation:

Any column without a name will be inlined. For example, computed columns or nested scalar queries that do not specify column alias will generate columns without any name.

NOTE: This is a very important step towards concatenation. We need to somehow get rid of the xml markup around our data and we’ve dropped an entire node. In the next step we’ll find out how to get rid of the second one.

SELECT ',' + LastName
FROM Person 
FOR XML PATH('Person')

Results:

<Person>,Mit</Person>
<Person>,Builder</Person>
<Person>,Hendrix</Person>

XML Query with no root node

The command we’ve been using all along is FOR XML which has four different modes:

  1. RAW
  2. AUTO
  3. EXPLICIT
  4. PATH

In this case, we’re using PATH, which will wrap the data elements in a parent element named for the table from which it came. Optionally, you can add a string parameter to Path to override the root element name. The last trick:

If you specify a zero-length string, the wrapping element is not produced.

By manually specifying the path as an empty string, all the data elements are shown right next to each other.
Note the file name: this is still returning XML (just poorly formatted XML)

SELECT ',' + LastName
FROM Person 
FOR XML PATH('')

Results:

XML_F52E2B61-18A1-11D1-B105-00805F49916B
,Mit,Builder,Hendrix

Get result of the XML sub query

By selecting the result of the entire query, we transform the XML into a value:

SELECT (
        SELECT ',' + LastName
        FROM Person 
        FOR XML PATH('')
)

Results:

COLUMN_0
,Mit,Builder,Hendrix

Remove the First Comma

To remove the leading comma, we’ll use STUFF(character_expression, start, length, replaceWith_expression). The following query will start at position 1 and replace the 1st character with '':

SELECT STUFF((
              SELECT ',' + LastName
              FROM Person 
              FOR XML PATH('')
), 1, 1, '')

Results:

COLUMN_0
Mit,Builder,Hendrix

Cast into VARCHAR for type safety

Finally, we’ll take the whole query and make sure it’s of type VARCHAR. Also, for good measure, we’ll give the returned column a name:

SELECT CAST(STUFF((
              SELECT ',' + LastName
              FROM Person 
              FOR XML PATH('')
), 1, 1, '') AS VARCHAR(MAX)) AS LastNames

Results:

LastNames
Mit,Builder,Hendrix

And that’s how to go from constructing XML to a relatively simple concatenation. There are other ways to do this and other customizations you can add to this query, but this should help you understand a little bit more of the fundamentals underneath it all.