SQL to Extract Cadis Database Parameters from the XML Config

declare @xmlParams xml

declare @name varchar (100)
set @name = '%%'
IF  OBJECT_ID(N'[tempdb]..[#temp]') is not null
drop table #temp

CREATE TABLE #temp
(Name nvarchar(200)
,DefinitionXml xml
,CRC nchar(1000)
,details image
 )
 insert #temp exec CADIS_SYS.SPCO_GLOBALSELBYNAME @Name=N'Database Parameters'
select @xmlParams = DefinitionXml from #temp

-- select * from cadis_sys.co_global

-- select @xmlParams

select T.col.value('(Name)[1]','nvarchar(200)') as name
,T.col.value('Value[1]','nvarchar(2000)') as Value
-- ,T.col.query('.')
from @xmlParams.nodes('/CadisXml/Content/Globals/nv/list/Item') T(col)
where T.col.value('(Name)[1]','nvarchar(200)')  like @name
or T.col.value('Value[1]','nvarchar(2000)') like @name
order by 1

select T.col.value('(Name)[1]','nvarchar(200)') as name
,T.col.value('Path[1]','nvarchar(2000)') as NamePath
,T.col.value('UNCPath[1]','nvarchar(2000)') as UncPath
-- ,T.col.query('.')
from @xmlParams.nodes('/CadisXml/Content/Locs/list/Item') T(col)
where T.col.value('(Name)[1]','nvarchar(200)') like @name
or T.col.value('Path[1]','nvarchar(2000)') like @name

select T.col.value('(Name)[1]','nvarchar(2000)') as ConnectionName
,T.col.value('(Info/Description)[1]','nvarchar(2000)') as Description
,T.col.value('(Info/DataSource)[1]','nvarchar(2000)') as DataSource
,T.col.value('(Info/Type)[1]','nvarchar(2000)') as ConnectionType
-- ,T.col.query('.')
from @xmlParams.nodes('/CadisXml/Content/Conns/list/Item') T(col)
where T.col.value('(Name)[1]','nvarchar(2000)')  like @name
or T.col.value('(Info/Description)[1]','nvarchar(2000)') like @name



drop table #temp