-- Citations_TMGimport_splitCDtoCD1-9.sql
/*
2014-09-21 Tom Holden ve3meo
2014-09-29 adapted from Citations_TMGimport_splitCDtoCD1-2.sql to handle up to CD9

RootsMagic 6.3.2 imports TMG Citation Detail memo field into CitationTable.Fields
as the field [CD] defined in the SourceTemplateTable.Fields regardless of TMG
memo splitter "||". The sentence templates imported do not have the [CD] variable,
if the Citation Detail was split, rather they have variables [CD1], [CD2], up to 9.
Consequently, there is no output of the value from CD. If the sentence templates 
were changed to use [CD] then the whole value including the "||" separator is 
output, to the detriment of the order of the sentence and to the length of the
short footnote.

This query addresses the general case where the Citation Detail memo is split
into any number of parts. It splits and replaces [CD] in CitationTable.Fields into the 
parts [CD1] up to [CD9]. It splits and replaces Citation Detail [CD] in the fields 
definition in SourceTemplateTable.FieldDefs for the source templates used by these 
citations.
*/

/* 
xCitFields will store temporarily the CitationID, text form of the 
CitationTable.Fields, and the TemplateID of the source template used
*/
DROP VIEW IF EXISTS xCitFields
;
CREATE TEMP VIEW xCitFields
AS
SELECT 
  C.CitationID
  , ST.TemplateID
  , CAST(C.Fields AS TEXT) AS CitFieldsText
--  , ST.Footnote
FROM CitationTable C
INNER JOIN SourceTable S USING(SourceID)              -- connect citation to master source in order to
INNER JOIN SourceTemplateTable ST USING(TemplateID)   -- connect the citation to the source template
WHERE CitFieldsText                               -- Source details values
LIKE '%<Name>CD</Name><Value>%||%</Value>%'       -- Citation must have [CD] field with || in it
AND FootNote REGEXP '[^\[]*\[Cd[1-9]{1}\].*'       -- footnote sentence template must have CDn
--AND Footnote NOT REGEXP '[^\[]*\[Cd[3-9]{1}\].*'  -- but not have Cd3 or greater
;

DROP TABLE IF EXISTS xCitFieldsTable
;
CREATE TEMP TABLE xCitFieldsTable
(CitationID INTEGER PRIMARY KEY
 , TemplateID INTEGER
 , CitFieldsText TEXT
 , CD TEXT DEFAULT ''
 , CD1 TEXT  DEFAULT ''
 , CD2 TEXT DEFAULT ''
 , CD3 TEXT DEFAULT ''
 , CD4 TEXT DEFAULT ''
 , CD5 TEXT DEFAULT ''
 , CD6 TEXT DEFAULT ''
 , CD7 TEXT DEFAULT ''
 , CD8 TEXT DEFAULT ''
 , CD9 TEXT DEFAULT ''
)
;

-- Keep the xCitFields View to see that it diminishes after the revision
-- Copy the xCitFields View to the temp table for processing 
INSERT INTO xCitFieldsTable
SELECT 
  CitationID
  , TemplateID
  , CitFieldsText
  , '' AS CD
  , '' AS CD1
  , '' AS CD2
  , '' AS CD3
  , '' AS CD4
  , '' AS CD5
  , '' AS CD6
  , '' AS CD7
  , '' AS CD8
  , '' AS CD9
FROM xCitFields
;

-------------- Parse the xCitFieldsTable
-- Delete the Citations with no value for CD
DELETE FROM xCitFieldsTable
WHERE CitFieldsText 
LIKE '%<Name>CD</Name><Value/>%'
;

-- CD strip leading XML
UPDATE xCitFieldsTable
SET CD = 
SUBSTR
(CitFieldsText
 ,INSTR(CitFieldsText,'<Name>CD</Name><Value>')
  +
  LENGTH('<Name>CD</Name><Value>')
)
;

