How To Pull Single Value From XML

SQL Server has a built in method for easily pulling one value from an XML. To do this you use the method called .value(). The .value method will run an XQuery against the XML specified in the query. This method is scalar, so it will only return 1 value. You cannot use this to return multiple values.

DECLARE	@XMLToParse  XML
 
-- Load the XML data in to a variable to work with.
-- This would typically be passed as a parameter to a stored procedure
SET	@XMLToParse =	'
						Baboon
					 '

SELECT  @XMLToParse.value('data(/Animal/AnimalName)[1]',
							'VARCHAR(MAX)') AS AnimalName

In the query above, you can see that you can just pass the xpath and the datatype that you want the output to be. This will pull that value out of the XML and put it in the specified format. You can see that after the xpath, there is a funny syntax [1]. This tells SQL Server to grab the first instance of the AnimalName node. You need to do this because the .value() method only returns one value and will not work with a repeating node.

Reference: https://msdn.microsoft.com/en-us/library/ms178030.aspx