从Sql server中的XML数据类型更新表行 [英] Update tables row from XML datatype in Sql server

问题描述

我不太喜欢在Sql中使用Xml数据类型，我有一个名为RSN_ALL的表，我需要从我的One Xml文件向该表插入数据..为此我创建一个商店程序。

  ALTER  过程 [dbo]。[SPInsertXmlData] 
（
  @ xmlData  XML 
）
  as  
  begin  
 
 插入 到 RSN_All 
（
 Batch_M_id，
 RSN，
 Parent_RSN，
 Pkg_Location，
 CompanyId 
）
 
  SELECT  
  COALESCE  
（[表]。[列]。value（'  Batch_M_id [1]'， '  bigint'）， 0 ） as  '  Batch_M_id'，
 [表]。[列]。value（'  RSN [1]'，'  varchar（20）'） as  '  RSN'，
 [ 表]。[列]。value（'  Parent_RSN [1]'，'  varchar（20 ）'） as  '  Parent_RSN'，
 [表]。[列]。value（'  Pkg_Location [1]'，'  nvarchar（100）'） as  '  Pkg_Location'，
 [表]。[列]。value（'  CompanyId [1]'，'  int'） as  '  CompanyId' 
 
  FROM   @ xmlData  .nodes（'  / NewDataSet / Table'） as  [表]（[ 列]）
 
 
  IF （ @@ ROWCOUNT >  0 ）
 选择 '  SUCCESS'  as  msg 
 
 结束 

它的工作原理完美，
 
但是我需要一些修改我想检查这行是否已经存在而不是更新该行其他明智的插入行。 （RSN专栏是我的唯一专栏），
 
我没有得到怎么做，请帮帮我

根据Mr.Ravi Shankar Dokka建议我尝试以下程序

  ALTER   procedure  [dbo]。[SPInsertXmlData] 
（
  @ xmlData  XML 
）
  as  
 开始 
 
   -   将xml导入临时表 
  SELECT  
  COALESCE  
（[表]。[列]。value（'  Batch_M_id [1]'，'  bigint'）， 0 ） as  '  Batch_M_id'，
 [表]。[列]。value（ '  RSN [1]'，'  varchar（20）'） as  '  RSN'，
 [表]。[列]。value（'  Parent_RSN [1]'，'  varchar（20）'） as  '  Parent_RSN'，
 [表]。[< span class =code-keyword> Column ]。value（'  Pkg_Location [1]'，'  nvarchar（100）'） as  '  Pkg_Location'，
 [表]。[列]。 value（'  CompanyId [1]'，'  int'） as  ' < span class =code-string> CompanyId' 
  into  #temp_xml 
  FROM   @ xmlData  .nodes（'  / NewDataSet / Table'） as  [表]（[列]）
 
 
  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 
 选择 * 
 来自 RSN_ALL ra 
  inner   join  #temp_xml tx  on （tx.RSN = ra.RSN ）  -   标识存在的字段 
 选择 ' 确定' 为 msg 
 
   -   现在进行插入，在'FROm'我正在获取错误 
 插入 进入 RSN_All（Batch_M_id，RSN，Parent_RSN，Pkg_Location，CompanyId ）
 来自 #temp_xml tx 
 其中 不 存在（选择  1  来自 RSN_All ra 其中 tx.RSN = ra.RSN）  -   标识存在的相同字段 
 
   -   提交事务 
  COMMIT   TRANSACTION  
  END  TRY 
  BEGIN  CATCH 
   -   任何问题回滚事务 
  IF   @@ TRANCOUNT >  0  
  BEGIN  
  ROLLBACK   TRANSACTION  
  END  
 结束 CATCH; 
 
   -    drop temp table  
  drop   table  #temp_xml 
 
 
  end  

但仍然是< pre>无效..其显示错误无效列名'RSN'

