Update tables row from XML datatype in Sql server

Question

0.00/5 (No votes)

See more:

I m not so good to use Xml data type in Sql, I have one table Named RSN_ALL, and i need to insert data to that table from my One Xml File.. for that I create One Store Procedure.

SQL

ALTER procedure [dbo].[SPInsertXmlData]
 (
@xmlData XML
 )
as
 begin

  insert into RSN_All
 (
Batch_M_id,
RSN,
Parent_RSN,
Pkg_Location,
CompanyId
)

SELECT
COALESCE
([Table].[Column].value('Batch_M_id[1]', 'bigint'),0) as 'Batch_M_id',
[Table].[Column].value('RSN [1]', 'varchar(20)') as ' RSN ',
[Table].[Column].value(' Parent_RSN[1]', 'varchar(20)') as ' Parent_RSN',
[Table].[Column].value(' Pkg_Location [1]', 'nvarchar(100)') as ' Pkg_Location',
[Table].[Column].value(' CompanyId [1]', 'int') as ' CompanyId'

 FROM @xmlData.nodes('/ NewDataSet / Table') as [Table]([Column])


 IF(@@ROWCOUNT > 0 )
 select   'SUCCESS' as msg

  end

and it works Perfectly ,

but i need Some Modification i want to check if this row its already Present Than update that row other wise insert row. (RSN column is my Unique Column),

I am not getting how to do this, please help me

As per Mr.Ravi Shankar Dokka Suggest me I try Below Procedure

SQL

ALTER procedure [dbo].[SPInsertXmlData]
(
@xmlData XML
)
as
begin 

-- import xml into temp table
SELECT
COALESCE
([Table].[Column].value('Batch_M_id[1]', 'bigint'),0) as 'Batch_M_id',
[Table].[Column].value('RSN [1]', 'varchar(20)') as ' RSN ',
[Table].[Column].value(' Parent_RSN[1]', 'varchar(20)') as ' Parent_RSN',
[Table].[Column].value(' Pkg_Location [1]', 'nvarchar(100)') as ' Pkg_Location',
[Table].[Column].value(' CompanyId [1]', 'int') as ' CompanyId'
into #temp_xml
FROM @xmlData.nodes('/ NewDataSet / Table') as [Table]([Column])


BEGIN TRY
    -- start the transaction
    BEGIN TRANSACTION

    -- now do the updates
    update ra
    set 
    ra.Batch_M_id =tx. Batch_M_id,
    ra.RSN=tx.RSN , 
    ra.Parent_RSN =tx.Parent_RSN,
    ra.Pkg_Location=tx.Pkg_Location,
    ra.CompanyId= tx.CompanyId
    select *      
    from RSN_ALL ra
    inner join #temp_xml tx on (tx.RSN = ra.RSN ) -- the fields that identify existence
    select 'Ok' as msg

    -- now do the inserts , In this at 'FROm'I am getting Error
    insert into RSN_All (Batch_M_id,RSN,Parent_RSN,Pkg_Location,CompanyId)
    from #temp_xml tx
    where not exists (select 1 from RSN_All ra where tx.RSN= ra.RSN) -- the same fields that identify existence

    -- commit the transaction
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    -- Any problems rollback transaction
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION
    END
END CATCH;

-- drop temp table
drop table #temp_xml


end

But still its <pre>not working .. Its show Error Invalid Column name 'RSN'

This Is My XML File

XML

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <Batch_M_id>10</Batch_M_id>
    <RSN>01HE55WV</RSN>
    <Parent_RSN />
    <Pkg_Location>1  </Pkg_Location>
    <CompanyId>21</CompanyId>
  </Table>
  <Table>
    <Batch_M_id>10</Batch_M_id>
    <RSN>01DK7KNH</RSN>
    <Parent_RSN />
    <Pkg_Location>1  </Pkg_Location>
    <CompanyId>21</CompanyId>
  </Table> 
</NewDataSet>

Posted 23-May-14 2:36am

Varun_nayak

Updated 23-May-14 20:30pm

v4

Add a Solution

Comments

Ravi Shankar Dokka 23-May-14 10:10am

