RiO dropdown field bindings

This query shreds RiO forms' XML to:

  • extract form fields; and to
  • identify lookup tables used to populate associated dropdown lists.
;WITH cte AS (
  SELECT
    'UserAssess' + code AS FormTable
  , CAST(
      REPLACE(
        CAST([ScreenXML] AS NVARCHAR(MAX))
      , '<?xml version="1.0" encoding="UTF-8"?>'
      , '<?xml version="1.0" encoding="UTF-16"?>'
      ) AS XML
    ) AS FormDefinition
  , ROW_NUMBER() OVER (
      PARTITION BY code
      ORDER BY CreatedOn DESC
    ) AS VersionNumber
  FROM [dbo].[GenScreenXML]
)
SELECT
  cte.FormTable
, t.c.value('@NAME', 'NVARCHAR(128)')
, t1.c1.value('@TABLE', 'NVARCHAR(128)')
FROM cte
  CROSS APPLY cte.FormDefinition.nodes('//SCREEN/DEFINITION/FIELD') AS t(c)
  CROSS APPLY t.c.nodes('SELECT') AS t1(c1)
WHERE VersionNumber = 1

The REPLACE call in the CTE has occasionally been necessary when a form's content doesn't abide by its declared encoding.