commentsxquery, text, text node, selector, mssqledit
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.
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.
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.
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…
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.
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
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.
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.
CREATEPROCEDURE#IamTemporaryASBEGINPRINT'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.
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: