locked
insert or update records in sql server 2000 using XML RRS feed

  • Question

  • Hi I am using sql server 2000 and I need to insert or update records from an xml based on ID column (i.e) if ID exists then insert else update. Below is sample data

    DECLARE @xmlvalue varchar(8000)
    set @xmlvalue='<?xml version="1.0"?>
            <root>
                <element>
                    <Id>1</Id>
                    <Name>aaa</ChannelName>
                </element>
                <element>
                    <Id></Id>
                    <Name>new</ChannelName>
                </element>
                <element>
                    <Id>2</Id>
                    <Name>bbb</ChannelName>
                </element>
            </root>
            '


     something like the first and the third needs to be updated (ID is not null) and the second needs to be inserted (ID is null). There can also be more than 3 elements (5 or 6 not predefined). So I believe the xml needs to be looped through.

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Monday, February 25, 2013 10:57 AM

Answers

  • Try the below:

    DECLARE @x VARCHAR(8000) SELECT @x = '<?xml version="1.0"?> <root> <element> <Id>1</Id> <ChannelName>aaa</ChannelName> </element> <element> <Id></Id> <ChannelName>new</ChannelName> </element> <element> <Id>2</Id> <ChannelName>bbb</ChannelName> </element> </root> ' DECLARE @h INT EXEC sp_xml_preparedocument @h OUTPUT, @x SELECT * into #Temp FROM OPENXML(@h, '/root/element',2) WITH ( id Int 'Id' ,ChannelName Varchar(500) 'ChannelName' ) EXEC sp_xml_removedocument @h Insert into <your table>(Id,ChannelName) Select * From #Temp Where ID IS null Update A Set A.ChannelName = B.ChannelName From <YourTable> A Inner Join #Temp B On A.Id = B.ID



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Kalman Toth Sunday, March 3, 2013 9:02 PM
    • Marked as answer by Iric Wen Tuesday, March 5, 2013 9:12 AM
    Monday, February 25, 2013 11:16 AM
  • You need to have Left join with ChannelName condition with a NULL check. That would work for fine.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Iric Wen Tuesday, March 5, 2013 9:12 AM
    Monday, February 25, 2013 11:58 AM

All replies

  • Try the below:

    DECLARE @x VARCHAR(8000) SELECT @x = '<?xml version="1.0"?> <root> <element> <Id>1</Id> <ChannelName>aaa</ChannelName> </element> <element> <Id></Id> <ChannelName>new</ChannelName> </element> <element> <Id>2</Id> <ChannelName>bbb</ChannelName> </element> </root> ' DECLARE @h INT EXEC sp_xml_preparedocument @h OUTPUT, @x SELECT * into #Temp FROM OPENXML(@h, '/root/element',2) WITH ( id Int 'Id' ,ChannelName Varchar(500) 'ChannelName' ) EXEC sp_xml_removedocument @h Insert into <your table>(Id,ChannelName) Select * From #Temp Where ID IS null Update A Set A.ChannelName = B.ChannelName From <YourTable> A Inner Join #Temp B On A.Id = B.ID



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Kalman Toth Sunday, March 3, 2013 9:02 PM
    • Marked as answer by Iric Wen Tuesday, March 5, 2013 9:12 AM
    Monday, February 25, 2013 11:16 AM
  • Lathesh the solution looks fine but .Just a small change, Before inserting or updating I need to check whether a channel name already exists in the table...

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Monday, February 25, 2013 11:52 AM
  • You need to have Left join with ChannelName condition with a NULL check. That would work for fine.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Iric Wen Tuesday, March 5, 2013 9:12 AM
    Monday, February 25, 2013 11:58 AM