-- strip trailing XML
UPDATE xCitFieldsTable
SET CD = 
SUBSTR
(CD
 , 1
 , INSTR(CD, '</Value>') -1
)
;   
-- CD value has been extracted, now parse it into CD1-9
UPDATE xCitFieldsTable
SET CD1 =
SUBSTR
(CD
 , 1
 , INSTR(CD, '||') -1
)
;

UPDATE xCitFieldsTable
SET CD2 =
CASE (INSTR(SUBSTR(CD, LENGTH(CD1)+3), '||') -1)
WHEN -1 THEN
  SUBSTR
  (CD
   , LENGTH(CD1) +3
   )
ELSE
  SUBSTR
  (CD
   , LENGTH(CD1) +3
   , INSTR(SUBSTR(CD, LENGTH(CD1) +3), '||') -1
  )
END  
;
--SELECT INSTR(SUBSTR(CD, LENGTH(CD1)+2 +1), '||') -1 FROM xCitFieldsTable

UPDATE xCitFieldsTable
SET CD3 =
CASE (INSTR(SUBSTR(CD, LENGTH(CD1 || CD2)+5), '||') -1)
WHEN -1 THEN
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2) +5
   )
ELSE
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2) +5
   , INSTR(SUBSTR(CD, LENGTH(CD1 || CD2)+5), '||') -1
  )
END  
;

UPDATE xCitFieldsTable
SET CD4 =
CASE (INSTR(SUBSTR(CD, LENGTH(CD1 || CD2 || CD3)+7), '||') -1)
WHEN -1 THEN
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2 || CD3)+7
   )
ELSE
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2 || CD3)+7
   , INSTR(SUBSTR(CD, LENGTH(CD1 || CD2 || CD3)+7), '||') -1
  )
END  
;

UPDATE xCitFieldsTable
SET CD5 =
CASE (INSTR(SUBSTR(CD, LENGTH(CD1 || CD2 || CD3 || CD4)+9), '||') -1)
WHEN -1 THEN
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2 || CD3 || CD4)+9
   )
ELSE
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2 || CD3 || CD4)+9
   , INSTR(SUBSTR(CD, LENGTH(CD1 || CD2 || CD3 || CD4)+9), '||') -1
  )
END  
;

UPDATE xCitFieldsTable
SET CD6 =
CASE (INSTR(SUBSTR(CD, LENGTH(CD1 || CD2 || CD3 || CD4 || CD5)+11), '||') -1)
WHEN -1 THEN
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2 || CD3 || CD4 || CD5)+11
   )
ELSE
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2 || CD3 || CD4 || CD5)+11
   , INSTR(SUBSTR(CD, LENGTH(CD1 || CD2 || CD3 || CD4 || CD5)+11), '||') -1
  )
END  
;

UPDATE xCitFieldsTable
SET CD7 =
CASE (INSTR(SUBSTR(CD, LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6)+13), '||') -1)
WHEN -1 THEN
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6)+13
   )
ELSE
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6)+13
   , INSTR(SUBSTR(CD, LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6)+13), '||') -1
  )
END  
;

UPDATE xCitFieldsTable
SET CD8 =
CASE (INSTR(SUBSTR(CD, LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6 || CD7)+15), '||') -1)
WHEN -1 THEN
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6 || CD7)+15
   )
ELSE
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6 || CD7)+15
   , INSTR(SUBSTR(CD, LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6 || CD7)+15), '||') -1
  )
END  
;


UPDATE xCitFieldsTable
SET CD9 =
CASE (INSTR(SUBSTR(CD, LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6 || CD7 || CD8)+17), '||') -1)
WHEN -1 THEN
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6 || CD7 || CD8)+17
   )
ELSE
  SUBSTR
  (CD
   , LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6 || CD7 || CD8)+17
   , INSTR(SUBSTR(CD, LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6 || CD7 || CD8)+17), '||') -1
  )
