BEGIN
-- Define Variables
DECLARE @ListCol   VARCHAR (1000)
      , @Query     VARCHAR (2000)
      , @ErrorTask VARCHAR (100)
      , @RunDate   VARCHAR (100)

SET @ListCol   = ''
SET @Query	   = ''
SET @ErrorTask = 'DFT Load Credit Card'
SET @RunDate   = '2010-04-29 14:08:00'

-- Create dynamic column list given Error Task name
SELECT @ListCol = (SELECT DISTINCT a.name + ','
                     FROM load_errors b
                    cross apply error_details.nodes('//field') AS xmlrows(data)
                    cross apply (SELECT xmlrows.data.value('./@name[1]','varchar(120)') AS field_name 
                   ) AS a(name)
                    WHERE b.error_task = @ErrorTask
                      FOR XML PATH(''))
SET @ListCol = SUBSTRING(@ListCol, 1, LEN(@ListCol)-1)


-- Create dynamic query
SET @Query = '  
SELECT *
FROM (SELECT t.load_error_id
           , t.package_run_date
           , t.error_task
           , t.error_step
           , t.error_code
           , t.error_desc
           , f.name
           , f.value
        FROM load_errors t
       --= blow out the rows from the xml column
       cross apply error_details.nodes(''//field'') as xmlrows(data)
       --= convert the nodes to two columns, field: name and value 
       cross apply (select xmlrows.data.value(''./@name[1]'',''varchar(120)'') as field_name
                         , xmlrows.data.value(''./@value[1]'',''varchar(120)'') as field_value ) as f(name,value)
       WHERE t.error_task = ''' + @ErrorTask + '''
         AND t.package_run_date = ''' + @RunDate + '''
        ) inner_table
pivot (max(inner_table.value) 
       for inner_table.name in (' + @ListCol + ')
       ) pvtfname
'

-- Execute dynamic query
EXEC(@QUERY)
END