Transform table definition to XML select, it can be useful when we need to create many XML select or re-create it.
Result:
1
2SELECT
3 [Message].value('(msg_Customers/Customers/CustomerID)[1]', 'nchar(5)') as [CustomerID]
4, [Message].value('(msg_Customers/Customers/CompanyName)[1]', 'nvarchar(40)') as [CompanyName]
5, [Message].value('(msg_Customers/Customers/ContactName)[1]', 'nvarchar(30)') as [ContactName]
6, [Message].value('(msg_Customers/Customers/ContactTitle)[1]', 'nvarchar(30)') as [ContactTitle]
7, [Message].value('(msg_Customers/Customers/Address)[1]', 'nvarchar(60)') as [Address]
8, [Message].value('(msg_Customers/Customers/City)[1]', 'nvarchar(15)') as [City]
9, [Message].value('(msg_Customers/Customers/Region)[1]', 'nvarchar(15)') as [Region]
10, [Message].value('(msg_Customers/Customers/PostalCode)[1]', 'nvarchar(10)') as [PostalCode]
11, [Message].value('(msg_Customers/Customers/Country)[1]', 'nvarchar(15)') as [Country]
12, [Message].value('(msg_Customers/Customers/Phone)[1]', 'nvarchar(24)') as [Phone]
13, [Message].value('(msg_Customers/Customers/Fax)[1]', 'nvarchar(24)') as [Fax]
14FROM [Customers]
15WHERE [Message].exist('/msg_Customers/Customers') = 1
Why do we need this? Well, I’m lazy to create and write the same code again and again. This code creates the first code, metacoding, to select a table that will be a XML. We can say that is complicate and specify but when you need create a lot of the same script.
Usually, we have XSD definition, now, the problem is convert XSD to tables.
1
2DECLARE @TABLE_NAME as NVARCHAR(100);
3DECLARE @TABLE_SCHEMA as NVARCHAR(100);
4DECLARE @MyElement as NVARCHAR(100);
5DECLARE @MyFieldXml as NVARCHAR(100);
6
7SET @TABLE_NAME = 'Customers'
8SET @TABLE_SCHEMA = 'dbo'
9
10--MyFieldXml
11SET @MyElement = 'msg_' + @TABLE_NAME; -- we must check if this name is ok.
12SET @MyFieldXml = 'Message'; -- we must check if this name is ok.
13SET NOCOUNT ON
14
15BEGIN TRY DROP TABLE #TMP END TRY BEGIN CATCH END CATCH;
16SELECT
17 TABLE_SCHEMA
18 ,TABLE_NAME
19 ,ORDINAL_POSITION AS rownumber
20 ,COLUMN_NAME AS 'ColumnName'
21 ,DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END AS 'DataType'
22 ,IS_NULLABLE
23 ,'@'+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
24 , '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
25 , '['+@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
26 , '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]
27 , '['+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
28INTO #TMP
29FROM INFORMATION_SCHEMA.COLUMNS
30WHERE TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME;
31
32DECLARE @SQL AS NVARCHAR(MAX);
33SET @SQL = '';
34
35SELECT @SQL = @SQL
36 + CASE WHEN @SQL = '' THEN ' ' ELSE ', ' END
37 + xmlParameterV2
38 + CHAR(13) + CHAR(10)
39FROM
40#TMP;
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
Comments