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.
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
ALTER procedure [dbo].[SPInsertXmlData]
(
@xmlData XML
)
as
begin
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
BEGIN TRANSACTION
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 )
select 'Ok' as msg
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)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END
END CATCH;
drop table #temp_xml
end
But still its <pre>not working .. Its show Error Invalid Column name 'RSN'
This Is My XML File
="1.0"="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>