xml query help.. - 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 2005 Forums

Transact-SQL (2005)

xml query help..

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-10-06 : 03:08:34

I have xml string like below and i want to add new node in below xml..
I want to add new node in such a way that attribute of that node should set to true current = true and in last node value in previous xml 's attribute should remove..


new node
<advisement current="true">
    <text>This is what was entered</text>
    <level>1</level>
    <user id="1">test 3</user>
    <changed date="2010-09-22 10:40:30" />
</advisement>



declare @xmlstr xml
set @xmlstr = '
<advisements>
  <advisement current="true">
    <text>This is what was entered</text>
    <level>1</level>
    <user id="1">test 1</user>
    <changed date="2010-09-22 10:40:30" />
  </advisement>
  <advisement>
    <text>Previously entered</text>
    <level>2</level>
    <user id="2">test 2</user>
    <changed date="2010-08-20 09:20:00" />
  </advisement>
</advisements>'

OUTPUT AFTER ADDING NEW NODE


<advisements>
  <advisement current="true">
    <text>This is what was entered</text>
    <level>1</level>
    <user id="1">test 3</user>
    <changed date="2010-09-22 10:40:30" />
  </advisement>
  <advisement>
    <text>This is what was entered</text>
    <level>1</level>
    <user id="1">test 1</user>
    <changed date="2010-09-22 10:40:30" />
  </advisement>
  <advisement>
    <text>Previously entered</text>
    <level>2</level>
    <user id="2">test 2</user>
    <changed date="2010-08-20 09:20:00" />
  </advisement>
</advisements>

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-10-06 : 14:12:21

Try this ...

declare @xmlstr xml
set @xmlstr = '
<advisements>
<advisement current="true">
<text>This is what was entered</text>
<level>1</level>
<user id="1">test 1</user>
<changed date="2010-09-22 10:40:30" />
</advisement>
<advisement>
<text>Previously entered</text>
<level>2</level>
<user id="2">test 2</user>
<changed date="2010-08-20 09:20:00" />
</advisement>
</advisements>'

set @xmlstr.modify('delete //@current');
set @xmlstr.modify('insert
<advisement current="true">
<text>This is what was entered</text>
<level>1</level>
<user id="1">test 3</user>
<changed date="2010-09-22 10:40:30" />
</advisement>
as first into(/advisements[1])')

For 2008 you can do as:

declare @newNode varchar(300)
set @newNode = '
<advisement current="true">
<text>This is what was entered</text>
<level>1</level>
<user id="1">test 3</user>
<changed date="2010-09-22 10:40:30" />
</advisement>'

declare @xmlstr xml
set @xmlstr = '
<advisements>
<advisement current="true">
<text>This is what was entered</text>
<level>1</level>
<user id="1">test 1</user>
<changed date="2010-09-22 10:40:30" />
</advisement>
<advisement>
<text>Previously entered</text>
<level>2</level>
<user id="2">test 2</user>
<changed date="2010-08-20 09:20:00" />
</advisement>
</advisements>'

set @xmlstr.modify('delete //@current');
set @xmlstr.modify('insert sql:variable("@newNode") as first into(/advisements[1])')

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-10-08 : 07:59:43

thanks namman

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources