How to parse XHTML node value with TSQL?
You can extract the node text with XML methods as long as the value is well-formed XML.
If the XHTML value is a parameter or variable:
DECLARE @XHTML xml = N'&lt;SPAN style=&quot;WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; FLOAT: none; COLOR: rgb(0,0,0); TEXT-ALIGN: left; FONT: 10px verdana; DISPLAY: inline !important; LETTER-SPACING: normal; TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px&quot;&gt;My Node Value&lt;/SPAN&gt;'
SELECT @XHTML.value('.','varchar(100)');

If the XHTML value is in a table column:
SELECT YourColumn.value('.','varchar(100)')
FROM dbo.YourTable;

The node paths can be adjusted if the SPAN element is not the root node.