Update data in the table using XML string

Question

I have table which contains columns like

• SiteID (identity_Col)
• SiteName
• POrderID
• Location
• Address
• Cluster
• VenderName

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.

My XML string is as below:

<NewDataSet>
  <DataTable>
     <SiteID>2</SiteID>
     <SiteName>NIZAMPURA</SiteName>
     <POrderID>7</POrderID>
     <Location>NIZAMPURA</Location>
     <SiteAddress>Vadodara</SiteAddress>
     <Cluster>002</Cluster>
     <SubVendorName>Test Vender-1</SubVendorName>
     <RowInfo>UNCHANGED</RowInfo>
  </DataTable>
  <DataTable>
     <SiteID>16</SiteID>
     <SiteName>Site-1</SiteName>
     <POrderID>7</POrderID>
     <Location>Alkapuri</Location>
     <SiteAddress>test</SiteAddress>
     <Cluster>Test Cluster</Cluster>
     <SubVendorName>Test Vender12</SubVendorName>
     <RowInfo>UNCHANGED</RowInfo>
  </DataTable>
  <DataTable>
     <SiteID>17</SiteID>
     <SiteName>Site-3</SiteName>
     <POrderID>7</POrderID>
     <Location>Alkapuri123</Location>
     <SiteAddress>test123</SiteAddress>
     <Cluster>Test Cluster123</Cluster>
     <SubVendorName>Test Vender123</SubVendorName>
     <RowInfo>DELETE</RowInfo>
  </DataTable>
</NewDataSet>'

This is the code that I have written to insert data in table if RowInfo = "NEW"

IF len(ISNULL(@xmlString, '')) > 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' 

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.

Answer 1

I would strongly recommend not to use the old, legacy OPENXML stuff anymore - with XML support in SQL Server, it's much easier to use the built-in XPath/XQuery methods.

In your case, I would use a CTE (Common Table Expression) to break up the XML into an "inline" table of rows and columns:

DECLARE @input XML = '<NewDataSet>
  <DataTable>
    <SiteID>2</SiteID>
    <SiteName>NIZAMPURA</SiteName>
    <POrderID>7</POrderID>
    <Location>NIZAMPURA</Location>
    <SiteAddress>Vadodara</SiteAddress>
    <Cluster>002</Cluster>
    <SubVendorName>Vender-1</SubVendorName>
    <RowInfo>UPDATE</RowInfo>
  </DataTable>
  <DataTable>
    <SiteName>Site-1</SiteName>
    <POrderID>7</POrderID>
    <Location>Alkapuri</Location>
    <SiteAddress>test</SiteAddress>
    <Cluster>Cluster-1</Cluster>
    <SubVendorName>Test Vender</SubVendorName>
    <RowInfo>NEW</RowInfo>
  </DataTable>
</NewDataSet>'

;WITH XMLData AS
(
    SELECT
        NDS.DT.value('(SiteID)[1]', 'int') AS 'SiteID',
        NDS.DT.value('(SiteName)[1]', 'varchar(50)') AS 'SiteName',
        NDS.DT.value('(POrderID)[1]', 'int') AS 'POrderID',
        NDS.DT.value('(Location)[1]', 'varchar(100)') AS 'Location',
        NDS.DT.value('(SiteAddress)[1]', 'varchar(100)') AS 'SiteAddress',
        NDS.DT.value('(Cluster)[1]', 'varchar(100)') AS 'Cluster',
        NDS.DT.value('(SubVendorName)[1]', 'varchar(100)') AS 'SubVendorName',
        NDS.DT.value('(RowInfo)[1]', 'varchar(20)') AS 'RowInfo'
    FROM 
        @input.nodes('/NewDataSet/DataTable') AS NDS(DT)
)
SELECT *
FROM XMLDATA

This gives you rows and columns which you can work with.

SiteID  SiteName   POrderID Location   SiteAddress  Cluster    SubVendorName   RowInfo
  2     NIZAMPURA     7     NIZAMPURA  Vadodara     002        Vender-1        UPDATE
 NULL   Site-1        7     Alkapuri   test         Cluster-1  Test Vender     NEW

Now if you're on SQL Server 2008 or newer, you could combine this with the MERGE command to do your INSERT/UPDATE in a single statement, basically.

If you're on 2005, you will need to either store this information into a temporary table / table variable inside your stored proc, or you need to do the select multiple times; the CTE allows only one single command to follow it.

Update: with this CTE, you can then combine it with a MERGE:

;WITH XmlData AS 
(
    SELECT
        NDS.DT.value('(SiteID)[1]', 'int') AS 'SiteID',
        NDS.DT.value('(SiteName)[1]', 'varchar(50)') AS 'SiteName',
        NDS.DT.value('(POrderID)[1]', 'int') AS 'POrderID',
        NDS.DT.value('(Location)[1]', 'varchar(100)') AS 'Location',
        NDS.DT.value('(SiteAddress)[1]', 'varchar(100)') AS 'SiteAddress',
        NDS.DT.value('(Cluster)[1]', 'varchar(100)') AS 'Cluster',
        NDS.DT.value('(SubVendorName)[1]', 'varchar(100)') AS 'SubVendorName',
        NDS.DT.value('(RowInfo)[1]', 'varchar(20)') AS 'RowInfo'
    FROM 
        @input.nodes('/NewDataSet/DataTable') AS NDS(DT)
)
MERGE INTO dbo.SiteTRS t
USING XmlData x ON t.SiteID = x.SiteID
WHEN MATCHED AND x.RowInfo = 'UPDATE'
   THEN 
     UPDATE SET 
        t.SiteName = x.SiteName,
        t.POrderID = x.POrderID,
        t.Location = x.Location,
        t.SiteAddress = x.SiteAddress,
        t.Cluster = x.Cluster,
        t.SubVendorName = x.SubVendorName

WHEN MATCHED AND x.RowInfo = 'DELETE'
   THEN DELETE 

WHEN NOT MATCHED AND x.RowInfo = 'NEW'
   THEN 
      INSERT(SiteID, SiteName, POrderID, Location, SiteAddress, Cluster, SubVendorName)
      VALUES(x.SiteID, x.SiteName, x.POrderID, x.Location, x.SiteAddress, x.Cluster, x.SubVendorName)
;

See some more resources:

• SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
• Using SQL Server 2008's MERGE statement