Thursday, June 25, 2015

How to insert bulk data in MS Sql with Xml ?



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