declare @xml xml='
<Customer>
<StatusId>1</StatusId>
<EntityName>Common</EntityName>
<Status>Active</Status>
</Customer>
<Customer>
<StatusId>2</StatusId>
<EntityName>Common</EntityName>
<Status>In-Active</Status>
</Customer>
<Customer>
<StatusId>3</StatusId>
<EntityName>ReceiptStatus</EntityName>
<Status>Created</Status>
</Customer>
<Customer>
<StatusId>4</StatusId>
<EntityName>ReceiptStatus</EntityName>
<Status>Submitted</Status>
</Customer>
<Customer>
<StatusId>5</StatusId>
<EntityName>ReceiptStatus</EntityName>
<Status>Confirmed</Status>
</Customer>
<Customer>
<StatusId>6</StatusId>
<EntityName>ReceiptStatus</EntityName>
<Status>Payment Initated</Status>
</Customer>
<Customer>
<StatusId>7</StatusId>
<EntityName>PaymentStatus</EntityName>
<Status>Credited to PAO Account</Status>
</Customer>
<Customer>
<StatusId>8</StatusId>
<EntityName>PaymentStatus</EntityName>
<Status>Failed</Status>
</Customer>
<Customer>
<StatusId>9</StatusId>
<EntityName>PaymentStatus</EntityName>
<Status>Quited</Status>
</Customer>
<Customer>
<StatusId>10</StatusId>
<EntityName>PaymentStatus</EntityName>
<Status>Status Pending</Status>
</Customer>
<Customer>
<StatusId>11</StatusId>
<EntityName>PaymentStatus</EntityName>
<Status>Status UnKnown/Abort</Status>
</Customer>
<Customer>
<StatusId>12</StatusId>
<EntityName>PaymentStatus</EntityName>
<Status>Transaction Success</Status>
</Customer>
<Customer>
<StatusId>13</StatusId>
<EntityName>PaymentStatus</EntityName>
<Status>Incomplete</Status>
</Customer>
<Customer>
<StatusId>14</StatusId>
<EntityName>PaymentStatus</EntityName>
<Status>Expired</Status>
</Customer>'
insert into ntrp.tblreceiptstatus(StatusId,EntityName,Status)
select contact.query('StatusId').value('.','int') as aa,
contact.query('EntityName').value('.','varchar(50)') as aa1,
contact.query('Status').value('.','varchar(50)') as aa2
from @xml.nodes('/Customer') as XMLData(Contact) where contact.query('StatusId').value('.','int') not in (select StatusId from ntrp.tblreceiptstatus)
No comments:
Post a Comment