Validation for data duplication of XML in SQL and C# ASP

Question

0.00/5 (No votes)

See more:

I have an C# asp page, which has an upload button to upload xml file:
Then I can choose the xml and display in grid view, which is working fine,
and whenever I upload a file which has been already uploaded, it rejects the xml file with duplicate records validation.

I created a stored procedure which inserts xml data to temp table and then checks with the main table, if there already exists same data it will delete those data.

now I need a validation message showing the there are duplicate data.
how do I do it?

What I have tried:

I have a sproc

alter PROCEDURE xmldata      
@xml XML , @c_id varchar(100)     

AS     
 
 create table #XMLTable
 (
	ID				int identity,
    [a]        varchar(50),
    [b]           varchar(50),
    [c]           varchar(50)
 )

 BEGIN      
      SET NOCOUNT ON;      
      INSERT INTO  #XMLTable      
      SELECT      
      C.value('(a/text())[1]','VARCHAR(max)') AS [a],      
      C.value('(b/text())[1]','VARCHAR(max)') AS [b],        
      C.value('(c/text())[1]','VARCHAR(max)') AS [c]  
      
      FROM      
         @xml.nodes('/xyz/C')AS TEMPTABLE(C)
		 
delete   #XMLTable from #XMLTable xt inner join main_table mt on xc.[a] = mt.[a] AND xc.[b]=mt.[b] AND xc.[c]=mt.[c] 

    


INSERT INTO main_table


 

select * from #XMLTable 

END

I need to add a validation of duplication of data
(raise error) throws an error validation display but how should I modify the sproc

Posted 11-Nov-22 2:35am

DevNoob

Updated 15-Nov-22 2:58am

Add a Solution

Comments

CHill60 15-Nov-22 8:30am

Did you know you can edit your posts with the Improve question link - which is what you should have done with How do I parse non duplicate values in XML to be parsed to database in an XML file which has duplicate records[^] rather than posting what is essentially a duplicate question

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2022-11-15T02:58:00

Firstly, your delete statement will not work Error: The multi-part identifier "xc.a" could not be bound.Your alias should be either xc for the table or xt. for the columns. E.G.

SQL

delete   #XMLTable from #XMLTable xc inner join main_table mt on xc.[a] = mt.[a] AND xc.[b]=mt.[b] AND xc.[c]=mt.[c]

Secondly, as you are using table aliases it is better to be consistent and use those aliases throughout e.g.

SQL

delete xc from #XMLTable xc inner join main_table mt on xc.[a] = mt.[a] AND xc.[b]=mt.[b] AND xc.[c]=mt.[c]

Probably the easiest way to add your validation message is to return an output parameter from your sproc e.g.

SQL

, @DupsFound int OUTPUT

and test for the duplicates before deleting them e.g.

SQL

SELECT @DupsFound = COUNT(*)
FROM main_table mt
inner join #XMLTable xc on xc.[a] = mt.[a] AND xc.[b]=mt.[b] AND xc.[c]=mt.[c]

Your UI layer should display the message if @dupsfound is > 0

As an aside, rather than creating a temporary table everytime, why not have a permanent "staging" table

Lastly, you have created an ID identity field on the temporary table - if your main_table also has an identity ID column then you must use

SQL

INSERT INTO main_table
select [a],[b],[c] from #XMLTable

Don't use * as that will include the ID column which is populated automatically

Validation for data duplication of XML in SQL and C# ASP

1 solution

Solution 1

Add your solution here

Preview 0

Validation for data duplication of XML in SQL and C# ASP

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us