====== 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.

<code tsql>;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
</code>
The REPLACE call in the CTE has occasionally been necessary when a form's content doesn't abide by its declared encoding.

{{tag> tsql admin development xml}}