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)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?