CURSOR not working. Need to Extract node elements from XML select

Techies--
2 things, the first : I am trying to iterate through an xml result set collection in order to eventually execute other sql statements on a row by row basis. I need to compare the values I have back from the xml config file with the values I'll need to perform the operations against.  When I try to do this in the code snippet, I get the error:

Msg 16922, Level 16, State 1, Line 75
Cursor Fetch: Implicit conversion from data type xml to varchar is not allowed.

The second thing--I recognize cursor style t-sql is a dated way of doing things. I am open to modernizing my approach!:-)

 

-- this is what is contained in the xml file called config.xml
-- <endpoints>
--   <endpoint>
--       <server_name>paulad</server_name>
--       <sql_version>10</sql_version>
--       <schem_name>[SMA.OLAP]</schem_name>
--       <db>SiteManagement_staging</db>
--   </endpoint>
--   <endpoint>
--          <server_name>paulad</server_name>
--          <sql_version>10</sql_version>
--         <schem_name>[SMA.OLAP.DEF]</schem_name>
--          <db>SiteManagement_staging2</db>
--    </endpoint>
-- </endpoints>


DECLARE @endpoint varchar(400);

DECLARE @server_name varchar(100)
DECLARE @x xml;
SET @x = (SELECT * FROM OPENROWSET(BULK 'C:\Users\paulad\Desktop\DEF Reports\config\config.xml', SINGLE_BLOB) AS x);

SELECT
    T1.c1.query('server_name/text()') AS [server_name],
    T1.c1.query('sql_version/text()') AS [sql_version],
    T1.c1.query('schem_name/text()') AS [schem_name],
    T1.c1.query('db/text()') AS [db] 
  INTO #temp
FROM
  @x.nodes('//endpoints/endpoint') AS T1(c1);

declare @i int 
declare @db_cur varchar(200)
declare @server_name_cur varchar(200)
declare @schem_name_cur varchar(200)

declare DistrEndpoints cursor 
  for select server_name,schem_name,db from #temp     

OPEN DistrEndpoints
  FETCH NEXT FROM DistrEndpoints
    INTO @server_name_cur,@schem_name_cur,@db_cur
  set @i = 0
  WHILE @@FETCH_STATUS=0
    BEGIN
      set @i = @i + 1
      -- concatenate the values from the config file
      set @endpoint = @server_name_cur + '.' + @schem_name_cur + '.' + 'Site'
      -- TODO: add merge statement here!
      print @endpoint
      FETCH NEXT FROM DistrEndpoints
        INTO @server_name_cur,@schem_name_cur,@db_cur
    END
  CLOSE DistribEndpoints
  DEALLOCATE DistribEndpoints
  
-- free up the #temp hash space
drop table #temp

Select allOpen in new window