Thoughts on Software

DECLARE @barcode nvarchar(max) = '[)>' + char(30) + '06' + char(29) + '6J123456789987654321' + char(29) + 'P9401234' + char(29) + 'Q1600' + char(29) + 'MPCS' + char(29) + '1T1234561' + char(29) + 'V303456789' + char(29) + 'U777777777' + char(29) + 'K888888888888' + char(29) + '12D20160419' + char(29) + '17D20160418'   + char(30) + '06' + char(29) + '1J123456789987654322' + char(29) + 'P9401234' + char(29) + 'Q400' + char(29) + 'MPCS' + char(29) + '15K123456789' + char(29) + '1T1234561'   + char(30) + '06' + char(29) + '1J123456789987654323' + char(29) + 'P9401234' + char(29) + 'Q400' + char(29) + 'MPCS' + char(29) + '15K123456789' + char(29) + '1T1234561'   + char(30) + '06' + char(29) + '1J123456789987654324' + char(29) + 'P9401234' + char(29) + 'Q400' + char(29) + 'MPCS' + char(29) + '15K123456789' + char(29) + '1T1234561'   + char(30) + '06' + char(29) + '1J123456789987654325' + char(29) + 'P9401234' + char(29) + 'Q400' + char(29) + 'MPCS' + char(29) + '15K123456789' + char(29) + '1T1234561'   + char(30) + '<EOT>' ;With numbers as ( SELECT ROW_NUMBER() over ( order by number) as rn from master..spt_values ) ,Positions as ( SELECT @barcode barcode, CHARINDEX(char(30)+'06',@barcode,rn) as position from numbers n WHERE 1=1 AND N.rn=CHARINDEX(char(30)+'06',@barcode,rn) AND N.rn <= len(@barcode) ) ,RawRecords as ( SELECT Barcode,IsNull(Lead(position) over (Order by position),Len(barcode)-4) as Next, Position   FROM Positions ) ,records as ( SELECT substring(barcode, position+3,next-position-3) as Record ,Case Row_Number() over  (order by position) When 1 then 'Parent' else 'Child' + cast( Row_Number() over  (order by position) -1  as varchar) END as x ,Row_Number() over  (order by position) as rn from RawRecords ) ,EntireRawRow as ( SELECT CHARINDEX(char(29),R.Record,N.rn) as position ,R.Record ,R.x --,r.rn ,ROW_NUMBER() Over (order by r.rn) as rn FROM Numbers N Cross Join records R WHERE 1=1 AND N.rn=CHARINDEX(char(29),R.Record,N.rn) AND N.rn <= len(R.Record) ) ,RawFields as ( SELECT  X , rn , Record , position , ISNULL( Lead( position ) over (order by rn), len(record)) as NextPosition , Case WHEN (x <> Lead( x ) over (order by rn)) THEN Null ELSE 1 END as SameParent , Lead( position ) over (order by rn) - Position as LENN from EntireRawRow ) ,DataIdentifiers as ( SELECT ID, len(ID) as [Len], [Name] FROM ( VALUES ('P','Part') ,('Q','Qty') ,('M','UofM' ) ,('V','Vendor') ,('U','Customer') ,('K','PO Number') ,('D','Print Date') ,('B','Container Type') ,('1J','Licence Plate Single Label') ,('5J','Licence Plate Mix Label') ,('6J','Licence Plate Master Serial') ,('1T','Traceability Number') ,('15K','Kanban') ,('14D','Expiry Date') ,('16D','Production Date') ,('17D','Mfg Date') ,('12D','Date2') ) as did(ID,  [Name]) ) ,Data as ( SELECT X ,substring(record, position+1, Case When NextPosition - Position > 0 then (NextPosition - Position) WHEN SameParent is null then Len(record) ELSE LENN End  ) as B FROM RawFields ) SELECT X ,CASE WHEN DID.ID is not NULL then '"' + DID.Name + ':" "' + SUBSTRING(D.b,Did.Len+1,Len(b)-Did.Len) + '"' ELSE B END as JSON ,CASE WHEN DID.ID is not NULL then '<' + DID.Name + '>' + SUBSTRING(D.b,Did.Len+1,Len(b)-Did.Len) + '</' + DID.Name + '>' ELSE B END as XML from DATA D Left outer Join DataIdentifiers DID on LEFT(D.b,Did.[Len]) = DID.ID

I've started to replace SQL CASE statements with pseudo table definitions in CTE's. The results are much more in the spirit of SET theory in my opinion and make the SQL more understandable. They code takes a "Data-Driven" and is easily converted to real tables if the need arises. Example: SELECT             PaintlineID            ,CASE                            WHEN PaintLineId = 0 THEN 'Prime'                            WHEN PaintLineId = 1 THEN 'Hi-Bake'                            WHEN PaintLineId = 2 THEN 'Low-Bake'            END AS PaintLineName FROM SCHEDULE S gets replaced with ;WITH PaintLines AS ( SELECT * FROM ( VALUES (0,'Prime') ,(1,'Hi-Bake') ,(2,'Low-Bake') ,(-1, '') ) as P(PaintLineId, PaintLineName) ) SELECT  PL.PaintLineName , S.PaintLineId FROM SCHEDULE S INNER JOIN PaintLines PL on S.PaintLineID = PL.PaintLineId Now if I need to add a new entry, I only have to add a new line to the pseudo table and leave the basic query intact. I add a dummy entry to the VALUES statement at the bottom so I don't mess up the comma on the last entry. While it may seem that the data is now hard-coded into the query, I would only point out that the same is true when using a CASE statement but it's more difficult to extract. The combination of CTE and VALUES statements have created the ability to use data driven techniques and facilitate SET based queries.