Ok i have this working however I still don't understand how to get all the values when my select returns more than 1 row. I assume I need a loop just not sure if there is a better way.

Here is what I have:

DECLARE @x XML;

SELECT @x =

r.XMLData

FROM RulesetGroups rg

INNER JOIN Rulesets rs on

rg.RulesetGroupID = rs.RulesetGroupID

INNER JOIN Rules r on

rs.RulesetID = r.RulesetID

WHERE r.RuleTypeID = 3 and r.XMLData.value('(/Screen/ScreenOption/@Name)[1]', 'varchar(255)') is not null;

-- select "Name"

WITH Num(i)

AS

(

SELECT 1

UNION ALL

SELECT i + 1

FROM Num

WHERE i < (SELECT @x.value('count(/Screen/ScreenOption/@Name)','varchar(255)') )

)

SELECT x.value('@Name[1]', 'varchar(20)')

FROM Num

CROSS APPLY @x.nodes('/Screen/ScreenOption[position()=sql:column("i")]') e(x);

My XML for one row. I will have hundreds of these though in different rows:

<Screen Title="">

<ScreenOption Sequence="1" Name="BTWidth" />

<ScreenOption Sequence="2" Name="BTHeight" />

</Screen>

Any help will be appreciated.