我有此存储过程,该过程从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为空,则跳过该记录而不进行插入
我只需要一个WHERE子句。
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)