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

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

Paula DiTalloIntegration developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
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) ;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.