/*
recursive_cte_source_fields.sql   7/29/2024    Jerry Bryan

Uses recursive CTE to parse SourceTable.Fields, which the
master source fields names and values for all the variables
in the master source. As I am a source splitter, this is all
the field names for a citation. For non-source splitters, the
same code could be used for CitationTable.Fields to access the
source detail filed names and values. Then there could be a JOIN
to combine the master source fields with the source detail fields.

There are four major components to this query. 

#1. Most importantly, there is a recursive CTE component which extracts
    the data between tags <Field>...</Field> for each source field.
    This is the hard lifting, and it creates a row for each field name.

#2. There is a non-recursive CTE component that simply extracts the data
    from between <Name>...</Name> and <Value>...</Value> pairs to form two
    columns, one for the name of the field and the other for the value of the
    field. This code invokes the larger recursive CTE. It could
    surely have been embedded within the larger recursive CTE, but it was
    much easier and clearer to make separate CTE's for the two functions.
    
#3. There is a simple "main" query component that basiclly just invoikes
    component #2.
    
#4. There is a short component that converts rows into columns for display.
    Unfortunately, SQLite does not handle transposing rows and columns
    very gracefully at all. As a result, a new component #4 would need
    to be developed for each different source template that is in use.
    This particular component #4 is specific to my obituary template.
    
All the fields that are processed are master source fields because I am a source
splitter. For non-source splitters, components #1 and #2 would need to be
copied and rehomed to the CitationTable instead of to the SourceTable. Changing
the reference from SourceTable to CitationTable should be the only change that
is required. Then a JOIN would be required to combine the source fields with
the citation fields in a single query.

*/

--    Component #2
WITH S_Fields(S_ID, S_Name, TemplateID, Template, Field_Name, Field_Value) AS
(
SELECT A.S_ID, A.S_Name,
        CASE WHEN A.TemplateID IS NULL THEN 0 ELSE A.TemplateID END AS TemplateID,
        CASE WHEN A.T_Name IS NULL THEN 'Free Form' ELSE A.T_Name END AS Template,
REPLACE(SUBSTR(Field,1,INSTR(Field,'</Name>')-1),'<Name>','') AS Field_Name,
CASE WHEN INSTR( Field, '<Value/>' )>0 
    THEN
         NULL
    ELSE
         SUBSTR(    SUBSTR(Field,1,INSTR(Field,'</Value>')-1), INSTR(Field,'<Value>')+7)
  END AS Field_Value
FROM
(

-----------  recursive XML decoder begins here  ---------------------------------------

-- Component #1
WITH RECURSIVE Fields_CTE( element, remainder, SourceID) AS 
(

SELECT 
  NULL AS element,
  SUBSTR(r2, INSTR(r2,'<Field>') +7 ) AS remainder, SourceID 
  FROM
  (      
    SELECT SUBSTR(r1, 1, INSTR(r1,'</Fields>')-1) AS r2, SourceID 
    FROM        
    (
     SELECT CAST( S.Fields AS TEXT) AS r1, S.SourceID
     FROM SourceTable AS S
  )
)
                     
              UNION ALL
              
SELECT
  CASE WHEN INSTR( remainder, '</Field>' )>0 
    THEN
         SUBSTR( remainder, 1, INSTR( remainder, '</Field>' )-1 )
    ELSE
         remainder
  END AS element,

  CASE WHEN INSTR( remainder, '<Field>' )>0 
    THEN
        SUBSTR( remainder, INSTR( remainder, '<Field>' )+7 )
    ELSE
         NULL
  END AS remainder,
  SourceID AS SourceID

FROM Fields_CTE
WHERE remainder IS NOT NULL
              
)

---   main call to recursive decoder begins here.

-- Component #3
SELECT S.SourceID AS S_ID, 
       S.Name AS S_Name,
       ST.TemplateID,
       ST.Name AS T_Name, 
       Fields_CTE.SourceID, 
       element AS Field
FROM Fields_CTE 
JOIN SourceTable AS S ON S.SourceID = Fields_CTE.SourceID
LEFT JOIN SourceTemplateTable AS ST ON ST.TemplateID = S.TemplateID
WHERE element IS NOT NULL
ORDER BY ST.TemplateID, Fields_CTE.SourceID

-----------  main call to recursive decoder endshere  ----------------

) AS A
)

-----------  convert the rows for the field values into columns  ----------------
-----------  this converter is specific to my source tempate     ----------------
-----------  for obituaries. A different converter would be      ----------------
-----------  required for each different source template.        ----------------

-- I comment out and uncomment out various columns as a crude and manual
-- way to filter to columns that are of most current interest.

-- Component #4
SELECT --S_Name,
MAX(CASE WHEN Field_Name LIKE 'DeceasedName' THEN Field_Value END) AS 'DeceasedName',
MAX(CASE WHEN Field_Name LIKE 'FuneralHome' THEN Field_Value END) AS 'FuneralHome',
--MAX(CASE WHEN Field_Name LIKE 'Memorial' THEN Field_Value END) AS 'Memorial',
MAX(CASE WHEN Field_Name LIKE 'NewsPaper' THEN Field_Value END) AS 'Newspaper',
MAX(CASE WHEN Field_Name LIKE 'NewsPaperDate' THEN Field_Value END) AS 'Date',
MAX(CASE WHEN Field_Name LIKE 'NewsPaperPage' THEN Field_Value END) AS 'Page',
--MAX(CASE WHEN Field_Name LIKE 'NewsPaperPlace' THEN Field_Value END) AS 'Place',
--MAX(CASE WHEN Field_Name LIKE 'ObitMissing' THEN Field_Value END) AS 'ObitMissing',
MAX(CASE WHEN Field_Name LIKE 'Viewed' THEN Field_Value END) AS 'Viewed',
MAX(CASE WHEN Field_Name LIKE 'Website' THEN Field_Value END) AS 'WebSite'

FROM S_Fields
WHERE TemplateID = 10000   -- template ID for my obituary template. needs to be
GROUP BY S_ID              -- set to template currently of interest. the fields
                           -- names in this component of the query need to be adjusted
                           -- to match the template that is in use.