1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
|
USE [ZoneCustomerExchange]
GO
/****** Object: StoredProcedure [dbo].[SetDataCategoriesTravelNote] Script Date: 19-10-16 11:11:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SetDataCategories]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @idFile int
--travelfile
DECLARE XmlData CURSOR FOR
SELECT
[dbo].[FilesXML].IDFilesXML
FROM [dbo].[FilesXML]
WHERE
[dbo].[FilesXML].Parite = 1
AND [dbo].[FilesXML].FKSource = 1
AND [dbo].[FilesXML].DateExplore IS NULL
AND [dbo].[FilesXML].Type = 'Categories'
OPEN XmlData
FETCH XmlData INTO @idFile
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO [dbo].[category]
( [IDSourceIdCategory],[labelcategoryfr],[labelcategorynl],[active]
,[IDSource],[createddate],[createdby]
)
SELECT
pref.value('(idcategory/text())[1]', 'varchar(255)') as IDSourceIdCategory,
pref.value('(labelcategoryfr/text())[1]', 'varchar(255)') as labelcategoryfr,
pref.value('(labelcategorynl/text())[1]', 'varchar(255)') as labelcategorynl,
pref.value('(Active/text())[1]', 'bit') as Active,
1 as IdSource,
getdate() as createddate,
'SYSTEM' as createdby
FROM
FilesXML CROSS APPLY
XmlData.nodes('/Root/Category') AS Category(pref)
WHERE
FilesXML.IDFilesXML = @idFile
AND NOT EXISTS (
SELECT [dbo].[category].[idcategory]
FROM [dbo].[category]
WHERE [IDSourceIdCategory] = pref.value('(idcategory/text())[1]', 'varchar(255)')
)
FETCH XmlData INTO @idFile
END
CLOSE XmlData
DEALLOCATE XmlData
END |
Partager