MS SQL XML - Modifying data in complex type

Author

Modifying data in complex type

h_e_kyle

2005-12-29, 1:23 pm

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!

Michael Rys [MSFT]

2005-12-30, 3:24 am

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@discussions.microsoft.com> wrote in message
news:1E372518-91E9-4BEA-BB10- 56C1AAEC7DDB@microso
ft.com...
>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!
>

h_e_kyle

2005-12-30, 11:23 am

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@discussions.microsoft.com> wrote in message
> news:1E372518-91E9-4BEA-BB10- 56C1AAEC7DDB@microso
ft.com...
>
>
>

Michael Rys [MSFT]

2005-12-30, 8:23 pm

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@discussions.microsoft.com> wrote in message
news:E466ECFE-18A5-47FD-9095- D0FB4C962BDE@microso
ft.com...[color=darkred]
> 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:
>

h_e_kyle

2006-01-03, 9:23 am

Michael,

I thought I had already tried what you suggested, but I must have had the
syntax incorrect somewhere, or my predicates weren't correct, or something.
Either way, I just tried it as you suggested and it worked perfectly!

Thank you so much for your help, it was invaluable!

"Michael Rys [MSFT]" wrote:

> 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@discussions.microsoft.com> wrote in message
> news:E466ECFE-18A5-47FD-9095- D0FB4C962BDE@microso
ft.com...
>
>
>

Sponsored Links