Update XML Element - Syntax issues

Question

Post reply

Update XML Element - Syntax issues

jevon.hills

SSC Rookie

Points: 31
More actions
March 31, 2008 at 11:44 am

#74661

Attached is my xml that i'm trying to update:
I want to update the store_number from null to a value. Let's say 1. I have tried several forms of the syntax and I can't find the incantation. I'm assuming it has to do with typed xml, but after several hours I've had no luck solving this.
Can anyone please help me do this.
TIA
Jevon

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

RyanRandall SSChampion Points: 13623 More actions · Answer 1

Like this?

declare @x xml

set @x = '<xdata>

<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<name>bob</name>

<store_number xsi:nil="true" />

</project>

</xdata>'

SET @x.modify('insert text{"1"} as first into (//xdata/project/store_number)[1]')

select @x

--result

/*

<xdata>

<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<name>bob

<store_number xsi:nil="true">1</store_number>

</project>

</xdata>

*/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

jevon.hills SSC Rookie Points: 31 More actions · Answer 2

Thanks! Second question then...once I have inserted the value of 1 into storenumber as you have shown me is there a way to remove the xsinil attribute or set it to false?

Jevon

RyanRandall (4/8/2008)
Like this?
declare @x xml
set @x = '<xdata>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<name>bob</name>
<store_number xsi:nil="true" />
</project>
</xdata>'
SET @x.modify('insert text{"1"} as first into (//xdata/project/store_number)[1]')
select @x
--result
/*
<xdata>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<name>bob
<store_number xsi:nil="true">1</store_number>
</project>
</xdata>
*/

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Easy...

SET @x.modify('delete //xdata/project/store_number/@xsi:nil')

This is XML DML, and you can read about it in BOL or online...

http://msdn2.microsoft.com/en-us/library/ms177454.aspx

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

jevon.hills SSC Rookie Points: 31 More actions · Answer 4

Thanx very much. I thought I had tried to delete the attribute xsinil that way already, but apparently not.

Your responses are very much appreciated.

Jevon