Link to home
Start Free TrialLog in
Avatar of Robb Hill
Robb HillFlag for United States of America

asked on

Process of Converting XML files to Database Records - Recursion

Please help clean this process up.  I am getting errors that my TempAR is in the database but It never lets me drop it.  Sais it doesnt exist or I dont have permission.  This didnt start happening until I put the if else logic in place along with the Begin End statements.

Hopefully cleaning this up can add to clarity.  Here is what I have so far.

High level this reads an xml file in from the drive....builds an initial insert statement into the database using the first file it reads which creates column names.

Going forward I only want to insert the values...but again they need to match what the columns would be.

In each method I do the ame insert to an attachments table.




USE SharePoint2007Archive
BEGIN

RECONFIGURE -- To update the currently configured value for advanced options.
EXEC sp_configure 'xp_cmdshell', 1  -- To enable the feature.
RECONFIGURE -- To update the currently configured value for this feature.


CREATE TABLE #tmp(excelFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /a-d /B E:\AR';

declare @fileName varchar(100)

Declare @fileData  XML
DECLARE @ReqID nvarchar(50)

DECLARE @ParmDefinition nvarchar(500); 
DECLARE @SQLString nvarchar(MAX);

DECLARE @ProjectXML XML;

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

DECLARE @Counter INT;
SET @Counter = 1;

SET NOCOUNT ON

While (Select Count(*) From #tmp where excelFileName is not null) > 0
	Begin
	    SET @Counter = @Counter + 1
		Select Top 1 @fileName = excelFileName From #tmp

		print @fileName
		set @SQLString = 'Select @fileDataOUT =BulkColumn from OpenRowSet(Bulk ''E:\AR\' +  @fileName + ''',Single_blob) x;'

		SET @ParmDefinition = N'@fileDataOUT XML OUTPUT';  

		EXECUTE sp_executesql @SQLString,
						      @ParmDefinition,
						      @fileDataOUT = @fileData OUTPUT;

SET @ProjectXML = @fileData

--Store RequestID as PK
		SELECT @ReqID = xData.Col.value('*:RequestID[1]','nvarchar(50)')
		from @ProjectXML.nodes('/*:AppropriationRequestForm/*:RequestHeader') AS xData(Col)-- this is the xpath to the individual records we want to extract							   

--get rest of data elements 


if @Counter = 1
BEGIN


SELECT

n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
n.value('text()[1]','NVARCHAR(MAX)') AS Value 
INTO #TempAR
FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
Where n.value('local-name(.)','VARCHAR(100)')
not in
(
	'root',
	'AppropriationRequestForm',
	'RequestHeader',
	'OperationalBenefit',
	'ProjectSummary',
	'FinancialBenefits',
	'RIMIImpulseGroup',
	'Summary',
	'Authorization',
	'SiteAuthorization',
	'ProjectManagerGroup',
	'ProjectManager2Group',
	'ProjectManager3Group',
	'ProjectManager4Group',
	'ProjectManager5Group',
	'TechnicalManagerGroup',
	'OperationControllerGroup',
	'SalesManagerGroup',
	'VPSalesGroup',
	'SiteManagerGroup',
	'SalesManagerSelector',
	'Person',
	'VPSalesSelector',
	'ProjectManager1Selector',
	'ProjectManager2Selector',
	'ProjectManager3Selector',
	'ProjectManager4Selector',
	'ProjectManager5Selector',
	'DepartmentAuthorization',
	'DepartmentApprover1Group',
	'DepartmentApprover2Group',
	'DepartmentApprover3Group',
	'DepartmentFPAReviewerGroup',
	'SpecialAuthorization',
	'ITReviewerGroup',
	'ITDirectorApprovalGroup',
	'DivisionAuthorization',
	'DivisionControllerGroup',
	'DivisionOperationsGroup',
	'FPAReviewerGroup',
	'DivisionVPGroup',
	'CorporateAuthorization',
	'CreditDirectorGroup',
	'TreasuryFinanceGroup',
	'AssistantTreasurerGroup',
	'TreasuryFinanceGroup',
	'VPTreasuryGroup',
	'AddtlCorpApproverGroup',
	'VPFinanceGroup',
	'CFOGroup',
	'COOApprovalGroup',
	'AddtCorpApproverSelector',
	'CEOAuthorization',
	'FormControl',
	'AttachmentsTable',
	'Documents')	




select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name) 
                    from #TempAR
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 
'SELECT ' + @cols + ' INTO tabletest from 
             (
              select Name, Value
			  from #TempAR
            ) x
            pivot 
            (
                max(Value)
                for Name in (' + @cols + ')
            ) p '

execute(@query);



Insert into SharePoint2007Archive.dbo.FileAttachments
		(FormName,RequestID,[File])
		select
		   'AR', 
		   @ReqID RequestID,
		   xData.Col.value('*:Documents[1]','nvarchar(MAX)') Document
		from @fileData.nodes('/*:AppropriationRequestForm/*:AttachmentsTable') AS xData(Col)
		WHERE DATALENGTH(xData.Col.value('*:Documents[1]','nvarchar(MAX)')) > 0

 Delete from #tmp Where excelFileName = @FileName

END
ELSE
BEGIN
drop table #TempAR

SELECT

n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
n.value('text()[1]','NVARCHAR(MAX)') AS Value 
INTO #TempAR
FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
Where n.value('local-name(.)','VARCHAR(100)')
not in
(
	'root',
	'AppropriationRequestForm',
	'RequestHeader',
	'OperationalBenefit',
	'ProjectSummary',
	'FinancialBenefits',
	'RIMIImpulseGroup',
	'Summary',
	'Authorization',
	'SiteAuthorization',
	'ProjectManagerGroup',
	'ProjectManager2Group',
	'ProjectManager3Group',
	'ProjectManager4Group',
	'ProjectManager5Group',
	'TechnicalManagerGroup',
	'OperationControllerGroup',
	'SalesManagerGroup',
	'VPSalesGroup',
	'SiteManagerGroup',
	'SalesManagerSelector',
	'Person',
	'VPSalesSelector',
	'ProjectManager1Selector',
	'ProjectManager2Selector',
	'ProjectManager3Selector',
	'ProjectManager4Selector',
	'ProjectManager5Selector',
	'DepartmentAuthorization',
	'DepartmentApprover1Group',
	'DepartmentApprover2Group',
	'DepartmentApprover3Group',
	'DepartmentFPAReviewerGroup',
	'SpecialAuthorization',
	'ITReviewerGroup',
	'ITDirectorApprovalGroup',
	'DivisionAuthorization',
	'DivisionControllerGroup',
	'DivisionOperationsGroup',
	'FPAReviewerGroup',
	'DivisionVPGroup',
	'CorporateAuthorization',
	'CreditDirectorGroup',
	'TreasuryFinanceGroup',
	'AssistantTreasurerGroup',
	'TreasuryFinanceGroup',
	'VPTreasuryGroup',
	'AddtlCorpApproverGroup',
	'VPFinanceGroup',
	'CFOGroup',
	'COOApprovalGroup',
	'AddtCorpApproverSelector',
	'CEOAuthorization',
	'FormControl',
	'AttachmentsTable',
	'Documents')	


	