END  
;
--------- Finished parsing into CD1-9 in temp table
/*
SELECT
    TemplateID 
   , CD
   , LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6)+12 +1 AS Start
   , INSTR(SUBSTR(CD, LENGTH(CD1 || CD2 || CD3 || CD4 || CD5 || CD6)+12), '||') -1 AS Length
FROM xCitFieldsTable
*/
-- What is the max CD# used in the template sentences?
DROP TABLE IF EXISTS xCDmaxTable
;

CREATE TEMP TABLE xCDmaxTable (TemplateID INTEGER PRIMARY KEY, CDmax INTEGER)
;

INSERT INTO xCDmaxTable
SELECT TemplateID, 9 FROM SourceTemplateTable 
WHERE FootNote || ShortFootnote || Bibliography LIKE '%[CD9]%'
;

INSERT INTO xCDmaxTable
SELECT TemplateID, 8 FROM SourceTemplateTable 
WHERE FootNote || ShortFootnote || Bibliography LIKE '%[CD8]%'
EXCEPT SELECT DISTINCT TemplateID, 8 FROM xCDmaxTable
;

INSERT INTO xCDmaxTable
SELECT TemplateID, 7 FROM SourceTemplateTable 
WHERE FootNote || ShortFootnote || Bibliography LIKE '%[CD7]%'
EXCEPT SELECT DISTINCT TemplateID, 7 FROM xCDmaxTable
;

INSERT INTO xCDmaxTable
SELECT TemplateID, 6 FROM SourceTemplateTable 
WHERE FootNote || ShortFootnote || Bibliography LIKE '%[CD6]%'
EXCEPT SELECT DISTINCT TemplateID, 6 FROM xCDmaxTable
;

INSERT INTO xCDmaxTable
SELECT TemplateID, 5 FROM SourceTemplateTable 
WHERE FootNote || ShortFootnote || Bibliography LIKE '%[CD5]%'
EXCEPT SELECT DISTINCT TemplateID, 5 FROM xCDmaxTable
;

INSERT INTO xCDmaxTable
SELECT TemplateID, 4 FROM SourceTemplateTable 
WHERE FootNote || ShortFootnote || Bibliography LIKE '%[CD4]%'
EXCEPT SELECT DISTINCT TemplateID, 4 FROM xCDmaxTable
;

INSERT INTO xCDmaxTable
SELECT TemplateID, 3 FROM SourceTemplateTable 
WHERE FootNote || ShortFootnote || Bibliography LIKE '%[CD3]%'
EXCEPT SELECT DISTINCT TemplateID, 3 FROM xCDmaxTable
;

INSERT INTO xCDmaxTable
SELECT TemplateID, 2 FROM SourceTemplateTable 
WHERE FootNote || ShortFootnote || Bibliography LIKE '%[CD2]%'
EXCEPT SELECT DISTINCT TemplateID, 2 FROM xCDmaxTable
;

INSERT INTO xCDmaxTable
SELECT TemplateID, 1 FROM SourceTemplateTable 
WHERE FootNote || ShortFootnote || Bibliography LIKE '%[CD1]%'
EXCEPT SELECT DISTINCT TemplateID, 1 FROM xCDmaxTable
;

----- What is the max # of CD parts in the Citation Details value?

DROP TABLE IF EXISTS xCDpartsTable
;

CREATE TEMP TABLE xCDpartsTable (TemplateID INTEGER, Parts INTEGER)
;

INSERT INTO xCDpartsTable
SELECT TemplateID, 9 FROM xCitFieldsTable
WHERE CD9 NOT LIKE ''
;

INSERT INTO xCDpartsTable
SELECT TemplateID, 8 FROM xCitFieldsTable
WHERE CD8 NOT LIKE ''
;

INSERT INTO xCDpartsTable
SELECT TemplateID, 7 FROM xCitFieldsTable
WHERE CD7 NOT LIKE ''
;

INSERT INTO xCDpartsTable
SELECT TemplateID, 6 FROM xCitFieldsTable
WHERE CD6 NOT LIKE ''
;

