July 2013

SQL Server XQuery: .value() method

Back to XQuery; the third method I wanted to discuss in this series is the .value() method. Like the .query() method, .value() bridges the SQL Server database engines and XQuery engines by extracting information from an XML document.  The difference is that while .query() returns XML, .value() translates the specific information requested into a scalar SQL value.  For example:

DECLARE @T TABLE ( ID INT, x XML )

INSERT  INTO @T
        ( ID, x )
VALUES  ( 1, '<root><plant><fruit>tomato</fruit></plant></root>' )
,       ( 2, '<root><plant><fruit>banana</fruit></plant></root>' )
,       ( 3, '<root><plant><vegetable>tomato</vegetable></plant></root>' )

SELECT ID, x.value('(.)[1]', 'varchar(max)')
FROM @T t

Note that the .value() method has two parameters, an XQuery statement and a string representing the expected SQL Server type for the scalar value returned.  The results are as follows:

image

So, what is the XQuery statement requesting?  The axis is referring to the self-node of the XML fragment (and remember that in SQL Server’s implementation of XQuery, the query begins at the top of the document), there is no node test (so any node will satisfy the test), and the predicate is referring to the first position [1] of the path.  The .value() function returns the value found at that location, and converts it to a sql value of type varchar(max).  So what happens if there’s more than one value at a given XPath?

INSERT  INTO @T
        ( ID, x )
VALUES    ( 4, '<root><plant><fruit>tomato</fruit><fruit>banana</fruit><vegetable>tomato</vegetable></plant></root>' )


SELECT ID, x.value('(.)[1]', 'varchar(max)')
FROM @T t

The above script inserts a slightly more complex XML document than used before; there are now multiple nodes under the <plant> node (in addition to the three rows we already have); the output looks like:

image 

Note how row 4 has a single string that represents all of the values of the XML document.  Without a specific XQuery, the value() function returns the values that satisfy the request; in this case, all of them.  In the case where the XQuery cannot be satisfied, .value() will return a NULL value to the SQL Server engine, as illustrated by the following code examples:

SELECT ID, x.value('(//plant/fruit)[1]', 'varchar(max)')
FROM @T t 

SELECT ID, x.value('(//plant/fruit)[2]', 'varchar(max)')
FROM @T t 

The output should look like:

image

In the first query, we are looking for an axis of any node in the document, a node test of fruit under the <plant> node, and a positional predicate of 1.  Rows 1,2, and 4 satisfy this query; the XML document in row 3 doesn’t have a <fruit node>, and thus returns a NULL.  The second query is similar in that several of the XML documents in our table don’t satisfy the query; only the document in row 4 has node in the second [2] position of the <fruit> node.

Summary

Key takeaways:

  1. The .value() function allows you to specify an XQuery statement against an XML datatype looking for a specific value, and
  2. that returned value is cast to a SQL datatype.

Next up: .nodes()

my amazing life

I don’t often talk about personal stuff on this blog (it’s CODEgumbo; not LIFEgumbo), but a lot’s happened in the last few weeks, so I thought I’d share a bit.  Besides, I was on a roll for blogging for a bit there, and then suddenly stopped; I figured I should at least attempt to explain what happened Smile.  So let me explain in chronological order (with a few flashbacks) what’s been going on with me lately.

I’m a dad, again….

Evan Stuart Ainsworth was born on June 25, 2013.  It’s been 14 years since I’ve had a baby in the house, so it’s a complete reboot.  I’ve been totally unprepared for the joys and trials of having a baby; I kept thinking it was going to be easy, but I guess I forgot how worn out you feel all the time, and I’m just the dad.  I don’t understand the sheer strength my wife has; the kid eats all the time.  And when he’s not eating, he’s either happy or mad; my nickname for him is the Hulk (“don’t make me angry”).

Before I go too much further with the story of Evan’s arrival, I should pause for a second and say how grateful I am for my two daughters (Isabel, 16, and Grace, 14); their world just got a little weirder, and they’ve stepped in and accepted it reasonably well.  Blended families are tough, and I appreciate them for trying to balance their relationship with me, their stepmother, and their new little brother, as well as building a relationship with their mom (and her new family).

Anyway, while the birth of any kid is big life change, Evan is special.  He was actually supposed to be an induced birth, so we were at the hospital when labor started.  Betsy (my wife) was all hooked up to monitors, and labor was progressing when things went awry; during some of the contractions, Betsy suffered a condition known as a placental abruption, and Evan went into shock in utero.  Within minutes, Betsy underwent an emergency C-section, and Evan was out within 15-30 minutes of the incident.

Betsy and I spent two weeks hanging out at the NICU, waiting on our little boy to get well; it wasn’t comfortable.  Lots of stress, nowhere to really go, and too little time in between feedings for either of us to leave.  Thankfully, Evan’s condition was relatively minor compared to many babies that spend time in the NICU, but it was still scary.  When I say my prayers, I give thanks for the doctors and midwives at the hospital; without them, I don’t think either Betsy or Evan would be home with me today.  After I give thanks, I then turn my thoughts toward the parents and families of children in the NICU.

I say all of this to explain that even while it’s tough coping to a new baby, I just laugh when I hear him cry.  Life is a blessing, even in the everyday struggles.

I survived another trip around the sun

As of July 5, 2013, I am now twice-21.  I’m a little slower, a little grayer, and happier than I’ve ever been.  42 is a lot more awesome than I thought it would be when I was 21.  Besides, kids are supposed to keep you young, and if I keep having them, I should live forever.

I celebrated a second year of marriage

While I don’t share a lot about my personal life on my blog, I REALLY don’t talk a lot about my wife.   As I pointed out above, this is my second marriage; my ex-wife and I separated in November 2009 after 14 years of marriage.  If you go back and search older posts on here, you’ll find a few references to my original marriage scattered about; I try not to dwell on them too much, because I don’t really want to revisit those feelings.  Let me just say that I spent a lot of my adult life trying to be happy and make someone love me, and I was only marginally successful.

After my divorce was final, I dated a few women and thought I would enjoy being single. And then I met Betsy.  My wife is an amazing woman, and I don’t think I tell her that often enough.  She loves me for being me, and its easy to love her.  I’m happy every day; love is easy with the right person.  At times it seems as though I just met her a week ago; at times, it seems as though life began when I met her.  Enough mushy stuff by me; sing along with the Proclaimers.  It’ll make you feel good.

And now back to our show

Hopefully, I’ll get back on track with technical blogging now that I’ve had some time to adjust to all the recent life events.  Thanks for reading.