Last active
May 7, 2019 14:23
-
-
Save ryanvs/49634eccf9684e4b60a8585133eb300d to your computer and use it in GitHub Desktop.
SQL script transform MES Middleware XML to EXEC PROC
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Paste Middleware XML in @xml parameter value | |
| DECLARE @xml xml = N'<request><object>QDC_sample</object><msgtype>getspec</msgtype><cmd>GetTableData</cmd><filter><start_time_utc>2017-11-16T12:02:51</start_time_utc><end_time_utc>2018-02-14T17:02:51.283857</end_time_utc><num_rows>180</num_rows><ent_id>32</ent_id><cols><columns><column><id>Col1</id><dataType>Attributes</dataType><label>Shift</label><attrId>38</attrId><operation>Forming Machine</operation><charId /><field>sa.attr_value</field><QDCSpecification>38946</QDCSpecification><queryType>sample_attr</queryType><orderOrLocation>valueNo</orderOrLocation><valueNo /><location /></column><column><id>Col2</id><dataType>Attributes</dataType><label>Crew</label><attrId>35</attrId><operation>Forming Machine</operation><charId /><field>sa.attr_value</field><QDCSpecification>38946</QDCSpecification><queryType>sample_attr</queryType><orderOrLocation>valueNo</orderOrLocation><valueNo /><location /></column><column><id>Col3</id><dataType>Characteristic</dataType><label>Avg Basis Weight</label><attrId /><operation>Forming Machine</operation><charId>658</charId><field>round(scl.mean, c.num_decimals)</field><QDCSpecification>38946</QDCSpecification><queryType>sample_char_link</queryType><orderOrLocation>valueNo</orderOrLocation><valueNo /><location /></column><column><id>Col4</id><dataType>Characteristic</dataType><label>Mat Thickness</label><attrId /><operation>Forming Machine</operation><charId>731</charId><field>round(scl.mean, c.num_decimals)</field><QDCSpecification>38946</QDCSpecification><queryType>sample_char_link</queryType><orderOrLocation>valueNo</orderOrLocation><valueNo /><location /></column><column><id>Col5</id><dataType>Characteristic</dataType><label>%Moisture(W)</label><attrId /><operation>Forming Machine</operation><charId>1364</charId><field>round(scl.mean, c.num_decimals)</field><QDCSpecification>38946</QDCSpecification><queryType>sample_char_link</queryType><orderOrLocation>valueNo</orderOrLocation><valueNo /><location /></column><column><id>Col6</id><dataType>Sample</dataType><label>Item</label><attrId /><operation>Mix Chest</operation><charId /><field>sample.item_id</field><QDCSpecification /><queryType>sample</queryType><orderOrLocation>valueNo</orderOrLocation><valueNo /><location /></column></columns></cols><current_item>0</current_item><rowOrganization>sample</rowOrganization><showUnlabeledMeasure>0</showUnlabeledMeasure><display_item_id>GBPMR10602.171</display_item_id><lang_id>1007</lang_id></filter></request>' | |
| -- Internal variables | |
| DECLARE @object nvarchar(80) | |
| , @msgtype nvarchar(80) | |
| , @cmd nvarchar(80) | |
| , @spname nvarchar(80) | |
| -- Get the object and command | |
| SELECT @object = x.y.value('object[1]' , 'nvarchar(80)') | |
| , @msgtype= x.y.value('msgtype[1]', 'nvarchar(80)') | |
| , @cmd = x.y.value('cmd[1]' , 'nvarchar(80)') | |
| FROM @xml.nodes('/request') AS x(y) | |
| -- Build the stored procedure name. TODO: Assume SELECT ALL (SA) for now, but need to enhance | |
| SET @spname = CASE | |
| WHEN @cmd = 'add' THEN 'sp_I_' + @object | |
| WHEN @cmd = 'update' THEN 'sp_U_' + @object | |
| WHEN @cmd = 'exec' THEN 'sp_U_' + @object | |
| WHEN @cmd = 'getbykey' THEN 'sp_S_' + @object | |
| WHEN @cmd = 'getall' THEN 'sp_SA_' + @object | |
| WHEN @msgtype = 'exec' THEN 'sp_U_' + @object + '_' + @cmd | |
| ELSE 'sp_SA_' + @object + '_' + @cmd | |
| END | |
| IF OBJECT_ID(@spname) IS NULL | |
| PRINT 'Stored procedure NOT FOUND: ' + COALESCE(@spname, '#ERROR#') | |
| DECLARE @filter TABLE ( | |
| parameter nvarchar(80) | |
| , value nvarchar(max) | |
| , datatype nvarchar(40) | |
| , basetype nvarchar(40) | |
| , decltype nvarchar(40) | |
| , max_length int | |
| , match int DEFAULT 0 | |
| , quote bit DEFAULT 0 | |
| , is_output bit DEFAULT 0 | |
| , id int IDENTITY(1, 1) | |
| ) | |
| -- Get the parameters/filters | |
| IF @msgtype = 'exec' | |
| BEGIN | |
| DECLARE @nodeName nvarchar(80) | |
| SELECT @nodeName = x.y.value('local-name(.)', 'nvarchar(80)') | |
| FROM @xml.nodes('/request/*') AS x(y) | |
| PRINT 'Node name: ' + COALESCE(@nodeName, 'NULL') | |
| INSERT @filter (parameter, value) | |
| SELECT name, value | |
| FROM ( | |
| SELECT '@' + x.y.value('local-name(.)', 'nvarchar(80)') AS [name] | |
| , x.y.value('.[1]' , 'nvarchar(max)') AS [value] | |
| FROM @xml.nodes('/request/*[local-name(.)=sql:variable("@nodeName")]/*') AS x(y) | |
| ) AS x | |
| WHERE name <> '@session_id' | |
| END | |
| ELSE | |
| BEGIN | |
| INSERT @filter (parameter, value) | |
| SELECT name, value | |
| FROM ( | |
| SELECT '@' + x.y.value('local-name(.)', 'nvarchar(80)') AS [name] | |
| , x.y.value('.[1]' , 'nvarchar(max)') AS [value] | |
| FROM @xml.nodes('/request/filter/*') AS x(y) | |
| ) AS x | |
| WHERE name <> '@session_id' | |
| END | |
| -- Try to match the filter parameters with the stored procedure parameters | |
| UPDATE f | |
| SET datatype = TYPE_NAME(p.user_type_id) | |
| , basetype = TYPE_NAME(p.system_type_id) | |
| , decltype = TYPE_NAME(p.system_type_id) | |
| , is_output = p.is_output | |
| , max_length = p.max_length | |
| , quote = CASE | |
| WHEN TYPE_NAME(system_type_id) IN ('tinyint', 'smallint', 'int', 'bigint', 'bit', 'decimal', 'float', 'smallmoney', 'money', 'real') THEN 0 | |
| ELSE 1 | |
| END | |
| , match = 1 | |
| FROM @filter f | |
| JOIN sys.parameters p ON p.object_id = OBJECT_ID(@spname) AND f.parameter = p.name | |
| -- Append unused parameters | |
| INSERT @filter(parameter, value, datatype, basetype, decltype, is_output, max_length, match, quote) | |
| SELECT p.name | |
| , 'NULL' | |
| , TYPE_NAME(p.user_type_id) | |
| , TYPE_NAME(p.system_type_id) | |
| , TYPE_NAME(p.system_type_id) | |
| , p.is_output | |
| , p.max_length | |
| , -1 | |
| , 0 | |
| FROM @filter f | |
| RIGHT JOIN (SELECT * FROM sys.parameters p WHERE p.object_id = OBJECT_ID(@spname)) p ON f.parameter = p.name | |
| WHERE f.id IS NULL | |
| UPDATE f | |
| SET max_length = max_length / 2 | |
| FROM @filter f | |
| WHERE basetype IN ('nchar', 'nvarchar') | |
| -- Get the length for variable types | |
| UPDATE f | |
| SET decltype = basetype + '(' + IIF(max_length = 0, 'MAX', CAST(max_length AS varchar)) + ')' | |
| FROM @filter f | |
| WHERE basetype IN ('char', 'varchar', 'nchar', 'nvarchar') | |
| -- DEBUGGING: Show the parameters/filters | |
| SELECT * FROM @filter | |
| -- Store the output lines in a table | |
| DECLARE @s TABLE (text nvarchar(max)) | |
| IF OBJECT_ID(@spname) IS NULL | |
| INSERT @s VALUES('-- ****WARNING: Stored procedure NOT FOUND: ' + COALESCE(@spname, '#ERROR#') + ', DB=' + DB_NAME()) | |
| -- Build the DECLARE for output parameters | |
| IF 1 = 1 OR EXISTS(SELECT 1 FROM @filter WHERE datatype IS NOT NULL AND is_output = 1) | |
| BEGIN | |
| INSERT @s | |
| SELECT 'DECLARE' | |
| INSERT @s | |
| SELECT ' ' + IIF(rn = 1, ' ', ',') + parameter + REPLICATE(' ', 30 - LEN(parameter)) + ' ' + decltype + REPLICATE(' ', 20 - LEN(decltype)) + ' = ' + | |
| IIF(quote = 1, 'N''' + REPLACE(value, '''', '''''') + '''', value) | |
| FROM ( | |
| SELECT * | |
| , ROW_NUMBER() OVER (ORDER BY id) AS rn | |
| FROM @filter | |
| WHERE datatype IS NOT NULL --AND is_output = 1 | |
| ) AS f | |
| ORDER BY id | |
| INSERT @s | |
| SELECT '' | |
| END | |
| -- Build the stored procedure call | |
| INSERT @s | |
| SELECT 'EXEC ' + COALESCE(@spname, '#ERROR#') | |
| INSERT @s | |
| SELECT ' ' + IIF(rn = 1, ' ', ',') | |
| + parameter + REPLICATE(' ', 30 - LEN(parameter)) | |
| + ' = ' + parameter + | |
| CASE is_output | |
| WHEN 1 THEN REPLICATE(' ', 30 - LEN(parameter)) + ' OUTPUT' | |
| ELSE '' | |
| END | |
| FROM ( | |
| SELECT * | |
| , ROW_NUMBER() OVER(ORDER BY id) AS rn | |
| FROM @filter | |
| ) AS f | |
| ORDER BY id | |
| -- Show the OUTPUT parameter results | |
| IF EXISTS(SELECT 1 FROM @filter WHERE datatype IS NOT NULL AND is_output = 1) | |
| BEGIN | |
| INSERT @s | |
| SELECT '' | |
| UNION SELECT 'SELECT' | |
| INSERT @s | |
| SELECT ' ' + IIF(rn = 1, ' ', ',') | |
| + parameter | |
| + REPLICATE(' ', 30 - LEN(parameter)) | |
| + ' AS ' | |
| + QUOTENAME(SUBSTRING(parameter, 2, LEN(parameter)-1)) | |
| FROM ( | |
| SELECT * | |
| , ROW_NUMBER() OVER(ORDER BY id) AS rn | |
| FROM @filter | |
| WHERE datatype IS NOT NULL AND is_output = 1 | |
| ) AS f | |
| ORDER BY id | |
| END | |
| -- ============ | |
| -- Final output | |
| SELECT * FROM @s |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment