--===== Create and populate the Tally table on the fly
 SELECT TOP 11000 --equates to more than 30 years of dates
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC

drop table #SourceTable
 SELECT TOP 1000000
        RowNum = IDENTITY(INT,1,1),
        u.Parameters
   INTO #SourceTable 
   FROM (--===== Same two lines of data repeated  
          SELECT CAST('Subject=Drilling&Category=Drill Header&Status=NO'+
'&IPPDM_Count=94356&Well_Count=84353&Zone_Status=NO'+ 
'&Prod_String_Count=95249&R_Master_W_Count=94353'+
'&BeginDate=2/3/2008&EndDate=2/5/2008' AS VARCHAR(8000)) AS Parameters
          UNION ALL
          SELECT 'Subject=Zone&Category=Zone Header&Status=YES'+
'&IPPDM_Count=94356&Well_Count=94356&Zone_Status=NO'+
'&Prod_String_Count=95249&R_Master_W_Count=94356&AddlZoneInterest=107674') u,
        
        Master.sys.All_Columns ac1,
        Master.sys.All_Columns ac2

  ALTER TABLE #SourceTable
    ADD PRIMARY KEY CLUSTERED (RowNum)

use staging
go
SET ANSI_WARNINGS OFF
SET STATISTICS TIME ON
go
--===== Split the data into an EAV (Entity and Value) cte and reassemble as rows containing the desired columns
;WITH
cteFirstSplit AS
(--==== Splits data on the "&" character
 SELECT h.RowNum, 
        ColNum = (ROW_NUMBER() OVER (ORDER BY Parameters)-1)%10,
        ColVal = SUBSTRING(h.Parameters, t.N+1, CHARINDEX('&', h.Parameters + '&', t.N+1)-t.N-1)
   FROM dbo.Tally t WITH (NOLOCK)
  RIGHT OUTER JOIN --Necessary in case Parameters is NULL
        SourceTable h 
     ON SUBSTRING(h.Parameters, t.N, 1) = '='
	--AND t.N < CHARINDEX('Zone_',h.Parameters)  
    AND t.N < len(h.Parameters) 
	--WHERE RowNum > 900000
	--WHERE RowNum <= 10000
)
 SELECT RowNum, 
        MAX(CASE WHEN ColNum = 0 THEN ColVal ELSE NULL END) AS Subject,
        MAX(CASE WHEN ColNum = 1 THEN ColVal ELSE NULL END) AS Category,
        MAX(CASE WHEN ColNum = 2 THEN ColVal ELSE NULL END) AS Status,
        MAX(CASE WHEN ColNum = 3 THEN ColVal ELSE NULL END) AS IPPDM_Count,
        MAX(CASE WHEN ColNum = 4 THEN ColVal ELSE NULL END) AS Well_Count/*,
        MAX(CASE WHEN ColNum = 5 THEN ColVal ELSE NULL END) AS Zone_Status,
        MAX(CASE WHEN ColNum = 6 THEN ColVal ELSE NULL END) AS Prod_String_Count,
        MAX(CASE WHEN ColNum = 7 THEN ColVal ELSE NULL END) AS R_Master_W_Count,
        MAX(CASE WHEN ColNum = 8 THEN ColVal ELSE NULL END) AS BeginDate,
        MAX(CASE WHEN ColNum = 9 THEN ColVal ELSE NULL END) AS EndDate */
   INTO #Results
   FROM cteFirstSplit 
  GROUP BY RowNum
print @@ROWCOUNT
SET STATISTICS TIME OFF
go

use staging
go
SET ANSI_WARNINGS ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
SET QUOTED_IDENTIFIER ON
go
declare @xml XML
set @xml = (
   select cast('<parm RowNum="'+ cast(rowNum as varchar(12))+ '" '+ 
        replace(replace(Parameters,'=','="'),'&','" ')+ '"/>' as XML) as data   
	from SourceTable
	--where RowNum > 900000
	-- where RowNum <= 10000
	for xml auto, type)

select 	D.parm.value('@RowNum','int') as RowNum, 
	D.parm.value('@Subject','varchar(255)') as [Subject],
	D.parm.value('@Category','varchar(255)') as Category,
	D.parm.value('@Status','varchar(255)') as [Status],
	D.parm.value('@IPPDM_Count','varchar(255)') as IPPDM_Count,
	D.parm.value('@Well_Count','varchar(255)') as Well_Count /*,
	D.parm.value('@Zone_Status','varchar(255)') as Zone_Status,
	D.parm.value('@Prod_String_Count','varchar(255)') as Prod_String_Count,
	D.parm.value('@R_Master_W_Count','varchar(255)') as R_Master_W_Count,
	D.parm.value('@BeginDate','varchar(255)') as BeginDate,
	D.parm.value('@EndDate','varchar(255)') as EndDate */
into #results
from @xml.nodes('/SourceTable/data/parm') as D(parm)
go
SET STATISTICS TIME OFF
go

-- this doesn't work
select D.parm ... into #results
from (select cast(...) as XML) as data 
   from SourceTable 
   for xml auto, type).nodes('/SourceTable/data/parm') as D(parm)

-- this doesn't work either probably because it's an attribute collection rather than an element collection
select * 
from @xml.nodes('/SourceTable/data/parm/@*') as D(RowNum,Subject,Category,...)

drop table #Results
; with a as (select RowNum, cast('<parm '+ replace(replace(Parameters,'=','="'),'&','" ')+ '"/>' as xml) as Xml from #SourceTable)
select RowNum,
    Xml.value('(parm[1]/@Subject)[1]', 'varchar(255)') as Subject,
    Xml.value('(parm[1]/@Category)[1]', 'varchar(255)') as Category,
    Xml.value('(parm[1]/@Status)[1]', 'varchar(255)') as Status,
    Xml.value('(parm[1]/@IPPDM_Count)[1]', 'varchar(255)') as IPPDM_Count,
    Xml.value('(parm[1]/@Well_Count)[1]', 'varchar(255)') as Well_Count
into #Results
from a

SELECT distinct 
	ColName = 
		substring(
			h.Parameters, 
			t.N - (charindex('&', reverse('&' + left(h.Parameters, t.N - 1))) - 1), 
			charindex('&', reverse('&' + left(h.Parameters, t.N- 1))) - 1
		)
FROM dbo.Tally t WITH (NOLOCK)
RIGHT OUTER JOIN --Necessary in case Parameters is NULL
#SourceTable h 
ON 
	SUBSTRING(h.Parameters, t.N, 1) = '='
	AND t.N < len(h.Parameters)