select @cols = STUFF((SELECT QUOTENAME(Value) 
                    from #TempAR
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 
'SELECT ' + @cols + ' INTO tabletest from 
             (
              select Name, Value
			  from #TempAR
            ) x
            pivot 
            (
                max(Value)
                for Name in (' + @cols + ')
            ) p '

execute(@query);


Insert into SharePoint2007Archive.dbo.FileAttachments
		(FormName,RequestID,[File])
		select
		   'AR', 
		   @ReqID RequestID,
		   xData.Col.value('*:Documents[1]','nvarchar(MAX)') Document
		from @fileData.nodes('/*:AppropriationRequestForm/*:AttachmentsTable') AS xData(Col)
		WHERE DATALENGTH(xData.Col.value('*:Documents[1]','nvarchar(MAX)')) > 0

 Delete from #tmp Where excelFileName = @FileName

END








--DROP TABLE #tmp
--drop table #TempAR
--drop table tabletest
--drop table FileAttachments


--TRUNCATE TABLE Attachments
--USE {database-name};  
--GO  
---- Truncate the log by changing the database recovery model to SIMPLE.  
--ALTER DATABASE {database-name}
--SET RECOVERY SIMPLE;  
--GO  
---- Shrink the truncated log file to 1 MB.  
--DBCC SHRINKFILE ({database-file-name}, 1);  
--GO  
---- Reset the database recovery model.  
--ALTER DATABASE {database-name}
--SET RECOVERY FULL;  
--GO 

Open in new window

Avatar of ste5an
ste5an
Flag of Germany image

Your using SELECT..INTO #TempAR twice, so you have a problem with your scope. The easiest way to correct this is clean code.

Start by using a CURSOR instead of your loop. Use a separate procedure for the loop body.

p.s. take a closer look at your IF in the body. I don't think it is necessary at all.
Yes, I agree the IF is not necessary but that does not explain the error. Your code contains:
SET @Counter = 1;

SET NOCOUNT ON

While (Select Count(*) From #tmp where excelFileName is not null) > 0
	Begin
	    SET @Counter = @Counter + 1                             -- <===  @Counter = 2 in the first loop pass here 
		Select Top 1 @fileName = excelFileName From #tmp

		print @fileName
		set @SQLString = 'Select @fileDataOUT =BulkColumn from OpenRowSet(Bulk ''E:\AR\' +  @fileName + ''',Single_blob) x;'

		SET @ParmDefinition = N'@fileDataOUT XML OUTPUT';  

		EXECUTE sp_executesql @SQLString,
						      @ParmDefinition,
						      @fileDataOUT = @fileData OUTPUT;

SET @ProjectXML = @fileData

--Store RequestID as PK
		SELECT @ReqID = xData.Col.value('*:RequestID[1]','nvarchar(50)')
		from @ProjectXML.nodes('/*:AppropriationRequestForm/*:RequestHeader') AS xData(Col)-- this is the xpath to the individual records we want to extract							   

--get rest of data elements 


if @Counter = 1
BEGIN

Open in new window

so the  IF @Counter = 1  is never evaluated to true. Thus the ELSE part is executed first and the DROP command must fail.

You may also test whether the temp table exists by

IF OBJECT_ID('tempdb..#TempAR') IS NOT NULL
  DROP TABLE #TempAR
Avatar of Robb Hill

ASKER

I get the following:  

Msg 2714, Level 16, State 1, Line 170
There is already an object named '#TempAR' in the database.
Msg 134, Level 15, State 1, Line 235
The variable name '@cols' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Line 269
Incorrect syntax near 'OFF'.

I also refactored to handle the counter variable as that logic needed refactoring.

Yet I still get the same issue.

And notice the error is telling me the object is already in the database...if I try and manually drop I get this.
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#TempAR', because it does not exist or you do not have permission.


USE SharePoint2007Archive
BEGIN

RECONFIGURE -- To update the currently configured value for advanced options.
EXEC sp_configure 'xp_cmdshell', 1  -- To enable the feature.
RECONFIGURE -- To update the currently configured value for this feature.


CREATE TABLE #tmp(excelFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /a-d /B E:\AR';

declare @fileName varchar(100)

Declare @fileData  XML
DECLARE @ReqID nvarchar(50)

DECLARE @ParmDefinition nvarchar(500); 
DECLARE @SQLString nvarchar(MAX);

DECLARE @ProjectXML XML;

DECLARE @Counter INT;
SET @Counter = 1;

SET NOCOUNT ON

While (Select Count(*) From #tmp where excelFileName is not null) > 0
	Begin	   
		Select Top 1 @fileName = excelFileName From #tmp

		print @fileName

		set @SQLString = 'Select @fileDataOUT =BulkColumn from OpenRowSet(Bulk ''E:\AR\' +  @fileName + ''',Single_blob) x;'

		SET @ParmDefinition = N'@fileDataOUT XML OUTPUT';  

		EXECUTE sp_executesql @SQLString,
						      @ParmDefinition,
						      @fileDataOUT = @fileData OUTPUT;

SET @ProjectXML = @fileData

--Store RequestID as PK
		SELECT @ReqID = xData.Col.value('*:RequestID[1]','nvarchar(50)')
		from @ProjectXML.nodes('/*:AppropriationRequestForm/*:RequestHeader') AS xData(Col)-- this is the xpath to the individual records we want to extract							   

--get rest of data elements 


if @Counter = 1
BEGIN


SELECT

n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
n.value('text()[1]','NVARCHAR(MAX)') AS Value 
INTO #TempAR
FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
Where n.value('local-name(.)','VARCHAR(100)')
not in
(
	'root',
	'AppropriationRequestForm',
	'RequestHeader',
	'OperationalBenefit',
	'ProjectSummary',
	'FinancialBenefits',
	'RIMIImpulseGroup',
	'Summary',
	'Authorization',
	'SiteAuthorization',
	'ProjectManagerGroup',
	'ProjectManager2Group',
	'ProjectManager3Group',
	'ProjectManager4Group',
	'ProjectManager5Group',
	'TechnicalManagerGroup',
	'OperationControllerGroup',
	'SalesManagerGroup',
	'VPSalesGroup',
	'SiteManagerGroup',
	'SalesManagerSelector',
	'Person',
	'VPSalesSelector',
	'ProjectManager1Selector',
	'ProjectManager2Selector',
	'ProjectManager3Selector',
	'ProjectManager4Selector',
	'ProjectManager5Selector',
	'DepartmentAuthorization',
	'DepartmentApprover1Group',
	'DepartmentApprover2Group',
	'DepartmentApprover3Group',
	'DepartmentFPAReviewerGroup',
	'SpecialAuthorization',
	'ITReviewerGroup',
	'ITDirectorApprovalGroup',
	'DivisionAuthorization',
	'DivisionControllerGroup',
	'DivisionOperationsGroup',
	'FPAReviewerGroup',
	'DivisionVPGroup',
	'CorporateAuthorization',
	'CreditDirectorGroup',
	'TreasuryFinanceGroup',
	'AssistantTreasurerGroup',
	'TreasuryFinanceGroup',
	'VPTreasuryGroup',
	'AddtlCorpApproverGroup',
	'VPFinanceGroup',
	'CFOGroup',
	'COOApprovalGroup',
	'AddtCorpApproverSelector',
	'CEOAuthorization',
	'FormControl',
	'AttachmentsTable',
	'Documents')	


	DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name) 
                    from #TempAR
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 
'SELECT ' + @cols + ' INTO tabletest from 
             (
              select Name, Value
			  from #TempAR
            ) x
            pivot 
            (
                max(Value)
                for Name in (' + @cols + ')
            ) p '

execute(@query);



Insert into SharePoint2007Archive.dbo.FileAttachments
		(FormName,RequestID,[File])
		select
		   'AR', 
		   @ReqID RequestID,
		   xData.Col.value('*:Documents[1]','nvarchar(MAX)') Document
		from @fileData.nodes('/*:AppropriationRequestForm/*:AttachmentsTable') AS xData(Col)
		WHERE DATALENGTH(xData.Col.value('*:Documents[1]','nvarchar(MAX)')) > 0

Delete from #tmp Where excelFileName = @FileName
SET @Counter = @Counter + 1
END
ELSE
BEGIN
IF OBJECT_ID('tempdb..#TempAR') IS NOT NULL
  DROP TABLE #TempAR

SELECT

n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
n.value('text()[1]','NVARCHAR(MAX)') AS Value 
INTO #TempAR
FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
Where n.value('local-name(.)','VARCHAR(100)')
not in
(
	'root',
	'AppropriationRequestForm',
	'RequestHeader',
	'OperationalBenefit',
	'ProjectSummary',
	'FinancialBenefits',
	'RIMIImpulseGroup',
	'Summary',
	'Authorization',
	'SiteAuthorization',
	'ProjectManagerGroup',
	'ProjectManager2Group',
	'ProjectManager3Group',
	'ProjectManager4Group',
	'ProjectManager5Group',
	'TechnicalManagerGroup',
	'OperationControllerGroup',
	'SalesManagerGroup',
	'VPSalesGroup',
	'SiteManagerGroup',
	'SalesManagerSelector',
	'Person',
	'VPSalesSelector',
	'ProjectManager1Selector',
	'ProjectManager2Selector',
	'ProjectManager3Selector',
	'ProjectManager4Selector',
	'ProjectManager5Selector',
	'DepartmentAuthorization',
	'DepartmentApprover1Group',
	'DepartmentApprover2Group',
	'DepartmentApprover3Group',
	'DepartmentFPAReviewerGroup',
	'SpecialAuthorization',
	'ITReviewerGroup',
	'ITDirectorApprovalGroup',
	'DivisionAuthorization',
	'DivisionControllerGroup',
	'DivisionOperationsGroup',
	'FPAReviewerGroup',
	'DivisionVPGroup',
	'CorporateAuthorization',
	'CreditDirectorGroup',
	'TreasuryFinanceGroup',
	'AssistantTreasurerGroup',
	'TreasuryFinanceGroup',
	'VPTreasuryGroup',
	'AddtlCorpApproverGroup',
	'VPFinanceGroup',
	'CFOGroup',
	'COOApprovalGroup',
	'AddtCorpApproverSelector',
	'CEOAuthorization',
	'FormControl',
	'AttachmentsTable',
	'Documents')	


	DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT QUOTENAME(Value) 
                    from #TempAR
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 
'SELECT ' + @cols + ' INTO tabletest from 
             (
              select Name, Value
			  from #TempAR
            ) x
            pivot 
            (
                max(Value)
                for Name in (' + @cols + ')
            ) p '

execute(@query);


Insert into SharePoint2007Archive.dbo.FileAttachments
		(FormName,RequestID,[File])
		select
		   'AR', 
		   @ReqID RequestID,
		   xData.Col.value('*:Documents[1]','nvarchar(MAX)') Document
		from @fileData.nodes('/*:AppropriationRequestForm/*:AttachmentsTable') AS xData(Col)
		WHERE DATALENGTH(xData.Col.value('*:Documents[1]','nvarchar(MAX)')) > 0

Delete from #tmp Where excelFileName = @FileName

END

SET NOCOUNT OFF






--DROP TABLE #tmp
--drop table #TempAR
--drop table tabletest
--drop table FileAttachments





--TRUNCATE TABLE Attachments
--USE {database-name};  
--GO  
---- Truncate the log by changing the database recovery model to SIMPLE.  
--ALTER DATABASE {database-name}
--SET RECOVERY SIMPLE;  
--GO  
---- Shrink the truncated log file to 1 MB.  
--DBCC SHRINKFILE ({database-file-name}, 1);  
--GO  
---- Reset the database recovery model.  
--ALTER DATABASE {database-name}
--SET RECOVERY FULL;  
--GO 

Open in new window

As a sanity check this piece of code below works.  Here is the issue with it though.

1)  The body of this code need only run once in the while loop as the first file read in the loop will determine schema...I need all other files read which can be 10s of thousands to only insert values on the pivot.  
So with that being said in this state it will try and execute this same select over and over...obviouisly it fails because TempAR fails but if I was to drop temp ar it would just repeat the same file forever it seems.


