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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s