Click here to Skip to main content
15,868,139 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

I have a requirement please help me. I am inserting bulk number of records in SQL server database using XML datatype, i want to know if due to any inconsistent data any of the records are missed to insert, i want to get that missed records information in a log file. Please correct me if any thing wrong.
Posted

1 solution

Hi Ramesh,

You can do one thing.

Please convert your XML to SQL table and then try to find the missing record using "except" keyword. "except" keyword will help you to find the difference between 2 tables

Example:

SQL
declare @XML as XML = '<row>
    <col1>8</col1>
    <col2>3</col2>
    <col3>8</col3>
    <col4>5</col4>
</row>
<row>
    <col1>18</col1>
    <col2>5</col2>
    <col3>8</col3>
    <col4>5</col4>
</row>'

SELECT  
       Tbl.Col.value('Col1[1]', 'int'),  
       Tbl.Col.value('Col2[1]', 'int'),  
       Tbl.Col.value('Col3[1]', 'int'),
       Tbl.Col.value('Col4[1]', 'int')
FROM   @xml.nodes('//row') Tbl(Col) 


Let's assume Col1 column is your primary key. (which will help you to compare the data. You can use any of the combination of column based on your requirement)

Then please compare data in below way:

SQL
SELECT
       Tbl.Col.value('Col1[1]', 'int')
FROM   @xml.nodes('//row') Tbl(Col)

except

select yourPrimaryColumn from YourTable


In output, you will get data which are available on your above query but on available on your below query.

Please let me know if you have any concern or query or if I am missing something.

Thanks
Advay Pandya
 
Share this answer
 
Comments
Ramesh0809 27-May-15 2:50am    
Thanks a lot for your response Advay. My question here is while inserting data due to some inconsistent data/or due to some error, if any of the records are missing to insert into database. Then how i comes to know from where the records are not inserted in the given XML file. The same information i want to capture in a log file. Appreciate your support.
Advay Pandya 27-May-15 3:00am    
I have a query. The data are inserted in a loop or in bulk ? If the data are inserted in look, you can use try/catch block in SQL query and you can insert error log in a table from catch block. If the data is inserted in bulk then you will need to verify only using except keyword. Please let me know if you need more info or I am still missing something. :)
Ramesh0809 27-May-15 4:06am    
Hi Advay, i am inserting in bulk.
Advay Pandya 27-May-15 4:47am    
if you are inserting in bulk then there is no room for error. If error will occur then none of the data will be inserted. If insertion is completed successfully then only way to verify data using except keyword. it will result all the missing data. If there are 100 rows needs to be inserted and only 80 are inserted then "except" will show you missing 20 rows. Got my point ?
Ramesh0809 27-May-15 5:04am    
Thank U Advay for your quick response and support.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900