You probably want to split this into two different exercises. First extract the column name and values, and then use a dynamic pivot table to rebuild your rows.

The Dynamic pivot technique is described here[/url].

In order to pull the data you want to pivot, you're looking at a somewhat different XML extraction:

create table #temp

(

id int identity (1,1)

,FieldSet XML

)

INSERT INTO #temp

VALUES ('<Fields> <Field Key="Column1" value="value1" > </Field> <Field Key="Column2" value="value2"> </Field> </Fields>')

,('<Fields> <Field Key="Column3" value="value3" > </Field> <Field Key="Column4" value="value4"> </Field> </Fields>')

,('<Fields> <Field Key="Column1" value="value1" ></Field>

<Field Key="Column2" value="value2"> </Field>

<Field Key="Column3" value="value3" > </Field>

<Field Key="Column4" value="value4"> </Field>

</Fields>')

,('<Fields> <Field Key="Column1" value="value1" > </Field>

<Field Key="Column2" value="value2"> </Field>

<Field Key="Column3" value="value3" > </Field>

<Field Key="Column4" value="value4"> </Field>

<Field Key="Column5" value="value5" > </Field>

<Field Key="Column6" value="value6"> </Field>

<Field Key="Column7" value="value7" > </Field>

<Field Key="Column8" value="value8"> </Field>

</Fields>')

--actual query starts here

select ID, F.value ('(@Key)[1]', 'varchar(20)') ColumnName, F.value ('(@value)[1]', 'varchar(20)') ColumnValue

into #temp2

from #temp cross apply fieldset.nodes('/Fields/Field') g(F)