Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update XML column

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 SecData

The name of the column is xml_description

An 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 illustration

declare @t table
(
x xml
)
insert @t
values ('<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 @t
SET x.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]')

select * from @t

output
--------------------------------
<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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 column

SET 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.
Go to Top of Page

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 column

SET 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 xml
CAST(columnname as xml)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 example

declare @xml_description xml

set @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.
Go to Top of Page

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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 23:07:47
it will work

declare @xml_description xml

set @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 truncated
Increase length as above and try

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-01-12 : 15:59:34
Many thanks yaar for your help.

It's much appreciated.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-13 : 05:56:54
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 xml
Declare @username varchar (100)
Declare @secdata_id uniqueidentifier
Set @username = 'BUILTIN\ADMINSTRATORS'



DECLARE db_cursor CURSOR FOR
SELECT xmldescription, secdataid
FROM 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-15 : 04:50:32
why do you need cusror?
see this illustration

declare @x table
(
x xml
)
insert @x
values('<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 nodes
update @x
set x.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]')

select * from @x


output
---------------------
<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 this


Declare @username varchar (100)
Set @username = 'BUILTIN\ADMINSTRATORS'


UPDATE secdata
SET xmldescription.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]')

SELECT xmldescription, secdataid
FROM secdata



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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")]')
Go to Top of Page

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.
Go to Top of Page

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.


cool
even other wise you can cast it inline to XML and do the modify

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.


cool
even other wise you can cast it inline to XML and do the modify

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Can you show me how you would do this?

Thanks
Go to Top of Page

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 , secdataid
INTO #Temp
FROM secdata

UPDATE #temp
SET xmldescription.modify('delete /Policies/Policy[GroupUserName =sql:variable("@username")]')

UPDATE t
SET xmldescription = tmp.xmldescription
FROM secdata t
INNER JOIN (SELECT CAST(xmldescription AS nvarchar(max)) AS xmldescription , secdataid
FROM #Temp) tmp
ON tmp.secdataid = t.secdataid

SELECT xmldescription, secdataid
FROM secdata

DROP TABLE #Temp
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-01-15 : 09:29:48
Thanks again Visakh, this is much quicker than the cursor!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-16 : 04:51:42
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -