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