INSERT INTO xCDpartsTable
SELECT TemplateID, 5 FROM xCitFieldsTable
WHERE CD5 NOT LIKE ''
;

INSERT INTO xCDpartsTable
SELECT TemplateID, 4 FROM xCitFieldsTable
WHERE CD4 NOT LIKE ''
;

INSERT INTO xCDpartsTable
SELECT TemplateID, 3 FROM xCitFieldsTable
WHERE CD3 NOT LIKE ''
;

INSERT INTO xCDpartsTable
SELECT TemplateID, 2 FROM xCitFieldsTable
WHERE CD2 NOT LIKE ''
;

INSERT INTO xCDpartsTable
SELECT TemplateID, 1 FROM xCitFieldsTable
WHERE CD1 NOT LIKE ''
;

DROP VIEW IF EXISTS xCDmaxparts
;
CREATE TEMP VIEW xCDmaxparts
AS
SELECT TemplateID, MAX(Parts) AS MaxParts FROM xCDpartsTable
GROUP BY TemplateID
;

-- revise xCDmaxTable to the higher of CDmax and MaxParts
UPDATE xCDmaxTable
SET CDmax =
  MAX(CDmax
      , IFNULL((SELECT MaxParts FROM xCDmaxparts X WHERE xCDmaxTable.TemplateID = X.TemplateID),0)
      )
;


------- Now create a temp table of new plain text Citation Fields with CD# instead of CD

DROP TABLE IF EXISTS xCitFieldsNew
;
CREATE TEMP Table xCitFieldsNew
AS
SELECT CitationID, TemplateID, REPLACE(CitFieldsText, CD, '') AS CitFieldsText --Fields with content of CD deleted 
FROM xCitFieldsTable
;
-- Replace the XML starting CD with XML containing CD#
UPDATE xCitFieldsNew
SET CitFieldsText =
  REPLACE
  (CitFieldsText
   , '<Field><Name>CD</Name><Value>'
   , (
      SELECT --CitationID, TemplateID,
        CASE CDmax
          WHEN 9
            THEN '<Field><Name>CD1</Name><Value>' || CD1 || '</Value></Field>
                  <Field><Name>CD2</Name><Value>' || CD2 || '</Value></Field>
                  <Field><Name>CD3</Name><Value>' || CD3 || '</Value></Field>
                  <Field><Name>CD4</Name><Value>' || CD4 || '</Value></Field>
                  <Field><Name>CD5</Name><Value>' || CD5 || '</Value></Field>
                  <Field><Name>CD6</Name><Value>' || CD6 || '</Value></Field>
                  <Field><Name>CD7</Name><Value>' || CD7 || '</Value></Field>
                  <Field><Name>CD8</Name><Value>' || CD8 || '</Value></Field>
                  <Field><Name>CD9</Name><Value>' || CD9
          WHEN 8
            THEN '<Field><Name>CD1</Name><Value>' || CD1 || '</Value></Field>
                  <Field><Name>CD2</Name><Value>' || CD2 || '</Value></Field>
                  <Field><Name>CD3</Name><Value>' || CD3 || '</Value></Field>
                  <Field><Name>CD4</Name><Value>' || CD4 || '</Value></Field>
                  <Field><Name>CD5</Name><Value>' || CD5 || '</Value></Field>
                  <Field><Name>CD6</Name><Value>' || CD6 || '</Value></Field>
                  <Field><Name>CD7</Name><Value>' || CD7 || '</Value></Field>
                  <Field><Name>CD8</Name><Value>' || CD8
          WHEN 7
            THEN '<Field><Name>CD1</Name><Value>' || CD1 || '</Value></Field>
                  <Field><Name>CD2</Name><Value>' || CD2 || '</Value></Field>
                  <Field><Name>CD3</Name><Value>' || CD3 || '</Value></Field>
                  <Field><Name>CD4</Name><Value>' || CD4 || '</Value></Field>
                  <Field><Name>CD5</Name><Value>' || CD5 || '</Value></Field>
                  <Field><Name>CD6</Name><Value>' || CD6 || '</Value></Field>
                  <Field><Name>CD7</Name><Value>' || CD7
          WHEN 6
            THEN '<Field><Name>CD1</Name><Value>' || CD1 || '</Value></Field>
                  <Field><Name>CD2</Name><Value>' || CD2 || '</Value></Field>
                  <Field><Name>CD3</Name><Value>' || CD3 || '</Value></Field>
                  <Field><Name>CD4</Name><Value>' || CD4 || '</Value></Field>
                  <Field><Name>CD5</Name><Value>' || CD5 || '</Value></Field>
                  <Field><Name>CD6</Name><Value>' || CD6
          WHEN 5
            THEN '<Field><Name>CD1</Name><Value>' || CD1 || '</Value></Field>
                  <Field><Name>CD2</Name><Value>' || CD2 || '</Value></Field>
                  <Field><Name>CD3</Name><Value>' || CD3 || '</Value></Field>
                  <Field><Name>CD4</Name><Value>' || CD4 || '</Value></Field>
                  <Field><Name>CD5</Name><Value>' || CD5
          WHEN 4
            THEN '<Field><Name>CD1</Name><Value>' || CD1 || '</Value></Field>
                  <Field><Name>CD2</Name><Value>' || CD2 || '</Value></Field>
                  <Field><Name>CD3</Name><Value>' || CD3 || '</Value></Field>
                  <Field><Name>CD4</Name><Value>' || CD4
          WHEN 3
            THEN '<Field><Name>CD1</Name><Value>' || CD1 || '</Value></Field>
                  <Field><Name>CD2</Name><Value>' || CD2 || '</Value></Field>
                  <Field><Name>CD3</Name><Value>' || CD3
          WHEN 2
            THEN '<Field><Name>CD1</Name><Value>' || CD1 || '</Value></Field>
                  <Field><Name>CD2</Name><Value>' || CD2
          WHEN 1
            THEN '<Field><Name>CD1</Name><Value>' || CD1
        END --CASE
      FROM xCitFieldsTable INNER JOIN xCDmaxTable USING(TemplateID)
      WHERE xCitFieldsNew.CitationID = xCitFieldsTable.CitationID
      )
  )
