Here's a short example to query data like you've posted (please note that I added a few end tags to change it into a valid xml structure as per SQL requirements):

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)

*/