SQL Server Performance

updating XML column value

Discussion in 'SQL Server 2005 General Developer Questions' started by emamuthu, Mar 1, 2007.

  1. emamuthu New Member

    I have a column reportdefn defined as xml in a table AdhocPivotTableReport<br />Values in the table is as shown below:<br /><br />&lt;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=':eek:' />ffice:excel"&gt;<br /> &lt;x<img src='/community/emoticons/emotion-4.gif' alt=':p' />ivotTable&gt;<br /> &lt;x<img src='/community/emoticons/emotion-3.gif' alt=':O' />WCVersion&gt;11.0.0.8001 &lt;/x<img src='/community/emoticons/emotion-3.gif' alt=':O' />WCVersion&gt;<br /> &lt;x<img src='/community/emoticons/emotion-2.gif' alt=':D' />isplayScreenTips /&gt;<br /> &lt;x:MaxHeight&gt;523&lt;/x:MaxHeight&gt;<br /> &lt;x:MaxWidth&gt;954&lt;/x:MaxWidth&gt;<br /> &lt;x:CubeProvider&gt;msolap.2&lt;/x:CubeProvider&gt;<br /> &lt;x:CacheDetails /&gt;<br /> &lt;x:ConnectionString&gt<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&lt;/x:ConnectionString&gt;<br /> &lt;x<img src='/community/emoticons/emotion-2.gif' alt=':D' />ataMember&gt;Exposure Reporting&lt;/x<img src='/community/emoticons/emotion-2.gif' alt=':D' />ataMember&gt;<br /> &lt;x:Name&gt;Microsoft Office PivotTable 11.0&lt;/x:Name&gt;<br /> &lt;/x<img src='/community/emoticons/emotion-4.gif' alt=':p' />ivotTable&gt;<br />&lt;/xml&gt;<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=':eek:' />ffice:excel";<br /> replace value of (/MI<img src='/community/emoticons/emotion-4.gif' alt=':p' />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 />

Share This Page