How To Pull Single Value From XML

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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.

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

Leave a Comment

CommentLuv badge