We help IT Professionals succeed at work.

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

Paula DiTallo
Paula DiTallo asked
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);

    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
  @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
      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
  CLOSE DistribEndpoints
  DEALLOCATE DistribEndpoints
-- free up the #temp hash space
drop table #temp

Open in new window

Watch Question

Top Expert 2012
That would be because you are building your #temp table with Xml columns.  I would never consider using a cursor this way, bt if you insist change your query to:

SELECT  T1.c1.value('server_name[1]', 'varchar(200)') AS [server_name],
             T1.c1.value('sql_version[1]', 'tinyint') AS [sql_version],
             T1.c1.value('schem_name[1]', 'varchar(200)') AS [schem_name],
             T1.c1.value('db[1]', 'varchar(200)') AS [db]
INTO     #temp
FROM    @x.nodes('/endpoints/endpoint') AS T1 (c1) ;