;

            

-- Replace the Citation Fields with the BLOB of the new Citation Fields
UPDATE CitationTable
SET Fields =
CAST(
 (
  SELECT CitFieldsText 
  FROM xCitFieldsNew
  WHERE CitationTable.CitationID = xCitFieldsNew.CitationID
 ) AS BLOB
) -- end Cast
WHERE CitationID IN (SELECT CitationID FROM xCitFieldsNew)
;

/* 
Revise the SourceTemplateTable.FieldDefs for the templates used
Create a temporary View of the Field Defs in plain text
*/
DROP VIEW IF EXISTS xSrcTpltFieldDefsTxt
;

CREATE TEMP VIEW xSrcTpltFieldDefsTxt
AS
SELECT TemplateID, CAST(FieldDefs AS TEXT) AS FieldDefsText
FROM SourceTemplateTable 
WHERE TemplateID
IN (SELECT DISTINCT TemplateID FROM xCitFieldsTable)
;

DROP TABLE IF EXISTS xSrcTpltFieldDefsNew
;

CREATE TEMP TABLE xSrcTpltFieldDefsNew
AS
SELECT 
  TemplateID
  , REPLACE
     (FieldDefsText
      , '<Field><FieldName>CD</FieldName><DisplayName>Citation Detail</DisplayName><Type>Text</Type><Hint/><LongHint/><CitationField>True</CitationField></Field>'
      , ''
      ) AS FieldDefsText
FROM xSrcTpltFieldDefsTxt
;

------ Insert CD# field defs 