I need it to move to next file...and do the select into using the value only versus name for the remainder of the loop.


USE SharePoint2007Archive

RECONFIGURE -- To update the currently configured value for advanced options.
EXEC sp_configure 'xp_cmdshell', 1  -- To enable the feature.
RECONFIGURE -- To update the currently configured value for this feature.


CREATE TABLE #tmp(excelFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /a-d /B E:\AR';

declare @fileName varchar(100)

Declare @fileData  XML
DECLARE @ReqID nvarchar(50)

DECLARE @ParmDefinition nvarchar(500); 
DECLARE @SQLString nvarchar(MAX);

DECLARE @ProjectXML XML;

DECLARE @Counter INT;

While (Select Count(*) From #tmp where excelFileName is not null) > 0
	BEGIN
		Select Top 1 @fileName = excelFileName From #tmp

		print @fileName

		set @SQLString = 'Select @fileDataOUT =BulkColumn from OpenRowSet(Bulk ''E:\AR\' +  @fileName + ''',Single_blob) x;'

		SET @ParmDefinition = N'@fileDataOUT XML OUTPUT';  

		EXECUTE sp_executesql @SQLString,
						      @ParmDefinition,
						      @fileDataOUT = @fileData OUTPUT;

SET @ProjectXML = @fileData

--Store RequestID as PK
		SELECT @ReqID = xData.Col.value('*:RequestID[1]','nvarchar(50)')
		from @ProjectXML.nodes('/*:AppropriationRequestForm/*:RequestHeader') AS xData(Col)-- this is the xpath to the individual records we want to extract							   

