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

问题描述

我有这个存储过程，它从 xml 读取数据并进行一些插入

I have this stored procedure, that reads data from xml and make some inserts

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 可以是空值，这是我的问题:

Everything goes well.Sometimes C1 or C2 or C3 can be nulls and here are my questions:

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

In the procedure, when making the inserts if C1 is null or C2 is null or C3 is null skip that record and not make the insertion

推荐答案

我认为你只需要一个 WHERE 子句.

You just need a WHERE clause I think.

   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 中执行此操作，我想这可能更有效

Or you can do it in the XPath instead which I guess may be more efficient

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

这篇关于存储过程 - 将参数作为 xml 传递并读取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！