Pulling the value from a tag in an XML data type using T-SQL

If you need to extract the data from an XML data type column to be used as part of query, and you need it to be a usable data type in MS-SQL, you can use the Value() method.  Using the Value method, we can extract the data contained within an XML tag as a SQL Data Type. The Value() method takes two arguments:

XQuery and SQLType

The following returns the value stored in the second <SSID> tag as a VarChar(50):

— First we create an XML variable to store the data that we’ll use for this example
DECLARE @x xml
SET @x =      ‘<NETWORKS>
<SSID> Wompsters University </SSID>
<SSID> Wompsters Inc </SSID>
</NETWORKS>’

— Select the second SSID value and specify that we’d like to return it as a VarChar(50), (Keep in mind the <SSID> position starts at 1, not 0)
SELECT @x.value(‘(//NETWORKS/SSID)[2]’, ‘varchar(50)’);

— This will return the VarChar “Wompsters Inc”, which you could use like a normal String in any SQL query

Benn McGuire
QA Test Engineer
ImageSource, Inc.

What Johnny Cash, stolen cars & software development have in common

“Now the headlights they was another sight
We had two on the left and one on the right
But when we pulled out the switch all three of ‘em come on.”
– Johnny Cash
“One Piece at a Time”

Many years ago, Johnny Cash sang a song called “One Piece at a Time”, in which he describes an automobile assembly plant worker stealing parts and pieces of various automobiles and assembling them into a very distinctive, one-of-a-kind car.

Given the nature of software, the essence of which is some form of code, building software is somewhat like putting that car together.  Technology evolves over time, operating systems change, and new tools all contribute to the complex process of building an application.  Code is pieced together in files and modules, and the output of the code in the form of log files and/or visual display on a monitor are the effects of the code.  When building an engine, putting on the heads and bolting up the crankshaft before attaching the pistons and connecting rods isn’t recommended.  Similarly, software designers aren’t always able to see all the parts until there is a basic framework constructed, and limitations of the system come to light.  Re-designing components and restructuring development schedules are not uncommon. Continue reading