Sql 获取XML 值

ALTER PROCEDURE [dbo].[Addr_SP_Contactor_AddMutilField]
    @SeqNo INT ,
    @CompID INT ,
    @ContactorID BIGINT ,
    @ContactWay XML ,
    @ConfParticipatePhoneNo VARCHAR(50)
AS
    SET NOCOUNT ON
/*
<ContactWay>
    <Way>
        <WayType>1</WayType>
        <WayField>13810712519</WayField>
    </Way>
    <Way>
        <WayType>3</WayType>
        <WayField>346425159@qq.com</WayField>
    </Way>
</ContactWay>
*/
    DELETE  dbo.Addr_TB_ContactField
    WHERE   SeqNo = @SeqNo
            AND CompID = @CompID
            AND ContactorID = @ContactorID

    INSERT  INTO dbo.Addr_TB_ContactField
            ( ContactorID ,
              SeqNo ,
              CompID ,
              Field ,
              FieldType
            )
            SELECT  @ContactorID ,
                    @SeqNo ,
                    @CompID ,
                    Field = T.c.value('(./WayField/text())[1]', 'VARCHAR(200)') ,
                    FieldType = T.c.value('(./WayType/text())[1]', 'INT')
            FROM    @ContactWay.nodes('/ContactWay/Way') AS T ( c )

转载于:https://www.cnblogs.com/andymusic/p/4181864.html