Mauro Cazabonnet
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER