XQuery fun with text node selector

I came across a puzzling bug this morning using the text() node selector.

Here’s an example of the problem, note its contrived and is only used to help highlight the behaviour that we are observing. Take the following snippet of XML, say we want to grab the first region value.

<person>
  <addresses>
    <address>
      <line1>100 Long Road</line1>
      <line2>Unit 3</line2>
      <locality>x</locality>
      <region />
    </address>
    <address>
      <line1>5 Smith St</line1>
      <line2 />
      <locality>y</locality>
      <region>somewhere</region>
    </address>
  </addresses>
</person>

Here’s a XQuery that would do it using the text() node selector (I know there’s other ways but bear with me);

//person/addresses/address/region/text()

You’ll find that the result is not what you might expect, in our case we want an empty string or a null. However what you get is somewhere! Oh snap!

What’s going on? Well the text() node selector returns text nodes, if the node is not defined then it will not return it. Makes sense when you think about it however it’d be nice if there was a bool argument that you could pass that would include nil nodes.

Anyway, a better way to perform the query would be to pull the node before obtaining the text. Like so;

//person/addresses/address/region[1]/text()

The point of this post is that you should be aware of how selectors work so that you do not return the wrong value.

Using apply with table valued functions

So I got asked the other day whether it was possible to call a table-valued function for each record returned from another table-valued function, in one statement.

It is possible, thanks to the apply operator - see MSDN for more.

So if we take a contrived example of a parent with children.

	use JB;

	IF OBJECT_ID (N'dbo.fnGetParent', N'TF') IS NOT NULL
	DROP FUNCTION dbo.fnGetParent;
	GO

	create function dbo.fnGetParent()
	returns @parent table
	(
		PersonId int primary key not null,
		FirstName nvarchar(255) not null,
		IsParent bit not null
	)
	as
	begin
	   
		insert into @parent values (1, 'Jim', 1)
		insert into @parent values (2, 'Sarah', 1)
		insert into @parent values (3, 'Fred', 0)
	   
		return
	end;
	go

	IF OBJECT_ID (N'dbo.fnGetKids', N'TF') IS NOT NULL
	DROP FUNCTION dbo.fnGetKids;
	GO

	create function dbo.fnGetKids (@parentId int)
	returns @child table
	(
		ChildId int primary key not null,
		ParentId int not null,
		FirstName nvarchar(255) not null
	)
	as
	begin
	   
		insert into @child values (1, 1, 'Zelda')
		insert into @child values (2, 2, 'Chris')
		insert into @child values (3, 1, 'Mandy')
	   
		delete from @child where ParentId <> @parentId
	   
		return
	end;
	go

If we try a simple query where we get all the parents and cross apply that with children…

	-- 
	select p.PersonId, p.FirstName, k.FirstName, k.ParentId
	from dbo.fnGetParent() p
	cross apply dbo.fnGetKids(p.Personid) k

we will get all records from left side that have records on right, as shown in the screen grab.

With that example in mind. If we now try the same query but instead use outer apply…

	-- 
	select p.PersonId, p.FirstName, k.FirstName, k.ParentId
	from dbo.fnGetParent() p
	outer apply dbo.fnGetKids(p.Personid) k

We now get all records from left regardless of whether they have records on right.

So there you have it. A very simple, easy to follow (imho) example showing how to use the apply operator with table valued functions.

I’m not going to go much more the apply operator as others have written better articles, so here’s Rob.

git Lost and Found

I got asked a git question the other day and thought I should blog my reply.

Is it safe to run this? git branch -f master origin/master

To which I replied: Yes, with a but.

##The But? Any local commits you have maybe orphaned.

##Why? What you need to know about that command is that if (and let’s assume it does) the branch master exists, then the -f switch will mean that the local branch gets reset to the remote branch, thus removing any local commits.

See the Options section under Branching in the manual, http://git-scm.com/docs/git-branch, for more.

##Let’s see what I mean

We will work with a local repository and two branches for this example.

E:\_scratch\gitlostfound> git init
Initialized empty Git repository in E:/_scratch/gitlostfound/.git/
E:\_scratch\gitlostfound [master]> "first test" | Out-File first.txt
E:\_scratch\gitlostfound [master]> git add . ; git commit -m "First file"
[master (root-commit) b5ee354] First file
 1 file changed, 0 insertions(+), 0 deletions(-)
 create mode 100644 first.txt
E:\_scratch\gitlostfound [master]> "second test" | Out-File second.txt
E:\_scratch\gitlostfound [master]> git add . ; git commit -m "Second file"
[master d986f3b] Second file
 1 file changed, 0 insertions(+), 0 deletions(-)
 create mode 100644 second.txt
E:\_scratch\gitlostfound [master]> git checkout -b somebranch
Switched to a new branch 'somebranch'
E:\_scratch\gitlostfound [somebranch]> "third test" | Out-File third.txt
E:\_scratch\gitlostfound [somebranch]> git add . ; git commit -m "Third file"
[somebranch f4203be] Third file
 1 file changed, 0 insertions(+), 0 deletions(-)
 create mode 100644 third.txt
E:\_scratch\gitlostfound [somebranch]> "fourth test" | Out-File fourth.txt
E:\_scratch\gitlostfound [somebranch]> git add . ; git commit -m "Fourth file"
[somebranch ef3ce58] Fourth file
 1 file changed, 0 insertions(+), 0 deletions(-)
 create mode 100644 fourth.txt
E:\_scratch\gitlostfound [somebranch]> git checkout master
Switched to branch 'master'
E:\_scratch\gitlostfound [master]> "fifth test" | Out-File fifth.txt
E:\_scratch\gitlostfound [master]> git add . ; git commit -m "Fifth file"
[master 693e3b1] Fifth file
 1 file changed, 0 insertions(+), 0 deletions(-)
 create mode 100644 fifth.txt
E:\_scratch\gitlostfound [master]> git log --graph --abbrev-commit --decorate --format=format:'%C(bold normal)%h%C(reset) - %C(bold cyan)%aD%C(reset) %C(bold green)(%ar)%C(reset)%C(bold yellow)%d%C(reset)%n'' %C(white)%s%C(reset) %C(dim white)- %an%C(reset)' --all
* 693e3b1 - Fri, 9 May 2014 10:23:52 +1000 (65 seconds ago) (HEAD, master)
| ' Fifth file - Jonathan Bourke
| * ef3ce58 - Fri, 9 May 2014 10:23:09 +1000 (2 minutes ago) (somebranch)
| | ' Fourth file - Jonathan Bourke
| * f4203be - Fri, 9 May 2014 10:22:52 +1000 (2 minutes ago)
|/  ' Third file - Jonathan Bourke
* d986f3b - Fri, 9 May 2014 10:21:02 +1000 (4 minutes ago)
| ' Second file - Jonathan Bourke
* b5ee354 - Fri, 9 May 2014 10:19:40 +1000 (5 minutes ago)
  ' First file - Jonathan Bourke
E:\_scratch\gitlostfound [master]>

So after all that what we have is a repository with two branches. Now if we run the branch reset command that was mentioned at this start of this post an interesting thing occurs. Let’s take a look…

E:\_scratch\gitlostfound [master]> git branch -f somebranch master
E:\_scratch\gitlostfound [master]> git log --graph --abbrev-commit --decorate --format=format:'%C(bold normal)%h%C(reset) - %C(bold cyan)%aD%C(reset) %C(bold green)(%ar)%C(reset)%C(bold yellow)%d%C(reset)%n'' %C(white)%s%C(reset) %C(dim white)- %an%C(reset)' --all
* 693e3b1 - Fri, 9 May 2014 10:23:52 +1000 (4 minutes ago) (HEAD, somebranch, master)
| ' Fifth file - Jonathan Bourke
* d986f3b - Fri, 9 May 2014 10:21:02 +1000 (7 minutes ago)
| ' Second file - Jonathan Bourke
* b5ee354 - Fri, 9 May 2014 10:19:40 +1000 (8 minutes ago)
  ' First file - Jonathan Bourke
E:\_scratch\gitlostfound [master]>

Where’d the work on somebranch go?

##How do you get them back?

First we need to switch back to somebranch. This is very important because if we perform the next steps in master then the history will show that the work was originally done there and not in somebranch.

E:\_scratch\gitlostfound [master]> git checkout somebranch
Switched to branch 'somebranch'
E:\_scratch\gitlostfound [somebranch]> git fsck --lost-found
Checking object directories: 100% (256/256), done.
dangling commit ef3ce583d09f7b438c06da450f33620043cd2ce6
E:\_scratch\gitlostfound [somebranch]>

