title: Update data in the table using XML string
body: <p>I have table which contains columns like </p>
  
  <ul>
  <li><code>SiteID (identity_Col)</code></li>
  <li><code>SiteName</code></li>
  <li><code>POrderID</code></li>
  <li><code>Location</code></li>
  <li><code>Address</code></li>
  <li><code>Cluster</code></li>
  <li><code>VenderName</code> </li>
  </ul>
  
  <p>I want to use xml string to insert/update/delete data in this table. Apart from this column, XML string contains one more column viz. RowInfo. This column will have values like "Unchanged","Update","New","Delete". Based on this values the rows in the table should be inserted,updated,deleted. </p>
  
  <p>My XML string is as below:</p>
  
  <pre><code>&lt;NewDataSet&gt;
    &lt;DataTable&gt;
       &lt;SiteID&gt;2&lt;/SiteID&gt;
       &lt;SiteName&gt;NIZAMPURA&lt;/SiteName&gt;
       &lt;POrderID&gt;7&lt;/POrderID&gt;
       &lt;Location&gt;NIZAMPURA&lt;/Location&gt;
       &lt;SiteAddress&gt;Vadodara&lt;/SiteAddress&gt;
       &lt;Cluster&gt;002&lt;/Cluster&gt;
       &lt;SubVendorName&gt;Test Vender-1&lt;/SubVendorName&gt;
       &lt;RowInfo&gt;UNCHANGED&lt;/RowInfo&gt;
    &lt;/DataTable&gt;
    &lt;DataTable&gt;
       &lt;SiteID&gt;16&lt;/SiteID&gt;
       &lt;SiteName&gt;Site-1&lt;/SiteName&gt;
       &lt;POrderID&gt;7&lt;/POrderID&gt;
       &lt;Location&gt;Alkapuri&lt;/Location&gt;
       &lt;SiteAddress&gt;test&lt;/SiteAddress&gt;
       &lt;Cluster&gt;Test Cluster&lt;/Cluster&gt;
       &lt;SubVendorName&gt;Test Vender12&lt;/SubVendorName&gt;
       &lt;RowInfo&gt;UNCHANGED&lt;/RowInfo&gt;
    &lt;/DataTable&gt;
    &lt;DataTable&gt;
       &lt;SiteID&gt;17&lt;/SiteID&gt;
       &lt;SiteName&gt;Site-3&lt;/SiteName&gt;
       &lt;POrderID&gt;7&lt;/POrderID&gt;
       &lt;Location&gt;Alkapuri123&lt;/Location&gt;
       &lt;SiteAddress&gt;test123&lt;/SiteAddress&gt;
       &lt;Cluster&gt;Test Cluster123&lt;/Cluster&gt;
       &lt;SubVendorName&gt;Test Vender123&lt;/SubVendorName&gt;
       &lt;RowInfo&gt;DELETE&lt;/RowInfo&gt;
    &lt;/DataTable&gt;
  &lt;/NewDataSet&gt;'
  </code></pre>
  
  <p>This is the code that I have written to insert data in table if RowInfo = "NEW"</p>
  
  <pre><code>IF len(ISNULL(@xmlString, '')) &gt; 0
      BEGIN
      DECLARE @docHandle1 int = 0;
      EXEC sp_xml_preparedocument @docHandle1 OUTPUT, @xmlString
  
      INSERT INTO [SiteTRS] (
                  [SiteName],
                  [POrderID],
                  [Location],
                  [SiteAddress],
                  [Cluster],
                  [SubVendorName])
      SELECT SiteName,POrderID,Location,SiteAddress,Cluster,SubVendorName
          FROM OPENXML (@docHandle1, '/NewDataSet/DataTable')
  
          WITH (SiteName varchar(50) './SiteName',
            POrderID varchar(50) './PorderID',
            Location varchar(50) './Location',
            SiteAddress varchar(max) './SiteAddress',
            Cluster varchar(50) './Cluster',
            SubVendorName varchar(50) './SubVendorName',
            RowInfo varchar(30) './RowInfo')   
      WHERE RowInfo='NEW' 
  </code></pre>
  
  <p>But I don't know how to use XML to update/delete records in the table. Please guide
  I am novice in the XML so dont have any idea. Please forgive me if I am making something childish.</p>