--get rest of data elements 



SELECT

n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
n.value('text()[1]','NVARCHAR(MAX)') AS Value 
INTO #TempAR
FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
Where n.value('local-name(.)','VARCHAR(100)')
not in
(
	'root',
	'AppropriationRequestForm',
	'RequestHeader',
	'OperationalBenefit',
	'ProjectSummary',
	'FinancialBenefits',
	'RIMIImpulseGroup',
	'Summary',
	'Authorization',
	'SiteAuthorization',
	'ProjectManagerGroup',
	'ProjectManager2Group',
	'ProjectManager3Group',
	'ProjectManager4Group',
	'ProjectManager5Group',
	'TechnicalManagerGroup',
	'OperationControllerGroup',
	'SalesManagerGroup',
	'VPSalesGroup',
	'SiteManagerGroup',
	'SalesManagerSelector',
	'Person',
	'VPSalesSelector',
	'ProjectManager1Selector',
	'ProjectManager2Selector',
	'ProjectManager3Selector',
	'ProjectManager4Selector',
	'ProjectManager5Selector',
	'DepartmentAuthorization',
	'DepartmentApprover1Group',
	'DepartmentApprover2Group',
	'DepartmentApprover3Group',
	'DepartmentFPAReviewerGroup',
	'SpecialAuthorization',
	'ITReviewerGroup',
	'ITDirectorApprovalGroup',
	'DivisionAuthorization',
	'DivisionControllerGroup',
	'DivisionOperationsGroup',
	'FPAReviewerGroup',
	'DivisionVPGroup',
	'CorporateAuthorization',
	'CreditDirectorGroup',
	'TreasuryFinanceGroup',
	'AssistantTreasurerGroup',
	'TreasuryFinanceGroup',
	'VPTreasuryGroup',
	'AddtlCorpApproverGroup',
	'VPFinanceGroup',
	'CFOGroup',
	'COOApprovalGroup',
	'AddtCorpApproverSelector',
	'CEOAuthorization',
	'FormControl',
	'AttachmentsTable',
	'Documents')	


	DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name) 
                    from #TempAR
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 
'SELECT ' + @cols + ' INTO tabletest from 
             (
              select Name, Value
			  from #TempAR
            ) x
            pivot 
            (
                max(Value)
                for Name in (' + @cols + ')
            ) p '

execute(@query);



Insert into SharePoint2007Archive.dbo.Attachments
		(RequestID,Form, Document)
		select		  
		   @ReqID RequestID,
		   'AR', 
		   xData.Col.value('*:Documents[1]','nvarchar(MAX)') Document
		from @fileData.nodes('/*:AppropriationRequestForm/*:AttachmentsTable') AS xData(Col)
		WHERE DATALENGTH(xData.Col.value('*:Documents[1]','nvarchar(MAX)')) > 0

Delete from #tmp Where excelFileName = @FileName
SET @Counter = @Counter + 1

END



--drop table tabletest
--DROP TABLE #tmp
--drop table #TempAR
--Truncate table Attachments

Open in new window

Please look at this:

Now I am getting the following error:
Msg 156, Level 15, State 1, Line 54
Incorrect syntax near the keyword 'SELECT'.
Msg 2714, Level 16, State 1, Line 159
There is already an object named '#TempAR' in the database.


USE SharePoint2007Archive

RECONFIGURE -- To update the currently configured value for advanced options.
EXEC sp_configure 'xp_cmdshell', 1  -- To enable the feature.
RECONFIGURE -- To update the currently configured value for this feature.


CREATE TABLE #tmp(excelFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /a-d /B E:\AR';

DECLARE @fileName varchar(100)

DECLARE @fileData  XML
DECLARE @ReqID nvarchar(50)

DECLARE @ParmDefinition nvarchar(500); 
DECLARE @SQLString nvarchar(MAX);

DECLARE @ProjectXML XML;

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

DECLARE @Counter INT = 1;

