declare @xml1 XML, @xml2 XML;
set @xml1 = '<Content>
<![CDATA[
<div id="contentcenter">
<p>Perform a <b>hardware</b> check with the utility to assure the quality of the system driver, etc., etc.</p>
</div>
]]>
</Content>';

set @xml2 = '<Content>
<div id="contentcenter">
<p>Perform a <b>hardware</b> check with the utility to assure the quality of the system driver, etc., etc.</p>
</div>
</Content>';
select @xml1, @xml2;

DECLARE @x XML
SET @x='<p>
<div id="content">
<a name="ada-content" />
<div id="browseBox">
	<div class="roundedBox_top-left" />
	<div class="roundedBox_top-right" />
  <div class="licenseTitle">
		<h1>
			<span id="_SE_FLD" _SE_FLD="tcm:Content/custom:Content/custom:Title[1]">Aboveground Storage Tank (AST) Permits (>1 Million Gallons Total)</span>
		</h1>
		<div class="licenseDescription">
			<span id="_SE_FLD" _SE_FLD="tcm:Content/custom:Content/custom:Description[1]">Minnesota Pollution Control Agency (MPCA)</span>
		</div>
	</div>
	<div class="roundedBox_bottom-buff" />
</div>
<div class="roundedBox_bottom-left" />
<div class="roundedBox_bottom-right" />
<div id="licenseDetail">
	<div id="licenseRightColumn">
	<h3 style="margin:0;line-height:100%;">Agency contact information:</h3>
</div>
</div>
</div>
</p>'

;WITH cte AS 
(
	SELECT @x.query('//span') col 
)
SELECT  c.value('.','varchar(200)') span_result
FROM    cte
CROSS APPLY col.nodes('span') T(c)

/* result set
Aboveground Storage Tank (AST) Permits (>1 Million Gallons Total)
Minnesota Pollution Control Agency (MPCA)
*/

DECLARE @y XML
SET @y = '
<h1>
  <p xmlns:tcm="http://www.tridion.com/ContentManager/5.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns="http://www.w3.org/1999/xhtml">
    Facilities that have more than one million gallons capacity must obtain an individual permit from the MPCA according to Minnesota Rules Chapter <a href="http://www.revisor.leg.state.mn.us/arule/7001/">7001</a>.4200-4250.
    
</br>
    
</br>
    Owners of Aboveground Storage Tanks larger than 1,100 gallons must notify the Minnesota Pollution Control Agency (MPCA) of the existence of these tanks. In addition, tank owners are required to notify the MPCA of change of product or change of status of ASTs.
  
</h1>
'
SELECT 
	y.data.value('p[1]', 'varchar(500)') as Fees
from @y.nodes('h1') y(data);

DECLARE @x XML
SET @x='  <p xmlns:tcm="http://www.tridion.com/ContentManager/5.0"  xmlns:xlink="http://www.w3.org/1999/xlink" xmlns="http://www.w3.org/1999/xhtml">
<div id="content">
<a name="ada-content" />
<div id="browseBox">
	<div class="roundedBox_top-left" />
	<div class="roundedBox_top-right" />
  <div class="licenseTitle">
		<h1>
			<span id="_SE_FLD" _SE_FLD="tcm:Content/custom:Content/custom:Title[1]">Aboveground Storage Tank (AST) Permits (>1 Million Gallons Total)</span>
		</h1>
		<div class="licenseDescription">
			<span id="_SE_FLD" _SE_FLD="tcm:Content/custom:Content/custom:Description[1]">Minnesota Pollution Control Agency (MPCA)</span>
		</div>
	</div>
	<div class="roundedBox_bottom-buff" />
</div>
<div class="roundedBox_bottom-left" />
<div class="roundedBox_bottom-right" />
<div id="licenseDetail">
	<div id="licenseRightColumn">
	<h3 style="margin:0;line-height:100%;">Agency contact information:</h3>
</div>
</div>
</div>
</p>'

;WITH cte AS 
(
	SELECT @x.query('
	 declare default element namespace "http://www.w3.org/1999/xhtml";
	 //span') col 
)
SELECT  c.value('.','varchar(200)') span_result
FROM    cte
CROSS APPLY col.nodes('declare default element namespace "http://www.w3.org/1999/xhtml"; span') T(c)