SQL Server Performance Forum – Threads Archive
updating XML column value
I have a column reportdefn defined as xml in a table AdhocPivotTableReport<br />Values in the table is as shown below:<br /><br /><xml xmlns<img src=’/community/emoticons/emotion-12.gif’ alt=’:x’ />="urn<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />chemas-microsoft-com<img src=’/community/emoticons/emotion-3.gif’ alt=’
‘ />ffice:excel"><br /> <x<img src=’/community/emoticons/emotion-4.gif’ alt=’
‘ />ivotTable><br /> <x<img src=’/community/emoticons/emotion-3.gif’ alt=’:O’ />WCVersion>11.0.0.8001 </x<img src=’/community/emoticons/emotion-3.gif’ alt=’:O’ />WCVersion><br /> <x<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />isplayScreenTips /><br /> <x:MaxHeight>523</x:MaxHeight><br /> <x:MaxWidth>954</x:MaxWidth><br /> <x:CubeProvider>msolap.2</x:CubeProvider><br /> <x:CacheDetails /><br /> <x:ConnectionString><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />rovider=MSOLAP.3;Cache Authentication=False;Initial Catalog=Adhoc Analysis;Data Source=SROCRPTP001;Impersonation Level=Impersonate;Mode=ReadWrite;Auto Synch Period=20000;Default Isolation Mode=0;Default MDX Visual Mode=0;MDX Compatibility=0;MDX Unique Name Style=0;Non Empty Threshold=0;SQLQueryMode=Calculated;Safety Options=1;Secured Cell Value=0;SQL Compatibility=0;Compression Level=0;Real Time Olap=False<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />acket Size=4096</x:ConnectionString><br /> <x<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />ataMember>Exposure Reporting</x<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />ataMember><br /> <x:Name>Microsoft Office PivotTable 11.0</x:Name><br /> </x<img src=’/community/emoticons/emotion-4.gif’ alt=’‘ />ivotTable><br /></xml><br /><br /><br />I need to update the connectionstring , data source values which has SROCRPTP001 to SROCRPTP003.<br /><br />How could i do it.<br /><br />I tried using <br /><br />update AdhocPivotTableReport<br />set reportdefinition.modify(‘<br /> declare namespace MI="urn<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />chemas-microsoft-com<img src=’/community/emoticons/emotion-3.gif’ alt=’‘ />ffice:excel";<br /> replace value of (/MI<img src=’/community/emoticons/emotion-4.gif’ alt=’‘ />ivotTable/MI:ConnectionString)["data source"]<br /> with "SROCRPTP001"<br />’)<br />where reportid=7<br /><br />but got error msg <br /><br />Msg 2203, Level 16, State 1, Line 4<br />XQuery [AdhocPivotTableReport.ReportDefinition.modify()]: Only single (optional) numeric/boolean or node* expressions allowed as predicates, found ‘xs<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tring'<br /><br />Thanks<br />emamuthu<br />
]]>