MS SQL XML - How do I insert text into empty xml element in sql server 2005?

Author

How do I insert text into empty xml element in sql server 2005?

firechaser@talk21.com

2005-09-28, 3:24 am

Hello

I have the following xml data stored in an xml datatype colmun in sql
server 2005:

<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>

I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.

....
<pagetext>
Some content goes here.
</pagetext>
....

I've tried to achieve this using the query below:

update tbl_tree SET theTree.modify(' replace value of
(//*& #91;@id=sql:variable
("@focusID")][1]/title/text())[1] with
sql:variable("@someContent")')

but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'

I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.

Is there a way to do this?

Any help very much appreciated.

Peter

Michael Rys [MSFT]

2005-10-02, 3:23 am

You would need an IF_DML statement that unfortunately did not make it into
SQL Server 2005. Please send an email to sqlwish at microsoft.com with your
use case and request.

Here is a workaround: You first update all those pagetext elements that have
no content with an insert and then do your normal replace value of.

declare @x xml;

set @x = N'<webpage id="935623B3-F72D-45EE-AF88-47022F101184">

<createdate>Sep 6 2005 11:04AM</createdate>

<title>Themes</title>

<icon>app/16/p/text_align_left</icon>

<pagetext />

</webpage>';

declare @focusID uniqueidentifier;

set @focusID = '935623B3-F72D-45EE-AF88-47022F101184';

declare @someContent nvarchar(50);

set @someContent= N'this is a test';

--I'd like to be able to update the the xml so that there is text within

--the pagetext element, i.e.

SET @x.modify('insert text {"x"}

into /webpage& #91;@id=sql:variable
("@focusID")][1]/pagetext[not(text())][1]');

select @x;

SET @x.modify('replace value of

(/webpage& #91;@id=sql:variable
("@focusID")][1]/pagetext/text())[1] with

sql:variable("@someContent")');

select @x

Best regards

Michael

<firechaser@talk21.com> wrote in message
news:1127893569.480367.18870@g14g2000cwa.googlegroups.com...
> Hello
>
> I have the following xml data stored in an xml datatype colmun in sql
> server 2005:
>
> <webpage id="935623B3-F72D-45EE-AF88-47022F101184">
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <icon>app/16/p/text_align_left</icon>
> <pagetext />
> </webpage>
>
> I'd like to be able to update the the xml so that there is text within
> the pagetext element, i.e.
>
> ...
> <pagetext>
> Some content goes here.
> </pagetext>
> ...
>
> I've tried to achieve this using the query below:
>
> update tbl_tree SET theTree.modify(' replace value of
> (//*& #91;@id=sql:variable
("@focusID")][1]/title/text())[1] with
> sql:variable("@someContent")')
>
> but it doesn't work. Is this because you can't 'modify' an empty
> element such as '<pagetext/>'
>
> I thought about inserting a node such as '<pagetext>Some content goes
> here.</pagetext>'- this would solve the problem only until such time as
> the element has its contents removed at which point it will become
> <pagetext/> again.
>
> Is there a way to do this?
>
> Any help very much appreciated.
>
> Peter
>

firechaser@talk21.com

2005-10-03, 3:23 am

Thanks Michael - I appreciate your help.

Sponsored Links