Re: Modifying data in complex type
- From: "Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 30 Dec 2005 13:08:12 -0800
I think your problem with the insert is that you have not specified a single
static node.
Try something like:
insert <Eyes><LeftEye>purple</LeftEye><RightEye>black</RightEye></Eyes>
into (test:Face[yourpredicate])[1]
Best regards
Michael
"h_e_kyle" <hekyle@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E466ECFE-18A5-47FD-9095-D0FB4C962BDE@xxxxxxxxxxxxxxxx
> Sorry I didn't mention that...I am using SQL Server 2005. I was originally
> trying to modify the contents using just a 'replace value of' in a
> modify()
> method, which is what I realized I cannot do. It was rejected because I
> was
> trying to replace the value of a non-atomic type. This 'Face' node is one
> node in a larger Xml Document stored in a SQL Server 2005 table of typed
> Xml.
>
> I tried a delete and an insert, but (and perhaps I was doing it
> incorrectly)
> the insert did not work. The command I was using was like this:
>
> UPDATE Table1 SET XmlData.modify('declare namespace test="http://...";;
> insert <Eyes><LeftEye>purple</LeftEye><RightEye>black</RightEye></Eyes>
> into test:Face
>
> I also had a conditional statement to select the specific 'Face' to insert
> into. Again, I received an error because I was trying to insert something
> that was not a simple type.
>
> Would you be able to give me an example (or a link to an example, or even
> the name of a book with an example would be great) of the first option you
> presented (for updating the old tree with the new one)? I have seen
> mention
> made about the ability to do that, but I have not seen how to implement
> it.
>
> Thank you so much for taking the time to respond, and Happy New Year!
> Heather
>
> "Michael Rys [MSFT]" wrote:
>
>> Are you using SQL Server 2005 or 2000?
>>
>> In 2005, you can do:
>>
>> 1. Create a new instance with the new Eyes (using FOR XML and XQuery) and
>> update the old tree with the new one.
>>
>> 2. Do a delete and an insert in two modify() methods.
>>
>> Best regards
>> Michael
>>
>> "h_e_kyle" <hekyle@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:1E372518-91E9-4BEA-BB10-56C1AAEC7DDB@xxxxxxxxxxxxxxxx
>> >I need to be able to modify the contents of a node such as this:
>> > <Face>
>> > <Eyes>
>> > <LeftEye>blue</LeftEye>
>> > <RightEye>brown</RightEye>
>> > </Eyes>
>> > </Face>
>> >
>> > Ideally I would like to do this by replacing the Face contents with a
>> > completely new 'Eyes' node. Since SQL server does not allow for complex
>> > types
>> > in its update methods, I am wondering if anyone has any suggestions for
>> > a
>> > way
>> > to work around this limitation?
>> >
>> > Thanks!
>> >
>>
>>
>>
.
- References:
- Re: Modifying data in complex type
- From: Michael Rys [MSFT]
- Re: Modifying data in complex type
- Prev by Date: Re: Solved - Re: Bulk Upload : General operational error
- Next by Date: Re: DataSet Xml DateTime incompatible with Sql 2000
- Previous by thread: Re: Modifying data in complex type
- Next by thread: Re: DataSet Xml DateTime incompatible with Sql 2000
- Index(es):
Relevant Pages
|
