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:
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:
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