While (Select Count(*) From #tmp where excelFileName is not null) > 0
	BEGIN
		if (@Counter = 1)			
				Select Top 1 @fileName = excelFileName From #tmp

				print @fileName

				set @SQLString = 'Select @fileDataOUT =BulkColumn from OpenRowSet(Bulk ''E:\AR\' +  @fileName + ''',Single_blob) x;'

				SET @ParmDefinition = N'@fileDataOUT XML OUTPUT';  

				EXECUTE sp_executesql @SQLString,
									  @ParmDefinition,
									  @fileDataOUT = @fileData OUTPUT;

				SET @ProjectXML = @fileData

				--Store RequestID as PK
						SELECT @ReqID = xData.Col.value('*:RequestID[1]','nvarchar(50)')
						from @ProjectXML.nodes('/*:AppropriationRequestForm/*:RequestHeader') AS xData(Col)-- this is the xpath to the individual records we want to extract							   

				--get rest of data elements 

																																																																																																																																		SELECT

				SELECT n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
				n.value('text()[1]','NVARCHAR(MAX)') AS Value 
				INTO #TempAR
				FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
				Where n.value('local-name(.)','VARCHAR(100)')
				not in
				(
					'root',
					'AppropriationRequestForm',
					'RequestHeader',
					'OperationalBenefit',
					'ProjectSummary',
					'FinancialBenefits',
					'RIMIImpulseGroup',
					'Summary',
					'Authorization',
					'SiteAuthorization',
					'ProjectManagerGroup',
					'ProjectManager2Group',
					'ProjectManager3Group',
					'ProjectManager4Group',
					'ProjectManager5Group',
					'TechnicalManagerGroup',
					'OperationControllerGroup',
					'SalesManagerGroup',
					'VPSalesGroup',
					'SiteManagerGroup',
					'SalesManagerSelector',
					'Person',
					'VPSalesSelector',
					'ProjectManager1Selector',
					'ProjectManager2Selector',
					'ProjectManager3Selector',
					'ProjectManager4Selector',
					'ProjectManager5Selector',
					'DepartmentAuthorization',
					'DepartmentApprover1Group',
					'DepartmentApprover2Group',
					'DepartmentApprover3Group',
					'DepartmentFPAReviewerGroup',
					'SpecialAuthorization',
					'ITReviewerGroup',
					'ITDirectorApprovalGroup',
					'DivisionAuthorization',
					'DivisionControllerGroup',
					'DivisionOperationsGroup',
					'FPAReviewerGroup',
					'DivisionVPGroup',
					'CorporateAuthorization',
					'CreditDirectorGroup',
					'TreasuryFinanceGroup',
					'AssistantTreasurerGroup',
					'TreasuryFinanceGroup',
					'VPTreasuryGroup',
					'AddtlCorpApproverGroup',
					'VPFinanceGroup',
					'CFOGroup',
					'COOApprovalGroup',
					'AddtCorpApproverSelector',
					'CEOAuthorization',
					'FormControl',
					'AttachmentsTable',
					'Documents')		

		

			    select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name) 
							from #TempAR
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)'),1,1,'')

			    set @query = 
		               'SELECT ' + @cols + ' INTO tabletest from 
					        (
					         select Name, Value
					          from #TempAR
					        ) x
					    pivot 
					        (
						     max(Value)
						     for Name in (' + @cols + ')
					        ) p '

				execute(@query);



				Insert into SharePoint2007Archive.dbo.Attachments
				(RequestID,Form, Document)
				select		  
					@ReqID RequestID,
					'AR', 
					xData.Col.value('*:Documents[1]','nvarchar(MAX)') Document
				from @fileData.nodes('/*:AppropriationRequestForm/*:AttachmentsTable') AS xData(Col)
				WHERE DATALENGTH(xData.Col.value('*:Documents[1]','nvarchar(MAX)')) > 0

				Delete from #tmp Where excelFileName = @FileName

				IF OBJECT_ID('tempdb..#TempAR') IS NOT NULL
					DROP TABLE #TempAR
		
		ELSE
				
				SELECT n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
				n.value('text()[1]','NVARCHAR(MAX)') AS Value 
				INTO #TempAR
				FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
				Where n.value('local-name(.)','VARCHAR(100)')
				not in
				(
					'root',
					'AppropriationRequestForm',
					'RequestHeader',
					'OperationalBenefit',
					'ProjectSummary',
					'FinancialBenefits',
					'RIMIImpulseGroup',
					'Summary',
					'Authorization',
					'SiteAuthorization',
					'ProjectManagerGroup',
					'ProjectManager2Group',
					'ProjectManager3Group',
					'ProjectManager4Group',
					'ProjectManager5Group',
					'TechnicalManagerGroup',
					'OperationControllerGroup',
					'SalesManagerGroup',
					'VPSalesGroup',
					'SiteManagerGroup',
					'SalesManagerSelector',
					'Person',
					'VPSalesSelector',
					'ProjectManager1Selector',
					'ProjectManager2Selector',
					'ProjectManager3Selector',
					'ProjectManager4Selector',
					'ProjectManager5Selector',
					'DepartmentAuthorization',
					'DepartmentApprover1Group',
					'DepartmentApprover2Group',
					'DepartmentApprover3Group',
					'DepartmentFPAReviewerGroup',
					'SpecialAuthorization',
					'ITReviewerGroup',
					'ITDirectorApprovalGroup',
					'DivisionAuthorization',
					'DivisionControllerGroup',
					'DivisionOperationsGroup',
					'FPAReviewerGroup',
					'DivisionVPGroup',
					'CorporateAuthorization',
					'CreditDirectorGroup',
					'TreasuryFinanceGroup',
					'AssistantTreasurerGroup',
					'TreasuryFinanceGroup',
					'VPTreasuryGroup',
					'AddtlCorpApproverGroup',
					'VPFinanceGroup',
					'CFOGroup',
					'COOApprovalGroup',
					'AddtCorpApproverSelector',
					'CEOAuthorization',
					'FormControl',
					'AttachmentsTable',
					'Documents')		

				select @cols = STUFF((SELECT QUOTENAME(Value) 
									from #TempAR
							FOR XML PATH(''), TYPE
							).value('.', 'NVARCHAR(MAX)') 
						,1,1,'')

				set @query = 
				'SELECT ' + @cols + ' INTO tabletest from 
							 (
							  select Name, Value
							  from #TempAR
							) x
							pivot 
							(
								max(Value)
								for Name in (' + @cols + ')
							) p '

				execute(@query);			

			    Insert into SharePoint2007Archive.dbo.Attachments
				(RequestID,Form, Document)
				select		  
					@ReqID RequestID,
					'AR', 
					xData.Col.value('*:Documents[1]','nvarchar(MAX)') Document
				from @fileData.nodes('/*:AppropriationRequestForm/*:AttachmentsTable') AS xData(Col)
				WHERE DATALENGTH(xData.Col.value('*:Documents[1]','nvarchar(MAX)')) > 0

			    Delete from #tmp Where excelFileName = @FileName

	END

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE tmp



--drop table tabletest
--DROP TABLE #tmp
--drop table #TempAR
--Truncate table Attachments

Open in new window

Why is refactoring so hard? Start with

-- USE SharePoint2007Archive;
-- GO

-- Should be only run once..
-- RECONFIGURE
-- EXEC sys.sp_configure 'xp_cmdshell', 1
-- RECONFIGURE
-- GO

DECLARE @NO_ERROR INT = 0;

CREATE TABLE #Files (
    FileName NVARCHAR(MAX)
);
DECLARE @FileName NVARCHAR(MAX);
DECLARE Files CURSOR FOR
    SELECT   F.FileName
    FROM     #Files F
    ORDER BY F.FileName;

INSERT INTO #Files
EXECUTE sys.xp_cmdshell 'dir /a-d /B E:\AR';

OPEN Files;
FETCH NEXT FROM Files
INTO @FileName;
WHILE ( @@FETCH_STATUS <> @NO_ERROR )
BEGIN
    EXECUTE dbo.yourSproc @FileName;
    FETCH NEXT FROM Files
    INTO @FileName;
END;

CLOSE Files;
DEALLOCATE Files;

Open in new window

And place your loops body into its own sproc.

btw, as I told you, remove your counter if (wink, wink) ...

	BEGIN
		if (@Counter = 1)			
				Select Top 1 @fileName = excelFileName From #tmp

Open in new window

There is no begin..end else begin..end; for your IF's body.
No need to declare cursor. The approach with #tmp table should also work.

Also the Stored Procedure will work when the code is exactly same for both parts. And this is not true here.

The rest is true - missing BEGIN-ENDs, duplicate variable declarations.
So declare all variables at the code beginning, use indentation which does make the code more readable (two spaces are better than two TABs) and use BEGIN - END properly. And think twice where to declare/delete the temp tables.

Also read about the sp_configure and RECONFIGURE.  It would need more GO commands.
pcelba....im not totally sure what you are suggesting...I have been working on the syntax and just keep getting more errors.


