存储过程--数据拼接成XML 插入到表

USE [InformationSystem]
GO
/****** Object:  StoredProcedure [dbo].[pro_insertXML]    Script Date: 05/06/2015 16:36:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <数据拼接成XML 插入到表>
-- =============================================
ALTER PROCEDURE [dbo].[pro_insertXML]
 @xml varchar(max) = ''

AS
BEGIN

    SET NOCOUNT ON;
    
    BEGIN TRY
        DECLARE @paraXml XML;    
    BEGIN TRANSACTION --开始事务
        SET @paraXml = CONVERT(XML, @xml);

        INSERT INTO D_PortlCoutInfo(PortId,Count,Added,Note,ReadTime)
        select aa.PortId,aa.Count,aa.Added,aa.Note,getDate()from
        (
            SELECT T.C.value('@PortId','varchar(50)') as PortId,
            T.C.value('@Count','int') as Count,
            T.C.value('@Added','int') as Added,
            T.C.value('@Note','varchar(200)') as Note
            FROM @paraXml.nodes('//PortInfo') as T(C)    
        ) as  aa

        COMMIT TRANSACTION --提交事务
         END TRY
         BEGIN CATCH
            /**//*-------- 事务回滚 --------*/
              ROLLBACK TRANSACTION
              SET NOCOUNT OFF 
              RETURN   -1  
         END CATCH
         SET NOCOUNT OFF
         RETURN 0
  
  END

拼接的XML：

<?xml version="1.0" ?><Info><PortInfo PortId= "1" Count="111" Note="测试1" /><PortInfo PortId= "2" Count="222" Note="测试2" /><PortInfo PortId= "3" Count="333" Note="测试3" /></Info>

 

拼接的XML格式：

<?xml version="1.0" ?>
<Info>
   <PortInfo PortId= "1" Count="111" Note="测试1" />
   <PortInfo PortId= "2" Count="222" Note="测试2" />
   <PortInfo PortId= "3" Count="333" Note="测试3" />
</Info>

 

转载于:https://www.cnblogs.com/xiangshu/articles/4482522.html