Once you ascertain which commits to reapply, simply merge that commit:

E:\_scratch\gitlostfound [somebranch]> git merge ef3ce58
Merge made by the 'recursive' strategy.
 fourth.txt | Bin 0 -> 28 bytes
 third.txt  | Bin 0 -> 26 bytes
 2 files changed, 0 insertions(+), 0 deletions(-)
 create mode 100644 fourth.txt
 create mode 100644 third.txt
E:\_scratch\gitlostfound [somebranch]> git log --graph --abbrev-commit --decorate --format=format:'%C(bold normal)%h%C(reset) - %C(bold cyan)%aD%C(reset) %C(bold green)(%ar)%C(reset)%C(bold yellow)%d%C(reset)%n'' %C(white)%s%C(reset) %C(dim white)- %an%C(reset)' --all
*   9e45311 - Fri, 9 May 2014 10:29:19 +1000 (5 seconds ago) (HEAD, somebranch)
|\  ' Merge commit 'ef3ce58' - Jonathan Bourke
| * ef3ce58 - Fri, 9 May 2014 10:23:09 +1000 (6 minutes ago)
| | ' Fourth file - Jonathan Bourke
| * f4203be - Fri, 9 May 2014 10:22:52 +1000 (7 minutes ago)
| | ' Third file - Jonathan Bourke
* | 693e3b1 - Fri, 9 May 2014 10:23:52 +1000 (6 minutes ago) (master)
|/  ' Fifth file - Jonathan Bourke
* d986f3b - Fri, 9 May 2014 10:21:02 +1000 (8 minutes ago)
| ' Second file - Jonathan Bourke
* b5ee354 - Fri, 9 May 2014 10:19:40 +1000 (10 minutes ago)
  ' First file - Jonathan Bourke
E:\_scratch\gitlostfound [somebranch]>

Done! Of course this is a very simply example, your mileage may vary.

Renaming multiple files in Powershell

So I ran into an issue today where I was given some MSSQL database backup files that were prefixed and suffixed with values that were not appropriate for their final use. Instead of hand renaming each file I decided to try and flex my Powershell muscles… here’s the result

ls *.bak | foreach { rename-item $_ -newname ([regex]::Replace($_.name, "prefix(\w+)suffix", '$1', "IgnoreCase")) }

The first step is to select the files we want to rename. In this example it was done using the ls cmdlet with a file extension file. The results of this are then put through the foreach cmdlet. Which in this case has a function defined that each result is passed into which in turn calls the rename-item cmdlet.

Fun with MS SQL

This post contains a few TSQL tidbits, mainly for my future reference…

Bits and Pieces

A quick way of flipping the value of a bit field is to use the bitwise (^) operator, for example:

	UPDATE mytable
	SET mybitcol = mybitcol ^ 1

What this does is…

(0 ^ 1)
	0000
	0001
	----
	0001

(1 ^ 1)
	0001
	0001
	----
	0000

Nothing is Permanent

So you’ve heard of temporary tables in TSQL, but have you heard of temporary stored procedures? Just like with temp tables we simply prefix the name of the procedure with the hash (#) symbol.

	CREATE PROCEDURE #IamTemporary
	AS
	BEGIN
		PRINT 'All good things must come to an end'
	END

And when the session ends so does the life of the temp procedure. Want to make the procedure available outside the current session? Use double hashes (##)!

Finding this procedure is a little more tricky than usual. Let’s say you wanted to check if the procedure exists and drop it if it does. Normally you would simply query against sys.objects within your database, with temp procedures you need to query within tempdb, kinda makes sense huh but you what you might not be aware of is that the name is not what you think.

	SELECT name 
	FROM tempdb.sys.objects
	WHERE type = 'P' AND name LIKE '#IamTemporary%'
	
	-- returns
	
	name
	#IamTemporary____________________________________________________________________________________________________________00009464

See that? The name is suffixed for some reason. What could that reason be? Well if we open a new tab in SSMS and run the create procedure script again. Then run the above query to search tempdb.sys.objects we find that we get 2 records returned. Each has a slightly different suffix, which appears to be tied to the session.

So instead, to clean up the procedure for the current session we could do something like:

	IF OBJECT_ID('tempdb.dbo.#IamTemporary') IS NOT NULL
	BEGIN
		PRINT 'DROPPING'
		DROP PROCEDURE #IamTemporary
	END
	GO