Wednesday, December 3, 2008

Compare XML data with SQL 2000 table

In SQL 2000, I wrote some code in stored procedure in SQL 2000 openxml. I need check for the data duplication before inserting the value,
Create Proc prdSaveHPCatalog(@xml TEXT, @brand varchar(200), @isExisted bit output)
AS
SET NOCOUNT ON

DECLARE @IDocumentHandle int
DECLARE @isExisted bit

EXECUTE sp_xml_preparedocument @iDocumentHandle output, @xml
---- check if the data already existed
if not exists( select 1 from OpenXML(@iDocumnetHandle, '/productCatalog/header',2) WITH (batchRunDt varchar(20) 'barchRunDt') HeaderRunTime WHERE HeaderRunTime.batchRunDt in (select batchRunDt from CategoryHeader)
)
set @isExisted = 1

No comments: