Get XML Inner Text

A scalar function that extracts the inner text of a named tag from a simple XML string using substring math.

Published Mar 18, 2008

A quick-and-dirty way to pull the inner text out of a simple XML string when you do not want to bring in full XML parsing. Give it the XML and the tag name, and it uses CHARINDEX math to find the opening and closing tags and return whatever sits between them. It is intentionally simple - it works for flat markup without attributes or nested tags of the same name.

Example: SELECT dbo.GetXMLAttribute('OK', 'status') -- returns OK

ALTER FUNCTION GetXMLAttribute 
(
	@XML VarChar(8000),
	@Tag VarChar(250)
)
RETURNS VarChar(8000) 
AS
BEGIN
	DECLARE @BTag VarChar(250)
	DECLARE @ETag VarChar(250)
	DECLARE @SPos INT
	DECLARE @EPos INT
	DECLARE @ALen INT
	DECLARE @BTagLen INT

	SET @BTag = '<' + @Tag + '>'
	SET @ETag = '</' + @Tag + '>'
	SET @SPos = CharIndex(@BTag, @XML)
	SET @EPos = CharIndex(@ETag, @XML)

	IF @SPos > 0 AND @EPos > 0
	BEGIN--IF
		SET @BTagLen = LEN(@BTag)
		SET @ALen = (@EPos - @SPos) - @BTagLen
		RETURN SubString(@XML, @SPos + @BTagLen, @ALen)
	END--IF

	RETURN NULL
END
GO