Author |
Topic |
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2014-01-02 : 14:16:22
|
Hi all,I am trying to remove a user from Reporting Services via code. I have found that the way to do this is to remove their details from an XML field in the table SecDataThe name of the column is xml_descriptionAn example value of the column is below: <Policy> <GroupUserName>BUILTIN\Administrators</GroupUserName> <GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId> <Roles> <Role> <Name>Content Manager</Name> </Role> </Roles> </Policy> <Policy> <GroupUserName>EMEA\MJohnson</GroupUserName> <GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPFCQBAA==</GroupUserId> <Roles> <Role> <Name>Browser</Name> </Role> </Roles> </Policy> <Policy> <GroupUserName>EMEA\TParker</GroupUserName> <GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPmCUBAA==</GroupUserId> <Roles> <Role> <Name>Browser</Name> </Role> </Roles> </Policy></Policies>I would like to remove the bolded part in the column when I put in the user's name.So the code would start with:Declare @username varchar (20)Set @ username = 'EMEA\MJohnson' followed by the logic to remove the bolded part of the column.The column is called xml_description and the name of the table is SecData. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-03 : 01:11:26
|
see this illustrationdeclare @t table(x xml)insert @tvalues ('<Policies><Policy><GroupUserName>BUILTIN\Administrators</GroupUserName><GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId><Roles><Role><Name>Content Manager</Name></Role></Roles></Policy><Policy><GroupUserName>EMEA\MJohnson</GroupUserName><GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPFCQBAA==</GroupUserId><Roles><Role><Name>Browser</Name></Role></Roles></Policy><Policy><GroupUserName>EMEA\TParker</GroupUserName><GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPmCUBAA==</GroupUserId><Roles><Role><Name>Browser</Name></Role></Roles></Policy></Policies>')Declare @username varchar (20)Set @username = 'EMEA\MJohnson'UPDATE @tSET x.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]')select * from @toutput--------------------------------<Policies> <Policy> <GroupUserName>BUILTIN\Administrators</GroupUserName> <GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId> <Roles> <Role> <Name>Content Manager</Name> </Role> </Roles> </Policy> <Policy> <GroupUserName>EMEA\TParker</GroupUserName> <GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPmCUBAA==</GroupUserId> <Roles> <Role> <Name>Browser</Name> </Role> </Roles> </Policy></Policies> ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2014-01-09 : 11:45:54
|
Many thanks for your help Visakh, your suggestion worked.However I'm faced with one more problem. The column xml_description is an ntext column and as you are aware you cannot run the following command against an ntext columnSET x.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]') How can I convert the data to xml and then run the modify statement.The name of the table is secdata. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-10 : 05:07:42
|
quote: Originally posted by ranvir_2k Many thanks for your help Visakh, your suggestion worked.However I'm faced with one more problem. The column xml_description is an ntext column and as you are aware you cannot run the following command against an ntext columnSET x.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]') How can I convert the data to xml and then run the modify statement.The name of the table is secdata.
use cast to make it xmlCAST(columnname as xml)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2014-01-10 : 11:28:56
|
Is there a way I can modify the xml in a variable.So for exampledeclare @xml_description xmlset @xml_description = '<Policies><Policy><GroupUserName>BUILTIN\Administrators</GroupUserName><GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId><Roles><Role><Name>Content Manager</Name></Role></Roles></Policy><Policy><GroupUserName>EMEA\MJohnson</GroupUserName><GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPFCQBAA==</GroupUserId><Roles><Role><Name>Browser</Name></Role></Roles></Policy><Policy><GroupUserName>EMEA\TParker</GroupUserName><GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPmCUBAA==</GroupUserId><Roles><Role><Name>Browser</Name></Role></Roles></Policy></Policies>'Declare @username varchar (20)Set @username = 'EMEA\MJohnson' I have tried: set @xml_description.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]') select @xml_description but this does not work.select @xml_description returns the same result as before.Can you please advise.Thanks. |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2014-01-10 : 11:41:38
|
Actually it does work but not if I put @username = 'BUILTIN\Administrators'Is there a reason why it doesn't work for 'BUILTIN\Administrators' but does work for 'EMEA\Johnson' and 'EMEA\TParker'Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-10 : 23:07:47
|
it will workdeclare @xml_description xmlset @xml_description = '<Policies><Policy><GroupUserName>BUILTIN\Administrators</GroupUserName><GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId><Roles><Role><Name>Content Manager</Name></Role></Roles></Policy><Policy><GroupUserName>EMEA\MJohnson</GroupUserName><GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPFCQBAA==</GroupUserId><Roles><Role><Name>Browser</Name></Role></Roles></Policy><Policy><GroupUserName>EMEA\TParker</GroupUserName><GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPmCUBAA==</GroupUserId><Roles><Role><Name>Browser</Name></Role></Roles></Policy></Policies>'Declare @username varchar (50)Set @username = 'BUILTIN\Administrators'set @xml_description.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]') select @xml_description<Policies> <Policy> <GroupUserName>EMEA\MJohnson</GroupUserName> <GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPFCQBAA==</GroupUserId> <Roles> <Role> <Name>Browser</Name> </Role> </Roles> </Policy> <Policy> <GroupUserName>EMEA\TParker</GroupUserName> <GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPmCUBAA==</GroupUserId> <Roles> <Role> <Name>Browser</Name> </Role> </Roles> </Policy></Policies> the reason it didnt work was because your variable didnt have enough length to hold the entire string BUILTIN\Administrators so it got truncatedIncrease length as above and try------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2014-01-12 : 15:59:34
|
Many thanks yaar for your help.It's much appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-13 : 05:56:54
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2014-01-15 : 04:35:16
|
Sorry to bother you again Visakh, I'm trying to put this all into a cursor as I want to modify all rows in my table. It does not work this way.Can you explain to me what is wrong with my code:DECLARE @xml_description xmlDeclare @username varchar (100)Declare @secdata_id uniqueidentifierSet @username = 'BUILTIN\ADMINSTRATORS'DECLARE db_cursor CURSOR FOR SELECT xmldescription, secdataidFROM secdata OPEN db_cursor FETCH NEXT FROM db_cursor INTO @xml_description, @secdata_id WHILE @@FETCH_STATUS = 0 BEGIN set @xml_description.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]') select @xml_description as xmldescription, @secdata_id as secdataid--update secdata set xmldescription = cast (@xml_description as nvarchar(max)) where SecDataID = @secdata_id FETCH NEXT FROM db_cursor INTO @xml_description, @secdata_id END CLOSE db_cursor DEALLOCATE db_cursor My code is taking the xmldescription data which is ntext, converting it to xml and then doing the update in a cursor.Finally it is changing the secdata table to include the updated secdata value.However the xml update isn't working in the cursor. Can you tell me why this could be?The select @xml_description as xmldescription, @secdata_id as secdataid is not showing the updated value for @xml_description |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-15 : 04:50:32
|
why do you need cusror? see this illustrationdeclare @x table(x xml)insert @xvalues('<Policies><Policy><GroupUserName>BUILTIN\Administrators</GroupUserName><GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId><Roles><Role><Name>Content Manager</Name></Role></Roles></Policy><Policy><GroupUserName>EMEA\MJohnson</GroupUserName><GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPFCQBAA</GroupUserId><Roles><Role><Name>Browser</Name></Role></Roles></Policy></Policies>'),('<Policies><Policy><GroupUserName>BUILTIN\Administrators</GroupUserName><GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId><Roles><Role><Name>Content Manager</Name></Role></Roles></Policy><Policy><GroupUserName>EMEA\TParker</GroupUserName><GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPmCUBAA</GroupUserId><Roles><Role><Name>Browser</Name></Role></Roles></Policy></Policies>')Declare @username varchar (50)Set @username = 'BUILTIN\Administrators'--remove the nodesupdate @xset x.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]') select * from @xoutput---------------------<Policies> <Policy> <GroupUserName>BUILTIN\Administrators</GroupUserName> <GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId> <Roles> <Role> <Name>Content Manager</Name> </Role> </Roles> </Policy> <Policy> <GroupUserName>EMEA\MJohnson</GroupUserName> <GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPFCQBAA</GroupUserId> <Roles> <Role> <Name>Browser</Name> </Role> </Roles> </Policy></Policies><Policies> <Policy> <GroupUserName>BUILTIN\Administrators</GroupUserName> <GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId> <Roles> <Role> <Name>Content Manager</Name> </Role> </Roles> </Policy> <Policy> <GroupUserName>EMEA\TParker</GroupUserName> <GroupUserId>AQUAAAAAAAUVAAAA0Xt4Pm4e06wTxXiPmCUBAA</GroupUserId> <Roles> <Role> <Name>Browser</Name> </Role> </Roles> </Policy></Policies> so in your case you just need thisDeclare @username varchar (100)Set @username = 'BUILTIN\ADMINSTRATORS'UPDATE secdata SET xmldescription.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]') SELECT xmldescription, secdataidFROM secdata ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2014-01-15 : 06:41:36
|
The problem is that the xmldescription field in secdata is an ntext field. Also it is a system table so I don't want to change the column data type.So I need to convert it to xml to run the:SET xmldescription.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]') |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2014-01-15 : 06:57:29
|
Sorry my code does work fine, it was a typing mistake I made. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-15 : 07:33:15
|
quote: Originally posted by ranvir_2k Sorry my code does work fine, it was a typing mistake I made.
cooleven other wise you can cast it inline to XML and do the modify------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2014-01-15 : 08:13:18
|
quote: Originally posted by visakh16
quote: Originally posted by ranvir_2k Sorry my code does work fine, it was a typing mistake I made.
cooleven other wise you can cast it inline to XML and do the modify------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Can you show me how you would do this?Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-15 : 08:37:13
|
[code]Declare @username varchar (100)Set @username = 'BUILTIN\ADMINSTRATORS'SELECT CAST(xmldescription AS xml) AS xmldescription , secdataidINTO #TempFROM secdataUPDATE #temp SET xmldescription.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]')UPDATE tSET xmldescription = tmp.xmldescriptionFROM secdata tINNER JOIN (SELECT CAST(xmldescription AS nvarchar(max)) AS xmldescription , secdataidFROM #Temp) tmpON tmp.secdataid = t.secdataidSELECT xmldescription, secdataidFROM secdataDROP TABLE #Temp[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2014-01-15 : 09:29:48
|
Thanks again Visakh, this is much quicker than the cursor! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-16 : 04:51:42
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|