Is there a way to get the SQL part of this accomplished in one QUERY.  If so this would make this so much easier...

This is a query of an xml file and essentially the first pass just puts all the columns and and values in a query result.
So I pivot to get the rows as a the header for the purpose of making my table.
Then every time I iterate over a new xml file I only need the values of these columns inserted.

Can this be done in one query by adding some type of cte?
ste5an - your approach is clean..but I am still not sure how to build these two SQL statements into one proc or one select insert for that matter.

I am also not sure the second select works as it fails..but in a nutshell I am trying to do this.

Assume I get read one xml file on the first pass of the loop and I get the following:

RequestID    1234
BusinessName   Robbinc
StoreLocation     123 blvd


I need to pivot this dynamically so I have

requestid /  BusinessName  /  Store Location
1234         /  RobbInc             /  123 blvd


Then the next file is read .....

I need it to only insert the values pivoted as the columns are already in the table.


I think there must be a more elegant way to achieve what I am doing.

These xml files can be huge thus the reason I am doing this dynamically.    
And I will be doing them on other xml structures thus the reason I am doing dynamically as well.
Refactoring again....and the error still remains that #TempAR is in the database but its clearly not.  none of my messages that get created before this even runs are hit.  This does not happen until I create the else logic..this time based on the existance of the database that gets created in the first past.

USE SharePoint2007Archive

RECONFIGURE -- To update the currently configured value for advanced options.
EXEC sp_configure 'xp_cmdshell', 1  -- To enable the feature.
RECONFIGURE -- To update the currently configured value for this feature.


CREATE TABLE #tmp(excelFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /a-d /B E:\AR';

declare @fileName varchar(100)

Declare @fileData  XML
DECLARE @ReqID nvarchar(50)

DECLARE @ParmDefinition nvarchar(500); 
DECLARE @SQLString nvarchar(MAX);

DECLARE @ProjectXML XML;

DECLARE @cols NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

While (Select Count(*) From #tmp where excelFileName is not null) > 0
	BEGIN
		Select Top 1 @fileName = excelFileName From #tmp

		print @fileName

		set @SQLString = 'Select @fileDataOUT =BulkColumn from OpenRowSet(Bulk ''E:\AR\' +  @fileName + ''',Single_blob) x;'

		SET @ParmDefinition = N'@fileDataOUT XML OUTPUT';  

		EXECUTE sp_executesql @SQLString,
						      @ParmDefinition,
						      @fileDataOUT = @fileData OUTPUT;

SET @ProjectXML = @fileData

--Store RequestID as PK
		SELECT @ReqID = xData.Col.value('*:RequestID[1]','nvarchar(50)')
		from @ProjectXML.nodes('/*:AppropriationRequestForm/*:RequestHeader') AS xData(Col)-- this is the xpath to the individual records we want to extract							   

--get rest of data elements 

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'tabletest')
BEGIN
    PRINT 'Table Exists'
	SELECT n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
				n.value('text()[1]','NVARCHAR(MAX)') AS Value 
				INTO #TempAR
				FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
				Where n.value('local-name(.)','VARCHAR(100)')
				not in
				(
					'root',
					'AppropriationRequestForm',
					'RequestHeader',
					'OperationalBenefit',
					'ProjectSummary',
					'FinancialBenefits',
					'RIMIImpulseGroup',
					'Summary',
					'Authorization',
					'SiteAuthorization',
					'ProjectManagerGroup',
					'ProjectManager2Group',
					'ProjectManager3Group',
					'ProjectManager4Group',
					'ProjectManager5Group',
					'TechnicalManagerGroup',
					'OperationControllerGroup',
					'SalesManagerGroup',
					'VPSalesGroup',
					'SiteManagerGroup',
					'SalesManagerSelector',
					'Person',
					'VPSalesSelector',
					'ProjectManager1Selector',
					'ProjectManager2Selector',
					'ProjectManager3Selector',
					'ProjectManager4Selector',
					'ProjectManager5Selector',
					'DepartmentAuthorization',
					'DepartmentApprover1Group',
					'DepartmentApprover2Group',
					'DepartmentApprover3Group',
					'DepartmentFPAReviewerGroup',
					'SpecialAuthorization',
					'ITReviewerGroup',
					'ITDirectorApprovalGroup',
					'DivisionAuthorization',
					'DivisionControllerGroup',
					'DivisionOperationsGroup',
					'FPAReviewerGroup',
					'DivisionVPGroup',
					'CorporateAuthorization',
					'CreditDirectorGroup',
					'TreasuryFinanceGroup',
					'AssistantTreasurerGroup',
					'TreasuryFinanceGroup',
					'VPTreasuryGroup',
					'AddtlCorpApproverGroup',
					'VPFinanceGroup',
					'CFOGroup',
					'COOApprovalGroup',
					'AddtCorpApproverSelector',
					'CEOAuthorization',
					'FormControl',
					'AttachmentsTable',
					'Documents')		

	select @cols = STUFF((SELECT QUOTENAME(Value) 
									from #TempAR
							FOR XML PATH(''), TYPE
							).value('.', 'NVARCHAR(MAX)') 
						,1,1,'')

	set @query = 
				'SELECT ' + @cols + ' INTO tabletest from 
							 (
							  select Name, Value
							  from #TempAR
							) x
							pivot 
							(
								max(Value)
								for Name in (' + @cols + ')
							) p '

	execute(@query);	
END
ELSE
BEGIN
   PRINT 'Table Not Exists' 
   SELECT
n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
n.value('text()[1]','NVARCHAR(MAX)') AS Value 
INTO #TempAR
FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
Where n.value('local-name(.)','VARCHAR(100)')
not in
(
	'root',
	'AppropriationRequestForm',
	'RequestHeader',
	'OperationalBenefit',
	'ProjectSummary',
	'FinancialBenefits',
	'RIMIImpulseGroup',
	'Summary',
	'Authorization',
	'SiteAuthorization',
	'ProjectManagerGroup',
	'ProjectManager2Group',
	'ProjectManager3Group',
	'ProjectManager4Group',
	'ProjectManager5Group',
	'TechnicalManagerGroup',
	'OperationControllerGroup',
	'SalesManagerGroup',
	'VPSalesGroup',
	'SiteManagerGroup',
	'SalesManagerSelector',
	'Person',
	'VPSalesSelector',
	'ProjectManager1Selector',
	'ProjectManager2Selector',
	'ProjectManager3Selector',
	'ProjectManager4Selector',
	'ProjectManager5Selector',
	'DepartmentAuthorization',
	'DepartmentApprover1Group',
	'DepartmentApprover2Group',
	'DepartmentApprover3Group',
	'DepartmentFPAReviewerGroup',
	'SpecialAuthorization',
	'ITReviewerGroup',
	'ITDirectorApprovalGroup',
	'DivisionAuthorization',
	'DivisionControllerGroup',
	'DivisionOperationsGroup',
	'FPAReviewerGroup',
	'DivisionVPGroup',
	'CorporateAuthorization',
	'CreditDirectorGroup',
	'TreasuryFinanceGroup',
	'AssistantTreasurerGroup',
	'TreasuryFinanceGroup',
	'VPTreasuryGroup',
	'AddtlCorpApproverGroup',
	'VPFinanceGroup',
	'CFOGroup',
	'COOApprovalGroup',
	'AddtCorpApproverSelector',
	'CEOAuthorization',
	'FormControl',
	'AttachmentsTable',
	'Documents')

   select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name) 
                    from #TempAR
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

   set @query = 
'SELECT ' + @cols + ' INTO tabletest from 
             (
              select Name, Value
			  from #TempAR
            ) x
            pivot 
            (
                max(Value)
                for Name in (' + @cols + ')
            ) p '

   execute(@query);

END


Insert into SharePoint2007Archive.dbo.Attachments
		(RequestID,Form, Document)
		select		  
		   @ReqID RequestID,
		   'AR', 
		   xData.Col.value('*:Documents[1]','nvarchar(MAX)') Document
		from @fileData.nodes('/*:AppropriationRequestForm/*:AttachmentsTable') AS xData(Col)
		WHERE DATALENGTH(xData.Col.value('*:Documents[1]','nvarchar(MAX)')) > 0

IF OBJECT_ID('tempdb..#TempAR') IS NOT NULL
					DROP TABLE #TempAR

Delete from #tmp Where excelFileName = @FileName


END



--drop table tabletest
--DROP TABLE #tmp
--drop table #TempAR
--Truncate table Attachments

Open in new window

Well this works but crashes on 2nd insert......
I really have no idea how to get the 2nd scenario to insert.

I need to be able to match the table structure created .....and have an insert built from each of the next xml files.

Switching the pivot around did not work at all.

Some how I need to match......columns to rows and build an insert statement ......
Lets summarize it: You have some XML files which you would like to insert into some table.

The only insert I can see is  Insert into SharePoint2007Archive.dbo.Attachments

Then there is tabletest table creation but I don't see any usage for this table.

How do you ensure each insert into tabletest will provide columns compatible to given data types? If the tabletest has just one fixed structure (and this is expected on SQL server) then you don't need to create such table in SELECT command. How would you use the table which has structure different in each run? It is possible but highly unproductive.

To help we would need:
1) XML file samples
2) The tabletest expected structure
The use case behind this project does not require that I follow database best practices.  This will be run one time for many different xml structures which were defined in an old sharepoint site info  path forms.  Each folder I pull from I can guarantee the structure is the same.

With that being said the SQL I have provided is for one of these share point form libraries.  The data in the XML is sensative but I do not want to hand code anything specific to the form.  I want it to be dynamic so I can use the same principle accross the other libraries.

With that being said tabletest will change ..so its also created dyanamic..thus the reason I create the insert from the dyanmic pivot.

You shoudl see that I select into temp tables.
And you should see that I am doing a select into table test which is inserting the one row that is created from the pivot.


Would it be possible to take the logic on the 2nd run and create a dynamic MERGE that would do the insert dynamically and keep to the structure of the pivot?

Here is the code thus far:  You will notice some commented out code for merging in the area where I am thinking that might work.

USE SharePoint2007Archive

EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO


CREATE TABLE #tmp(excelFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /a-d /B E:\AR';

declare @fileName varchar(100)

Declare @fileData  XML
DECLARE @ReqID nvarchar(50)

DECLARE @ParmDefinition nvarchar(500); 
DECLARE @SQLString nvarchar(MAX);

DECLARE @ProjectXML XML;

DECLARE @cols NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

While (Select Count(*) From #tmp where excelFileName is not null) > 0
	BEGIN
		Select Top 1 @fileName = excelFileName From #tmp

		print @fileName

		set @SQLString = 'Select @fileDataOUT =BulkColumn from OpenRowSet(Bulk ''E:\AR\' +  @fileName + ''',Single_blob) x;'

		SET @ParmDefinition = N'@fileDataOUT XML OUTPUT';  

		EXECUTE sp_executesql @SQLString,
						      @ParmDefinition,
						      @fileDataOUT = @fileData OUTPUT;

SET @ProjectXML = @fileData

--Store RequestID as PK
		SELECT @ReqID = xData.Col.value('*:RequestID[1]','nvarchar(50)')
		from @ProjectXML.nodes('/*:AppropriationRequestForm/*:RequestHeader') AS xData(Col)-- this is the xpath to the individual records we want to extract							   

--get rest of data elements 

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'tabletest')
BEGIN
    PRINT 'Table Exists'
	SELECT n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
				n.value('text()[1]','NVARCHAR(MAX)') AS Value 
				INTO #TempAR1
				FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
				Where n.value('local-name(.)','VARCHAR(100)')
				not in
				(
					'root',
					'AppropriationRequestForm',
					'RequestHeader',
					'OperationalBenefit',
					'ProjectSummary',
					'FinancialBenefits',
					'RIMIImpulseGroup',
					'Summary',
					'Authorization',
					'SiteAuthorization',
					'ProjectManagerGroup',
					'ProjectManager2Group',
					'ProjectManager3Group',
					'ProjectManager4Group',
					'ProjectManager5Group',
					'TechnicalManagerGroup',
					'OperationControllerGroup',
					'SalesManagerGroup',
					'VPSalesGroup',
					'SiteManagerGroup',
					'SalesManagerSelector',
					'Person',
					'VPSalesSelector',
					'ProjectManager1Selector',
					'ProjectManager2Selector',
					'ProjectManager3Selector',
					'ProjectManager4Selector',
					'ProjectManager5Selector',
					'DepartmentAuthorization',
					'DepartmentApprover1Group',
					'DepartmentApprover2Group',
					'DepartmentApprover3Group',
					'DepartmentFPAReviewerGroup',
					'SpecialAuthorization',
					'ITReviewerGroup',
					'ITDirectorApprovalGroup',
					'DivisionAuthorization',
					'DivisionControllerGroup',
					'DivisionOperationsGroup',
					'FPAReviewerGroup',
					'DivisionVPGroup',
					'CorporateAuthorization',
					'CreditDirectorGroup',
					'TreasuryFinanceGroup',
					'AssistantTreasurerGroup',
					'TreasuryFinanceGroup',
					'VPTreasuryGroup',
					'AddtlCorpApproverGroup',
					'VPFinanceGroup',
					'CFOGroup',
					'COOApprovalGroup',
					'AddtCorpApproverSelector',
					'CEOAuthorization',
					'FormControl',
					'AttachmentsTable',
					'Documents')		

    select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name) 
                    from #TempAR1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

    set @query = 
