Regarding xml update in sql server2005

Question

Post reply

Regarding xml update in sql server2005

thummalalavanya

Valued Member

Points: 55
More actions
June 23, 2009 at 4:11 pm

#132183

declare @FeatureList xml
set @FeatureList = '

"True"
"False"
"False"

"True"
"False"
"False"

"True"
"False"
"False"

"True"
"False"
"False"
this is the xml string.in this string they are sending only "True" i want to update only this one into "false" how to do this ..................

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

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

DBA with OCD SSC Enthusiast Points: 199 More actions · Answer 1

Thum,

I'm not exactly sure which "one" of the "Reads" you want to set to False, but you can do something along the lines of:

SET @FeatureList.modify('replace value of (/FeatureList/Feature[@id="9CF9805F-367A-41A2-81F2-78562AA4D310"]/Read)[1]

with xs:string("False")')

Some notes:

- I changed the ID of the Feature nodes (in my testing) so that they weren't the same. Then end in 10, 11, etc. So the one ending in 10 is the only one I'm changing above.

- I also am using an xsd and associated it with the xml variable (@FeatureList)

- I am replacing " True " with False. I'm not storing spaces or quotation marks.

thummalalavanya (6/23/2009)
declare @FeatureList xml set @FeatureList = ' "True" "False" "False" "True" "False" "False" "True" "False" "False" "True" "False" "False"
this is the xml string.in this string they are sending only "True" i want to update only this one into "false" how to do this ..................

thummalalavanya Valued Member Points: 55 More actions · Answer 2

HI,

XQuery [modify()]: The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content, found 'element(Read,xdt:untyped) ?'

It is giving this error.thanks for reply.

but what my intension is they will not send the total string they will send only

false

like i want to update that in the string...................................

DBA with OCD SSC Enthusiast Points: 199 More actions · Answer 3

Thum,

I guess at this point I'm a bit confused about exactly what it is you are trying to do. I thought someone was sending you the XML you provided in @FeatureList and you wanted to know what statement to use to change the Read node from True to False for one Feature.

Maybe you can give a bit more information on what you have, where it is stored, what you're getting (in what format) and what you want to do with it.

Also, have you created an xsd and associated it with the XML variable that contains the node you want to update? If you have some code you are using that is creating this error, please post it so we can see what it is you are currently doing.

Also, please use the IFCodes for tags so that we don't see just False when what we should be seeing is: False

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

First, I'm also completely unsure what you are trying to do with your XML. Anyway, try this:

SET @FeatureList.modify('

replace value of (FeatureList/Feature/Read[text()='' "True" '']/text())[1]

with '' "False" ''

')

SELECT @FeatureList

If this does not give the expected result please describe exactly for one of your "Feature" elements what you want to change at.

As "DBA with OCD" wrote, use the code tags for XML please like:

[ code="xml" ]

<YourXml>Content</YourXml>

[ /code ]

(without the spaces in "code"-tags.