BALIW™ developer's notes

-- TEST DECLARE @Xml XML DECLARE @Filename varchar(100) SET @Filename = 'M-PetiksHour-XXX.xml' SELECT @Xml = CONVERT(XML, BulkColumn)  FROM OPENROWSET(BULK 'C:\BizTalk_Files\MW\Maersk\Documentations\JKL-PetiksHour-XXX.xml', SINGLE_BLOB) AS x; -- End of TEST DECLARE  @XmlAltered XML ,@XmlAsNVarChar NVarChar(MAX) SET @XmlAsNVarChar  = CAST(@Xml AS nvarchar(MAX)) SET @XmlAsNVarChar = REPLACE(@XmlAsNVarChar, 'xmlns=""', SPACE(0))   SET @XmlAltered = CAST(@XmlAsNVarChar AS xml) DECLARE @GUID uniqueidentifier   SET @GUID = NEWID()   SELECT  col.value('data(ShipNumber[1])', 'int') as ShipNumber ,col.value('data(AccountPeriod[1])', 'int') as AccountPeriod ,col.value('data(PersonEmployeeId[1])', 'int') as PersonEmployeeId ,col.value('data(RankCode[1])', 'int') as RankCode ,col.value('data(TotalNonConformityDeviation[1])', 'decimal(20,2)') as TotalNonConformityDevi...

            var results = Regex.Split("\"TEST\",123 TEST,\"TEST 99,9\"", ",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");             foreach (var item in results)             {                 Console.WriteLine(item);             }

USE [MyTestDb] GO DROP FUNCTION [dbo].[udf_Global_PadTwoDecimalDigitString] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[udf_Global_PadTwoDecimalDigitString](@StringDecimalValue varchar(25)) RETURNS varchar(25) AS BEGIN DECLARE  @Value varchar(25) ,@ValueAsFloat decimal(20, 3) ,@ValueAsFloatBackToVarchar varchar(25) ,@ValueAsVarcharWith2Decimal varchar(25) SET @Value = @StringDecimalValue -- Ensure that it has decimal values, if not, forced to add .00 SET @Value  = @Value + (CASE WHEN CHARINDEX('.', @Value) = 0 THEN '.00' ELSE SPACE(0) END) -- Secret sauce: SET @ValueAsFloat = CAST(@Value AS decimal(20, 3)) + .001 SET @ValueAsFloatBackToVarchar = CAST(@ValueAsFloat AS decimal(20, 3)) SET @ValueAsVarcharWith2Decimal = SUBSTRING(@ValueAsFloatBackToVarchar, 1, CHARINDEX('.', @ValueAsFloatBackToVarchar) + 2)     RETURN @ValueAsVarcharWith2Decimal END GO