Robb Hill
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.
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
Yes, I agree the IF is not necessary but that does not explain the error. Your code contains:
You may also test whether the temp table exists by
IF OBJECT_ID('tempdb..#TempAR ') IS NOT NULL
DROP TABLE #TempAR
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
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
DROP TABLE #TempAR
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.
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
ASKER
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.
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
ASKER
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.
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
Why is refactoring so hard? Start with
btw, as I told you, remove your counter if (wink, wink) ...
-- 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;
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
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.
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.
ASKER
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?
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?
ASKER
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.
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.
ASKER
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
ASKER
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 ......
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. Attachment s
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 only insert I can see is Insert into SharePoint2007Archive.dbo.
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
ASKER
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.
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
ASKER
Also for testing this...just load some xml in @ProjectXML= ' your xml here'
Here is a snippit to change the code for testing purposes;
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);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.