updating an XML field - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

updating an XML field

Author

Topic

lleoun
Starting Member

2 Posts

Posted - 2011-11-11 : 07:08:50

Hi all,

I have a table containing a very long xml field. I need to update that xml adding a readonly in an xsl input like that:

From

<input type="text" id="doc" name="doc" maxlength="15">

To

<input type="text" id="doc" name="doc" maxlength="15" readonly="readonly">

How can I do that?

Thanks a million

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 07:16:20

use modify() method



UPDATE table
SET xmlcol.modify('insert attribute readonly{"readonly"} into (//input)[1]')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lleoun
Starting Member

2 Posts

Posted - 2011-11-11 : 07:19:59

amazing! thanks a million!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-11 : 07:53:37

Also see http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx
and http://weblogs.sqlteam.com/peterl/archive/2009/08/04/Manipulate-XML-data-continued.aspx

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-11 : 07:55:04

quote:

Originally posted by visakh16

UPDATE table
SET xmlcol.modify('insert attribute readonly{"readonly"} into (//input)[1])')

Please notice that INSERT will create a duplicate attribute if already present.

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 08:53:05

quote:
Originally posted by SwePeso

quote:
Originally posted by visakh16

UPDATE table
SET xmlcol.modify('insert attribute readonly{"readonly"} into (//input)[1])')
Please notice that INSERT will create a duplicate attribute if already present.

N 56°04'39.26"
E 12°55'05.63"

I know that

In the sample XML OP posted there was no readonly attribute already

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 09:05:55

quote:
Originally posted by SwePeso

quote:
Originally posted by visakh16

UPDATE table
SET xmlcol.modify('insert attribute readonly{"readonly"} into (//input)[1])')
Please notice that INSERT will create a duplicate attribute if already present.

N 56°04'39.26"
E 12°55'05.63"

If at all there's already an attribute readonly, then following should handle it


UPDATE table
SET xmlcol.modify('insert 
if (count(//input[@readonly]) =0)
then attribute readonly{"readonly"} 
else ()
as last
into (//input)[1]')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources