Need to update xml in sql query

Not sure why the modify is not updating the xml in temp catalog3 table

any help much appreciated

WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource



SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.ConnString
	  INTO #CATALOG1
FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString
     FROM SDS
          CROSS APPLY 
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY CON.[Path]
        ,CON.SharedDsName;



WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
        ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
 AS rd)
,SDS AS
(SELECT SDS.name AS SharedDsName
       ,SDS.[Path]
       ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
 FROM dbo.[Catalog] AS SDS
 WHERE SDS.Type = 5)     -- 5 = Shared Datasource
SELECT [SharedDsName], [Path], [DEF]
  INTO #CATALOG2
  FROM SDS;

		
		SELECT C1.Path, C1.SharedDsName, C1.ConnString, C2.DEF 
		INTO #CATALOG3
		FROM #CATALOG1 C1
		INNER JOIN #CATALOG2 C2 ON C1.Path = C2.Path
		WHERE C1.ConnSTring LIKE '%STA-SQL64%'
		

		

		UPDATE #CATALOG3
		    SET DEF.modify ('replace value of (/DataSourceDefinition/ConnectString/text())[0] with "Test"');

		SELECT * FROM #CATALOG3
	
		DROP TABLE #CATALOG1
		DROP TABLE #CATALOG2
		DROP TABLE #CATALOG3

Open in new window

LVL 4
Mauro CazabonnetSenior Software EngineerAsked:
Who is Participating?
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.

David Johnson, CD, MVPOwnerCommented:
shouldn't the replace go before the update?
0
Mauro CazabonnetSenior Software EngineerAuthor Commented:
Needed to declare namespace
SET DEF.modify ('
            declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource";
            replace value of (/DataSourceDefinition/ConnectString[1]/text())[1] with sql:variable("@newconnstring")')
0

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
Mauro CazabonnetSenior Software EngineerAuthor Commented:
Closing
0
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
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.