Step1: Instead of inserting the rows into the target table you insert into a temporary table (#table).
Step2: Update the existing records by using an inner join.
Step3: Delete these existing records as they are no more required
Step4: Now in the temp table it will contain only those records which are not existing. Insert these directly to the target table.

Since I don't have the table structure for the target table and also the xml data all I can provide you is the approach.

Let me know for any issues.

Varun_nayak 24-May-14 0:34am

Thank you ravi . let me try than i will reply you back

Varun_nayak 24-May-14 2:13am

As per your Reply I try Above Procedure But Still Its Not Working, Neither Update not Insert.
Its Shows Error Invalid Column Name RSN
my table has 5 columns which All are Mention above,

2 solutions

Solution 1

you can use merge statement

SQL

;
WITH cte AS(
         SELECT COALESCE([Table].[Column].value('Batch_M_id[1]', 'bigint'), 0) AS 'Batch_M_id',
                [Table].[Column].value('RSN [1]', 'varchar(20)') AS 'RSN',
                [Table].[Column].value(' Parent_RSN[1]', 'varchar(20)') AS 'Parent_RSN',
                [Table].[Column].value(' Pkg_Location [1]', 'nvarchar(100)') AS 'Pkg_Location',
                [Table].[Column].value(' CompanyId [1]', 'int') AS 'CompanyId'
         FROM   @xmlData.nodes('/ NewDataSet / Table') AS [Table]([Column])
     )
     MERGE RSN_All AS T
     USING Cte AS S
     ON s.Rsn=t.Rsn
     WHEN MATCHED THEN

UPDATE
SET    t.Batch_M_id = S.Batch_M_id,
       t.Parent_RSN = S.Parent_RSN,
       T.Pkg_Location = S.Pkg_Location,
       t.CompanyId = S.CompanyId
       WHEN NOT MATCHED THEN

INSERT
  (
    Batch_M_id,
    RSN,
    Parent_RSN,
    Pkg_Location,
    CompanyId
  )
VALUES
  (
    S.Batch_M_id,
    S.RSN,
    S.Parent_RSN,
    S.Pkg_Location,
    S.CompanyId
  );

Posted 23-May-14 4:49am

Thava Rajan

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Varun_nayak · Accepted Answer · 2014-05-23T22:21:00

This Is Procedure I Create And Its Work Perfectly!! 
It Will Update row if Row Is Present Other Wise Insert .

SQL

CREATE procedure [dbo].[SPInsertXmlData]
(
@xmlData XML
)
as
begin 


-- import xml into temp table
SELECT
COALESCE
([Table].[Column].value('Batch_M_id[1]', 'bigint'),0) as Batch_M_id,
[Table].[Column].value('RSN [1]', 'varchar(20)') as RSN,
[Table].[Column].value(' Parent_RSN[1]', 'varchar(20)') as Parent_RSN,
[Table].[Column].value(' Pkg_Location [1]', 'nvarchar(100)') as Pkg_Location,
[Table].[Column].value(' CompanyId [1]', 'int') as CompanyId
into #temp_xml
FROM @xmlData.nodes('/ NewDataSet / Table') as [Table]([Column])


BEGIN TRY
    -- start the transaction
    BEGIN TRANSACTION

    ---- now do the updates
    update ra
    set 
      ra.Batch_M_id =tx.Batch_M_id,
     ra.Parent_RSN =tx.Parent_RSN,
    ra.Pkg_Location=tx.Pkg_Location,
    ra.CompanyId= tx.CompanyId
        
    from RSN_ALL ra
    inner join #temp_xml tx on (ra.RSN =tx.RSN   ) -- the fields that identify existence
   
    -- now do the inserts (Batch_M_id,RSN,Parent_RSN,Pkg_Location,CompanyId)
    insert into RSN_All 
    select *   
    from #temp_xml tx
    where not exists (select 1 from RSN_All ra where tx.RSN= ra.RSN) -- the same fields that identify existence

    -- commit the transaction
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    -- Any problems rollback transaction
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION
    END
END CATCH;

-- drop temp table
drop table #temp_xml


end