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.

marc_s · Accepted Answer · 2011-09-13 11:55:00Z

4

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:

edited Sep 13, 2011 at 11:55

answered Sep 13, 2011 at 8:31

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Microsoft Developer Over a year ago

@marc_s..The MERGE command link that you have provided shows only two operations in each block. But I want all three i.e. insert/update/delete. more over it shows insert/update/delete using simple query and not using XML. Can you please elaborate your answer???

marc_s Over a year ago

@Chirag Fanse: those are just samples! Of course you can have many more conditions to define insert, update, delete and other statements. Browse the web, search on Google - you'll find PLENTY of info! I updated my answer with a rough skeleton of what you need to do...

Microsoft Developer Over a year ago

@marc_s..What is "NDS.DT.value" in your answer before update? I thought it as alias for '/NewDataSet/DataTable' as you have written "FROM @input.nodes('/NewDataSet/DataTable') AS NDS(DT)" but when I copy ans pasted, it shows error "can not find either the column NDS or user-defined function or aggregate function NDS.DT.VALUE or the name is ambiguous"

Microsoft Developer Over a year ago

@marc_s..I tried so many things, read your links but still not able to get the idea how to write insert/update/delete command using XMLsting. Can you please provide code based on my data???

marc_s Over a year ago

@chirag fanse: updated my answer yet again - typed out all the statements as they should be. The MERGE statement will do one of those statements for each row, assuming that the SiteID is the unique ID that uniquely identifies each of your rows (the primary key). Since it does this for each row in the source table (your CTE based on the XML), it know which row (and thus which SiteID) this command is being executed - that's why you can e.g. write just ... THEN DELETE if a certain condition matches - SQL Server will know which row to delete from the source table

|

Collectives™ on Stack Overflow

Update data in the table using XML string

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related