Créer le même code à adapter à chaque table selon les champs et le type.
Cela peut être une activité assez plate. On peut créer un code qui crée notre code. Peut-on appeler une métacreation, ou métacodification, mais, peut-être, il n’existe pas ce mot en français.
Alors, ce SQL script nous liste les champs de tables, et après, c’est une autre histoire.
1BEGIN TRY DROP TABLE #TMP END TRY BEGIN CATCH END CATCH;
2SELECT
3 TABLE_SCHEMA
4 ,TABLE_NAME
5 ,ORDINAL_POSITION AS rownumber
6 ,COLUMN_NAME AS 'ColumnName'
7 ,DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END AS 'DataType'
8 ,IS_NULLABLE
9INTO #TMP
10FROM INFORMATION_SCHEMA.COLUMNS
11WHERE TABLE_SCHEMA = @SchemaSource AND TABLE_NAME = @TableSource;
Utilisation
1
2DECLARE @TABLE_NAME as NVARCHAR(100);
3DECLARE @TABLE_SCHEMA as NVARCHAR(100);
4DECLARE @MyElement as NVARCHAR(100);
5DECLARE @MyFieldXml as NVARCHAR(100);
6SET @TABLE_NAME = 'Customer'
7SET @TABLE_SCHEMA = 'dbo'
8--MyFieldXml
9SET @MyElement = 'msg_' + @TABLE_NAME; -- we must check if this name is ok.
10SET @MyFieldXml = 'Message'; -- we must check if this name is ok.
11
12SET NOCOUNT ON
13
14BEGIN TRY DROP TABLE #TMP END TRY BEGIN CATCH END CATCH;
15SELECT
16 TABLE_SCHEMA
17 ,TABLE_NAME
18 ,ORDINAL_POSITION AS rownumber
19 ,COLUMN_NAME AS 'ColumnName'
20 ,DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END AS 'DataType'
21 ,IS_NULLABLE
22 ,'@'+REPLACE(COLUMN_NAME,' ','_') + ' AS ' + DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END as procedureParameter
23 , 'x.m.value(''('+COLUMN_NAME+')[1]'', '''+DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END+''') as ['+COLUMN_NAME+']' as xmlParameterV1
24 , '['+@MyFieldXml+'].value(''('+@MyElement+'/'+TABLE_NAME+'/@'+COLUMN_NAME+')[1]'', '''+DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END+''') as ['+COLUMN_NAME+']' as xmlParameterV2
25 , 'DECLARE @'+REPLACE(COLUMN_NAME,' ','_') + ' AS ' + DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END + ';' AS [DeclareVariable]
26 , '['+COLUMN_NAME+'] ' + + DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END + CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL' END AS CreateField
27INTO #TMP
28FROM INFORMATION_SCHEMA.COLUMNS
29WHERE TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME;
30
31DECLARE @SQL AS NVARCHAR(MAX);
32SET @SQL = '';
33
34SELECT @SQL = @SQL
35 + CASE WHEN @SQL = '' THEN ' ' ELSE ', ' END
36 + xmlParameterV2
37 + CHAR(13) + CHAR(10)
38FROM
39#TMP;
40
41SET @SQL = 'SELECT'+ CHAR(13) + CHAR(10)
42 + @SQL
43 + 'FROM ['+@TABLE_NAME+']'+ CHAR(13) + CHAR(10)
44 + 'WHERE ['+@MyFieldXml+'].exist(''/'+@MyElement+'/'+@TABLE_NAME+''') = 1'
45
46PRINT @SQL
Resultat
1
2SELECT
3 [Message].value('(msg_Customer/Customer/@CustomerId)[1]', 'int') as [CustomerId]
4, [Message].value('(msg_Customer/Customer/@Timestamp)[1]', 'timestamp') as [Timestamp]
5, [Message].value('(msg_Customer/Customer/@CustomerNumber)[1]', 'int') as [CustomerNumber]
6, [Message].value('(msg_Customer/Customer/@CustomerName)[1]', 'nvarchar(100)') as [CustomerName]
7FROM [Customer]
8WHERE [Message].exist('/msg_Customer/Customer') = 1
Comments