Link to home
Start Free TrialLog in
Avatar of Mauro Cazabonnet
Mauro CazabonnetFlag for United States of America

asked on

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

SOLUTION
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mauro Cazabonnet

ASKER

Closing