Dump Mappings from a Source Format

This query returns all mapped fields from a source format along with the destination and one, sample value. This is especially useful for comparing multiple Liaison CAS source formats.

You must replace the Source Format GUID with your own.

with remap
as (
	select distinct t.c.value('@src', 'nvarchar(max)') as src
		,t.c.value('@dst', 'nvarchar(max)') as dst1
		,t.c.value('@dst2', 'nvarchar(max)') as dst2
		,t.c.value('@dst3', 'nvarchar(max)') as dst3
	from [source.format]
	cross apply [remap].nodes('/transform/map') as t(c)
	)
select distinct sk.[key]
	,remap.[dst1]
	,remap.[dst2]
	,remap.[dst3]
	,max(sk.[sample_value]) as [sample_value] --Get any sample value
from [source.format] sf
inner join [source] s on sf.[id] = s.[format]
	and s.[created] >= sf.[remap_effective] --Ignore sources older than remap date
inner join [source.key] sk on sk.[source] = s.[id]
inner join remap on remap.[src] = sk.[key]
where 1 = 1
	and sf.[id] = '<guid>' --Source Format GUID
	and ( -- Only mapped source fields
		remap.[dst1] is not null
		or remap.[dst2] is not null
		or remap.[dst3] is not null
		)
group by sk.[key]
	,remap.[dst1]
	,remap.[dst2]
	,remap.[dst3]

This slower query returns all value mappings for a source format:

SELECT fmap.c.value('@src', 'nvarchar(max)') AS src_field
	,fmap.c.value('@dst', 'nvarchar(max)') AS dst_field
	,vmap.c.value('@src', 'nvarchar(max)') AS src_val
	,vmap.c.value('@dst', 'nvarchar(max)') AS dst_val
	,lp.[value] AS [Prompt Value]
FROM [source.format] sf
CROSS APPLY sf.[remap].nodes('/transform/map') AS fmap(c)
CROSS APPLY sf.[remap].nodes('/transform/map/val') AS vmap(c)
LEFT JOIN [lookup.prompt] lp
	ON lp.[id] = try_cast(vmap.c.value('@dst', 'nvarchar(max)') AS UNIQUEIDENTIFIER)
WHERE sf.[id] = '<guid>' --Source Format GUID