Monday, 20 October 2014

INSERT from OPENXML inserts NULL rows



I have a piece of code which shreds XML files to table values. The problem is that for every order response I insert a few rows with all NULL columns are inserted too. How can I fix it?



EXEC sys.sp_xml_preparedocument @DocHandle OUTPUT
,@XmlDocument;

INSERT INTO XmlOrderResponses (
OrderResponseType
,OrderResponseNumber
,OrderResponseDate
,DocumentFunctionCode
,Remarks
)
SELECT *
FROM OPENXML(@DocHandle, '/Document-OrderResponse/*', 11) WITH (
OrderResponseType INT
,OrderResponseNumber NVARCHAR(40)
,OrderResponseDate DATETIME
,DocumentFunctionCode NVARCHAR(40)
,Remarks INT
);

EXEC sys.sp_xml_removedocument @DocHandle;

No comments:

Post a Comment