|
Home > Archive > MS SQL XML > December 2006 > Modifying xml nodelists in stored procedure
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Modifying xml nodelists in stored procedure
|
|
|
| Hello,
I have an xml doc with some nodelists. From amoong these nodelists I want to
chage value of an attribute of a particular node based on value of another
attribute. Is there a way to do this?
I have tried xmldocument.modify to modify values of xml node which does not
repeat but unable to do the same for repeating nodes.
I am trying to do all this in a stored proc and then return the modified xml.
Thanks.
| |
|
| Hi Nitin
Have you considered a temporary table? For example,
declare @x xml
set @x='<a><b>1</b><b>2</b></a>'
--the following should fail
--set @x.modify('replace value of (/a/b/text())[1] with 3')
--instead,
create table #tmp (x xml)
insert #tmp select a.b.query('.') from @x.nodes('/a/b') a(b)
update #tmp set x.modify('replace value of (/b/text())[1] with 3')
select x '*' from #tmp for xml path(''), root('a')
--result should be
<a><b>3</b><b>3</b></a>
"Nitin" <Nitin@discussions.microsoft.com> wrote in message
news:FD33A208-5A4D-4EA2-9D5B- D8948B1A57F7@microso
ft.com...
> Hello,
> I have an xml doc with some nodelists. From amoong these nodelists I want
> to
> chage value of an attribute of a particular node based on value of another
> attribute. Is there a way to do this?
> I have tried xmldocument.modify to modify values of xml node which does
> not
> repeat but unable to do the same for repeating nodes.
> I am trying to do all this in a stored proc and then return the modified
> xml.
> Thanks.
| |
|
| Han,
Thanks for the response buddy. For performance reasons I don't want to use a
temp table. Besides, these nodelists are goin to have some attributes and I
want to change the value of one of these attributes based on matching value
of the sibling attribute for a particular node. Is there a way to do
something like loop through the nodes examining each attribute and the one
that matches can be selected and its sibling attributes value can be changed.
I hope I have not made it too confusing for you.
"Han" wrote:
> Hi Nitin
>
> Have you considered a temporary table? For example,
>
> declare @x xml
> set @x='<a><b>1</b><b>2</b></a>'
>
> --the following should fail
> --set @x.modify('replace value of (/a/b/text())[1] with 3')
>
> --instead,
>
> create table #tmp (x xml)
> insert #tmp select a.b.query('.') from @x.nodes('/a/b') a(b)
>
> update #tmp set x.modify('replace value of (/b/text())[1] with 3')
> select x '*' from #tmp for xml path(''), root('a')
>
> --result should be
> <a><b>3</b><b>3</b></a>
>
> "Nitin" <Nitin@discussions.microsoft.com> wrote in message
> news:FD33A208-5A4D-4EA2-9D5B- D8948B1A57F7@microso
ft.com...
>
>
>
| |
|
| Thank you for your reply Nitin.
I see what you mean. Yes, that's an idea to do your job. You can loop
through XML nodes,
set @count=x.value('count(/*/*)', 'int')
while (@count > 0)
begin
do ...
@c=@c-1
However that should be the last resort. If performance is important issue
for you, you may need to consider some other ways like XSLT.
"Nitin" <Nitin@discussions.microsoft.com> wrote in message
news:6F6E57E0-1E0B-498E-A0A5- 9CE4D005E7CB@microso
ft.com...[color=darkred]
> Han,
> Thanks for the response buddy. For performance reasons I don't want to use
> a
> temp table. Besides, these nodelists are goin to have some attributes and
> I
> want to change the value of one of these attributes based on matching
> value
> of the sibling attribute for a particular node. Is there a way to do
> something like loop through the nodes examining each attribute and the one
> that matches can be selected and its sibling attributes value can be
> changed.
> I hope I have not made it too confusing for you.
>
> "Han" wrote:
>
|
|
|
|
|