'SELECT ' + @cols + ' INTO ##tabletest from 
             (
              select Name, Value
			  from #TempAR1
            ) x
            pivot 
            (
                max(Value)
                for Name in (' + @cols + ')
            ) p '

   execute(@query);	

--MERGE INTO tabletest AS dest
--USING ##tabletest AS ins ON 1=0   -- always false

--WHEN NOT MATCHED BY TARGET          -- happens for every row, because 1 is never 0
--    THEN INSERT ([Name])
--         VALUES (ins.[NAME])

--OUTPUT inserted.ID, ins.ID
--INTO @MyCrossRef (NewId, OldId);

---- Check the result
--SELECT * FROM @MyCrossRef



END
ELSE
BEGIN
   PRINT 'Table Not Exists' 
   SELECT
n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
n.value('text()[1]','NVARCHAR(MAX)') AS Value 
INTO #TempAR2
FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
Where n.value('local-name(.)','VARCHAR(100)')
not in
(
	'root',
	'AppropriationRequestForm',
	'RequestHeader',
	'OperationalBenefit',
	'ProjectSummary',
	'FinancialBenefits',
	'RIMIImpulseGroup',
	'Summary',
	'Authorization',
	'SiteAuthorization',
	'ProjectManagerGroup',
	'ProjectManager2Group',
	'ProjectManager3Group',
	'ProjectManager4Group',
	'ProjectManager5Group',
	'TechnicalManagerGroup',
	'OperationControllerGroup',
	'SalesManagerGroup',
	'VPSalesGroup',
	'SiteManagerGroup',
	'SalesManagerSelector',
	'Person',
	'VPSalesSelector',
	'ProjectManager1Selector',
	'ProjectManager2Selector',
	'ProjectManager3Selector',
	'ProjectManager4Selector',
	'ProjectManager5Selector',
	'DepartmentAuthorization',
	'DepartmentApprover1Group',
	'DepartmentApprover2Group',
	'DepartmentApprover3Group',
	'DepartmentFPAReviewerGroup',
	'SpecialAuthorization',
	'ITReviewerGroup',
	'ITDirectorApprovalGroup',
	'DivisionAuthorization',
	'DivisionControllerGroup',
	'DivisionOperationsGroup',
	'FPAReviewerGroup',
	'DivisionVPGroup',
	'CorporateAuthorization',
	'CreditDirectorGroup',
	'TreasuryFinanceGroup',
	'AssistantTreasurerGroup',
	'TreasuryFinanceGroup',
	'VPTreasuryGroup',
	'AddtlCorpApproverGroup',
	'VPFinanceGroup',
	'CFOGroup',
	'COOApprovalGroup',
	'AddtCorpApproverSelector',
	'CEOAuthorization',
	'FormControl',
	'AttachmentsTable',
	'Documents')

   select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name) 
                    from #TempAR2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

   set @query = 
'SELECT ' + @cols + ' INTO tabletest from 
             (
              select Name, Value
			  from #TempAR2
            ) x
            pivot 
            (
                max(Value)
                for Name in (' + @cols + ')
            ) p '

   execute(@query);

END


Insert into SharePoint2007Archive.dbo.Attachments
		(RequestID,Form, Document)
		select		  
		   @ReqID RequestID,
		   'AR', 
		   xData.Col.value('*:Documents[1]','nvarchar(MAX)') Document
		from @fileData.nodes('/*:AppropriationRequestForm/*:AttachmentsTable') AS xData(Col)
		WHERE DATALENGTH(xData.Col.value('*:Documents[1]','nvarchar(MAX)')) > 0

IF OBJECT_ID('tempdb..#TempAR1') IS NOT NULL
					DROP TABLE #TempAR1

IF OBJECT_ID('tempdb..#TempAR2') IS NOT NULL
					DROP TABLE #TempAR2
					

Delete from #tmp Where excelFileName = @FileName


END



--drop table tabletest
--DROP TABLE #tmp
--drop table #TempAR1
--drop table #TempAR2
--Truncate table Attachments

Open in new window

Also for testing this...just load some xml in @ProjectXML= ' your xml here'


Here is a snippit to change the code for testing purposes;




Use SharePoint2007Archive

DECLARE @ProjectXML XML;
DECLARE @cols NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX)

set @ProjectXML = 'paste xml here'

	SELECT n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
				n.value('text()[1]','NVARCHAR(MAX)') AS Value 
				INTO #TempAR1
				FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
				Where n.value('local-name(.)','VARCHAR(100)')
				not in
				(
					'root',
					'AppropriationRequestForm',
					'RequestHeader',
					'OperationalBenefit',
					'ProjectSummary',
					'FinancialBenefits',
					'RIMIImpulseGroup',
					'Summary',
					'Authorization',
					'SiteAuthorization',
					'ProjectManagerGroup',
					'ProjectManager2Group',
					'ProjectManager3Group',
					'ProjectManager4Group',
					'ProjectManager5Group',
					'TechnicalManagerGroup',
					'OperationControllerGroup',
					'SalesManagerGroup',
					'VPSalesGroup',
					'SiteManagerGroup',
					'SalesManagerSelector',
					'Person',
					'VPSalesSelector',
					'ProjectManager1Selector',
					'ProjectManager2Selector',
					'ProjectManager3Selector',
					'ProjectManager4Selector',
					'ProjectManager5Selector',
					'DepartmentAuthorization',
					'DepartmentApprover1Group',
					'DepartmentApprover2Group',
					'DepartmentApprover3Group',
					'DepartmentFPAReviewerGroup',
					'SpecialAuthorization',
					'ITReviewerGroup',
					'ITDirectorApprovalGroup',
					'DivisionAuthorization',
					'DivisionControllerGroup',
					'DivisionOperationsGroup',
					'FPAReviewerGroup',
					'DivisionVPGroup',
					'CorporateAuthorization',
					'CreditDirectorGroup',
					'TreasuryFinanceGroup',
					'AssistantTreasurerGroup',
					'TreasuryFinanceGroup',
					'VPTreasuryGroup',
					'AddtlCorpApproverGroup',
					'VPFinanceGroup',
					'CFOGroup',
					'COOApprovalGroup',
					'AddtCorpApproverSelector',
					'CEOAuthorization',
					'FormControl',
					'AttachmentsTable',
					'Documents')		

--select * from #TempAR1
--drop table #TempAR1

	select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Value) 
                    from #TempAR1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

   set @query = 
'SELECT ' + @cols + ' INTO tabletest2 from 
             (
              select Name, Value
			  from #TempAR1
            ) x
            pivot 
            (
                max(Value)
                for Name in (' + @cols + ')
            ) p '

   execute(@query);

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Robb Hill
Robb Hill
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial