XML queries in MS SQL Server

Recently I needed to extract some data that was stored as XML in a SQL Server 2012 db; I found the documentation lacking, mainly because you need to know both MS proprietary SQL extensions and XPath, to query the xml. Here's what I figured; it may not be optimal, but it works :) (BTW, I think Server 2008 has the same XML methods, but I only tested with 2012).

The xml type

SQL server supports an XML datatype; we use singlle-quotes to denote its values, but it is not quite a varchar type. Below we define a table Person, with id, name and an XML field called SkillsXML; we insert two rows. The xml looks like <skills>  <skill level="High"> SQL</skill> ... </skills>.

	CREATE TABLE People (
	id int primary key,
	name varchar(20),
	SkillsXml xml
	);
	INSERT INTO People(id,name,SkillsXml) VALUES(1,'Orlando',
	'<skills>
	<skill level="High">SQL</skill>
	<skill level="Medium">C#</skill>
	</skills>');
	INSERT INTO People(id,name,SkillsXml) VALUES(2,'Lina',
	'<skills>
	<skill level="Low">SQL</skill>
	<skill level="Medium">Java</skill>
	</skills>');

view raw Tables.sql hosted with ❤ by GitHub

Simple queries

With this, we can use the query method of the xml datatype to select only certain nodes; for example, the following query would select only the first skill for each person. Notice we're using an XPath expression for selecting here; simple XPath expressions look like folders; here we're selecting from the root of the document, the skills node, and from there the first skill node (notice XPath starts counting at 1, not 0).

	SELECT id,name, SkillsXml.query('/skills/skill[1]')
	FROM People

view raw Query1.sql hosted with ❤ by GitHub

Notice the value returned is of type xml, not a varchar, which is fine to just look at it, but you may want to cast it as a varchar to use it in other places.

Getting the value of an attribute

We can also use the value method, which allows us to get the value of an XML attribute; this method takes 2 arguments, an XPath expression and a string with a SQL datatype; the value of the XPath expression will be cast (or converted) to that type. The following query gets the value of the level attribute of the first skill.

	SELECT id,name, SkillsXml.value('(/skills/skill[1]/@level)[1]','varchar(10)')
	FROM People

view raw value.sql hosted with ❤ by GitHub

Nodes queries

The most interesting queries use the nodes xml operator; with this, and cross apply (or outer apply) we can get one row per xml element; for example, the following query gets us each person with their skills; since each of our person rows has 2 skills, we will get 4 rows total.

	SELECT P.*, Skill.query('.')
	FROM People P CROSS APPLY SkillsXml.nodes('/skills/skill') as Skills(Skill)

view raw cross_apply.sql hosted with ❤ by GitHub

We can make this much nicer by using a WITH expression, selecting both the skill name (which is text inside the element and its level attribute, and casting each of them as a varchar; the following example selects only those skills with Medium level.

	WITH
	PeopleAndSkills AS (
	SELECT P.*,
	cast(Skill.query('./text()') as varchar) as SkillName,
	Skill.value('(./@level)[1]' ,'varchar(20)') as SkillLevel
	FROM People P CROSS APPLY SkillsXml.nodes('/skills/skill') as Skills(Skill)
	)
	SELECT * from PeopleAndSkills
	WHERE SkillLevel='Medium'

view raw complex_nodes_query.sql hosted with ❤ by GitHub

To Do

I will post examples using XML namespaces, and will add links to other references soon.

Web api and Dapper for super-simple REST APIs

Recently c# got the dynamic keyword, which allows for truly dynamic typing (like in Ruby, Python or Javascript), and allow you to define objects to which you can add fields at runtime. In fact, we now have ExpandoObject in the standard library.

An obvious place to take advantage of that is with communicating to databases; Most times, we're still using a relational database, and use SQL to communicate with it. SQL is dynamically typed (at least in the sense that your types are coming from a database, independently of your program), and translating it to our objects usually involves lots of gruntwork; it would be nice if the language helps with that gruntwork; with dynamic and ExpandoObject being available, there are now a few micro-frameworks for DB access; one of the simplest is Dapper ; it just adds a few extension methods to the SqlConnection class, and can either automatically put data into our classes, or into dynamic objects (which is what we'll do).

The new-ish web api in ASP.net automatically generates REST APIs with very little code; with that, and Dapper, we can give access to our database in very little time; for example, if we have a database with one table, called People, with two fields, id, and name; we can generate a controller with code as simple as:

	using Dapper;
	namespace DapperCleanDemo.Controllers
	{
	public class PeopleController : ApiController
	{
	public IEnumerable<dynamic> GetResultById(string id)
	{
	using (var conn = new SqlConnection(@"Server=localhost\sqlexpress;Database=XmlTest;Trusted_Connection=True"))
	{
	var rows = conn.Query("select id,name FROM people WHERE Id=@Id", new { Id = id });
	return rows;
	}
	}
	}
	}

view raw PersonController.cs hosted with ❤ by GitHub

Of course, eventually you may want to use a more complete ORM, like EntityFramework, but for quick prototyping, it doesn't get any simpler than this.

Vlookup - doing joins in excel

I've been doing some data analysis in excel (and watching somebody who really knows excel :) one of the useful functions I've learned is vlookup, which basically lets you do a join (like in SQL) ; you pass it the value to find, a range of cells over which to look, an index indicating which column to return and a boolean indicating whether to do exact or approximate matching (usually you pass false, to ask for exact matching, a behavior like SQL)

invoke-webrequest : wget for Windows

Many times you need to do a quick test that a web site or program works, or you want to download a web page within a script; in unix/linux, you'd probably use curl or wget ; Powershell (as of 3.0) has invoke-webrequest , which does a similar function.

Although it has many other options, the most basic invocation just takes a URL and it returns the 'output' of that request, as an object containing several fields; the Content field contains the actual information returned (the web page), so you can use something like this:

(Invoke-WebRequest http://okaram.com).Content.length

to find out its length, and could use String.Contains and such to verify that it returns the info you expected (so you could script a simpletest for your web app).

Other useful parameters include -OutFile to save the page to a file, and -UseDefaultCredentials to have the request use your windows credentials, in an intranet.