UPDATE xSrcTpltFieldDefsNew
SET FieldDefsText =
  REPLACE
  (FieldDefsText
   , '</Fields>'
   , (
      SELECT --CitationID, TemplateID,
        CASE CDmax
          WHEN 9
            THEN '<Field><FieldName>CD1</FieldName><DisplayName>[CD1]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD2</FieldName><DisplayName>[CD2]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD3</FieldName><DisplayName>[CD3]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD4</FieldName><DisplayName>[CD4]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD5</FieldName><DisplayName>[CD5]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD6</FieldName><DisplayName>[CD6]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD7</FieldName><DisplayName>[CD7]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD8</FieldName><DisplayName>[CD8]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD9</FieldName><DisplayName>[CD9]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '</Fields>'
          WHEN 8
            THEN '<Field><FieldName>CD1</FieldName><DisplayName>[CD1]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD2</FieldName><DisplayName>[CD2]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD3</FieldName><DisplayName>[CD3]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD4</FieldName><DisplayName>[CD4]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD5</FieldName><DisplayName>[CD5]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD6</FieldName><DisplayName>[CD6]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD7</FieldName><DisplayName>[CD7]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD8</FieldName><DisplayName>[CD8]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '</Fields>'
          WHEN 7
            THEN '<Field><FieldName>CD1</FieldName><DisplayName>[CD1]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD2</FieldName><DisplayName>[CD2]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD3</FieldName><DisplayName>[CD3]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD4</FieldName><DisplayName>[CD4]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD5</FieldName><DisplayName>[CD5]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD6</FieldName><DisplayName>[CD6]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD7</FieldName><DisplayName>[CD7]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '</Fields>'
          WHEN 6
            THEN '<Field><FieldName>CD1</FieldName><DisplayName>[CD1]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD2</FieldName><DisplayName>[CD2]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD3</FieldName><DisplayName>[CD3]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD4</FieldName><DisplayName>[CD4]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD5</FieldName><DisplayName>[CD5]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD6</FieldName><DisplayName>[CD6]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '</Fields>'
          WHEN 5
            THEN '<Field><FieldName>CD1</FieldName><DisplayName>[CD1]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD2</FieldName><DisplayName>[CD2]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD3</FieldName><DisplayName>[CD3]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD4</FieldName><DisplayName>[CD4]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD5</FieldName><DisplayName>[CD5]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '</Fields>'
          WHEN 4
            THEN '<Field><FieldName>CD1</FieldName><DisplayName>[CD1]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD2</FieldName><DisplayName>[CD2]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD3</FieldName><DisplayName>[CD3]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD4</FieldName><DisplayName>[CD4]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '</Fields>'
          WHEN 3
            THEN '<Field><FieldName>CD1</FieldName><DisplayName>[CD1]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD2</FieldName><DisplayName>[CD2]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD3</FieldName><DisplayName>[CD3]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '</Fields>'
          WHEN 2
            THEN '<Field><FieldName>CD1</FieldName><DisplayName>[CD1]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '<Field><FieldName>CD2</FieldName><DisplayName>[CD2]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '</Fields>'
          WHEN 1
            THEN '<Field><FieldName>CD1</FieldName><DisplayName>[CD1]</DisplayName><Type>Text</Type><Hint>ex-TMG split Citation Detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
            ||   '</Fields>'
        END --CASE
      FROM xSrcTpltFieldDefsNew X INNER JOIN xCDmaxTable USING(TemplateID)
      WHERE xSrcTpltFieldDefsNew.TemplateID = X.TemplateID
      )
  )
;

------ Replace the FieldDefs in SourceTemplateTable with BLOB of the new FieldDefsText

UPDATE SourceTemplateTable
SET FieldDefs =
CAST
 (
  (
   SELECT
     FieldDefsText
   FROM xSrcTpltFieldDefsNew AS x
   WHERE SourceTemplateTable.TemplateID = x.TemplateID
   )
   AS BLOB
  ) -- end Cast
WHERE TemplateID IN (SELECT DISTINCT TemplateID FROM xSrcTpltFieldDefsNew)
;