How do I trim the log text from the change log?

Epicor ERP 10
8

I had to put this together for an Audit, just throwing it in for reference =) Not the prettiest all I could come up with in 15 minutes

SELECT *, WhoChangedItQuery.LogSplit AS WhoChangedIt, WhatWasChangedQuery.LogSplit AS WhatWasChanged FROM (
       SELECT * FROM
       (
              SELECT
              Company, Identifier, TableName, DateStampedOn,
              LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS LogSplit
              FROM
              (
                     SELECT Ice.ChgLog.Company, Ice.ChgLog.Identifier, Ice.ChgLog.TableName, Ice.ChgLog.DateStampedOn, CAST('<XMLRoot><RowData>' + REPLACE(Ice.ChgLog.LogText,'   ','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
                     FROM   Ice.ChgLog
                     WHERE Ice.ChgLog.Identifier IN ('Company', 'Plant', 'PlantConfCtrl') AND DateStampedOn >= '1/1/2018'
              ) AS t

              CROSS APPLY x.nodes('/XMLRoot/RowData')m(n) 
       ) mark
       WHERE LEN(mark.LogSplit) <= 6 AND mark.LogSplit NOT LIKE '%smSupplierID%' AND mark.LogSplit NOT LIKE '%NextCustID%'
) WhoChangedItQuery

JOIN

(
       SELECT * FROM
       (
              SELECT
              Company, Identifier, TableName, DateStampedOn,
              LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS LogSplit
              FROM
              (
                     SELECT Ice.ChgLog.Company, Ice.ChgLog.Identifier, Ice.ChgLog.TableName, Ice.ChgLog.DateStampedOn, CAST('<XMLRoot><RowData>' + REPLACE(Ice.ChgLog.LogText,'   ','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
                     FROM   Ice.ChgLog
                     WHERE Ice.ChgLog.Identifier IN ('Company', 'Plant', 'PlantConfCtrl') AND DateStampedOn >= '1/1/2018'
              ) AS t

              CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
       ) mark
       WHERE LEN(mark.LogSplit) >= 6 AND mark.LogSplit NOT LIKE '%smSupplierID%' AND mark.LogSplit NOT LIKE '%NextCustID%'
) WhatWasChangedQuery ON WhatWasChangedQuery.Company = WhoChangedItQuery.Company AND WhatWasChangedQuery.Identifier = WhoChangedItQuery.Identifier AND WhatWasChangedQuery.TableName = WhoChangedItQuery.TableName AND WhatWasChangedQuery.DateStampedOn = WhoChangedItQuery.DateStampedOn

WHERE WhatWasChangedQuery.LogSplit NOT LIKE '%New Record%'
1 Like