admin

存储过程-将参数作为xml传递并读取数据

sql

我有此存储过程,该过程从xml读取数据并进行一些插入

ALTER procedure [dbo].[SP_InsertIOs] 
    @iosxml xml AS

DECLARE @currRecord int

-- parse the records from the XML
  EXECUTE sp_xml_preparedocument @currRecord OUTPUT, @iosxml     
  BEGIN TRY       
   INSERT INTO SN_IO ( [C1] ,[C2]  ,[C3] )
   SELECT [C1] ,[C2] ,[C3]
   FROM OPENXML (@currRecord, 'ios/io', 1)
   WITH ([C1] [varchar](25)       'C1',
         [C2] [varchar](25)       'C2',
         [C3] [varchar](20)       'C3'  )                                                                  
    END TRY
    BEGIN CATCH
        //SELECT SOME ERROR
    END CATCH
    EXECUTE sp_xml_removedocument @currRecord

xml看起来像这样

<ios>
  <io>
    <C1>a</C1>
    <C2>b</C2>
    <C3>c</C3>    
  </io>
  <io>
    <C1>x</C1>
    <C2>y</C2>
    <C3>z</C3>
  </io>
</ios>

一切顺利。有时C1或C2或C3可以为空,这是我的问题:

在该过程中,如果插入时C1为空或C2为空或C3为空,则跳过该记录而不进行插入


阅读 201

收藏
2021-07-01

共1个答案

admin

我只需要一个WHERE子句。

   INSERT INTO SN_IO ( [C1] ,[C2]  ,[C3] )
   SELECT [C1] ,[C2] ,[C3]
   FROM OPENXML (@currRecord, 'ios/io', 1)
   WITH ([C1] [varchar](25)       'C1',
         [C2] [varchar](25)       'C2',
         [C3] [varchar](20)       'C3'  )    
    WHERE  [C1]  IS NOT NULL  AND [C2]  IS NOT NULL AND [C3] IS NOT NULL

或者,您也可以在XPath中做到这一点,我想它可能会更有效

   FROM OPENXML (@currRecord, 'ios/io[C1 and C2 and C3]', 1)
2021-07-01