这是我的XML文件

 < ？  xml     version  < span class =code-keyword> =  1.0    standalone   =  yes ？ >  
 <   NewDataSet  >  
 <  表 >  
 <   Batch_M_id  >  10 <   / Batch_M_id  >  
 <   RSN  >  01HE55WV <   / RSN  >  
 <   Parent_RSN     /  >  
 <   Pkg_Location  >  1 <   / Pkg_Location  >  
 <   CompanyId  >  21 <   / CompanyId  >  
 <   / Table  >  
 <  表 >  
 <   Batch_M_id  >  10 <   / Batch_M_id  >  
 <   RSN  >  01DK7KNH <   / RSN  >  
 <   Parent_RSN     /  > ;  
 <   Pkg_Location   >  1 <   / Pkg_Location  > ;  
 <   CompanyId   >  21 <   / CompanyId  >  
 <   /表 >  
 <   / NewDataSet  >  

解决方案

你可以使用合并声明

; 
  WITH  cte  AS （
  SELECT   COALESCE （[表]。[列]。value（'  Batch_M_id [1]'，'  bigint'）， 0 ） AS  '  Batch_M_id'，
 [表]。[列]。value（'  RSN [1]'，'  varchar（20）'） AS  '  RSN'，
 [表]。[列]。value（' < span class =code-string> Parent_RSN [1]'，'  varchar（20）'） AS  '  Parent_RSN' ，
 [表]。[列]。value（'  Pkg_Location [1]'，'  nvarchar（100）'） AS  '  Pkg_Location'，
 [表]。[列]。 value（'  CompanyId [1]'，'  int'） AS  '  CompanyId' 
  FROM   @ xmlData  .nodes（< span class =code-string>'  / NewDataSet / Table'） AS  [ 表]（[列]）
）
  MERGE  RSN_All  AS  T 
  USING  Cte  AS  S 
  ON  s.Rsn = t.Rsn 
  WHEN   MATCHED  那么 
 
  UPDATE  
  SET  t.Batch_M_id = S.Batch_M_id，
 t.Pa rent_RSN = S.Parent_RSN，
 T.Pkg_Location = S.Pkg_Location，
 t.CompanyId = S.CompanyId 
  WHEN   NOT   MATCHED  那么 
 
  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 
）; 

这是我创造的程序及其完美的工作！ 
如果行存在其他明智的插入，它将更新行。

  CREATE   procedure  [dbo]。[SPInsertXmlData] 
（
  @ xmlData  XML 
）
  as  
 开始 
 
 
   -   将xml导入临时表 
  SELECT  
  COALESCE  
（[表]。[列]。value（'  Batch_M_id [1]'，'  bigint'）， 0 ） as  Batch_M_id，
 [表]。[列]。value（'  RSN [1]'，'  varchar（20）'） as  RSN，
 [表]。[列]。value（'  Parent_RSN [1]'，'  varchar（20）'） as  Parent_RSN，
 [表].[列]。value（'  Pkg_Location [1]'，'  nvarchar（100）'） as  Pkg_Location，
 [表]。[列 ] .value（'  CompanyId [1]'，'  int'） as  CompanyId 
  into  #temp_xml 
  FROM   @ xmlData  .nodes（'  / NewDataSet / Table'） as  [表]（[列]）
 
 
  BEGIN  TRY 
   -   启动事务 
  BEGIN   TRANSACTION  
 
   -     - 现在做更新 
 更新 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 
 
 来自 RSN_ALL ra 
 内部 加入 #temp_xml tx  （ra.RSN = tx.RSN）  -   标识存在的字段 
 
   -   现在执行插入（Batch_M_id，RSN，Parent_RSN，Pkg_Location，CompanyId） 
  insert   into  RSN_All 
 选择 * 
 来自 #temp_xml tx 
 其中 不 存在（选择  1  来自 RSN_All ra 其中 tx.RSN = ra.RSN）  -   标识相同的字段存在 
 
   -   提交交易 
  COMMIT   TRANSACTION  
  END  TRY 
  BEGIN  CATCH 
   -   任何问题回滚交易 
  IF   @@ TRANCOUNT >  0  
  BEGIN  
  ROLLBACK   TRANSACTION  
  END  
 结束 CATCH; 
 
   -    drop temp table  
  drop   table  #temp_xml 
 
 
  end  

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 

-- 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 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>

解决方案

you can use merge statement

;
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
  );

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

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

这篇关于从Sql server中的XML数据类型更新表行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！