xml update

Roy wrote:
> I have id and xml columns in mytable. I want to update an element called
> myelement with the following
>
> UPDATE MyTable SET
> xml.modify
> ('replace value of
> (/root/child/grandchild/myelement)[1] with
> xs:dateTime("2002-05-30T09:30:10Z")')
> WHERE id = '00000000-0000-0000-0000-000000000000'
>
> But I got
> XQuery [mytable.xml.modify()]: The target of 'replace value of' must be a
> non-metadata attribute or an element with simple typed content, found
> 'element(myelement,xdt:untyped) ?'

How does the current content of that 'myelement' look, does it contain
child elements?

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
 >> Stay informed about: xml update 

I don't have xsd associated with the column.

"Joe Fawcett" wrote:

> Is the XML column typed?
>
> --
>
> Joe Fawcett (MVP - XML)
>
> http://joe.fawcett.name
>
> "Roy" wrote in message
>
> >I have id and xml columns in mytable. I want to update an element called
> > myelement with the following
> >
> > UPDATE MyTable SET
> > xml.modify
> > ('replace value of
> > (/root/child/grandchild/myelement)[1] with
> > xs:dateTime("2002-05-30T09:30:10Z")')
> > WHERE id = '00000000-0000-0000-0000-000000000000'
> >
> > But I got
> > XQuery [mytable.xml.modify()]: The target of 'replace value of' must be a
> > non-metadata attribute or an element with simple typed content, found
> > 'element(myelement,xdt:untyped) ?'
> >
> > If I removed "[1]" in the query, I got
> > XQuery [mytable.xml.modify()]: The target of 'replace' must be at most one
> > node, found 'element(myelement,xdt:untyped) *'
> >
> > I am sure it has only one instance of the element in the xml with the
> > given
> > id.
> >
> > What is the problem?
>
>
>
 >> Stay informed about: xml update 

myelement is an xml element with datetime type. So it looks like
<myelement>2008-05-21T09:50:56.531Z</myelement>

"Martin Honnen" wrote:

> Roy wrote:
> > I have id and xml columns in mytable. I want to update an element called
> > myelement with the following
> >
> > UPDATE MyTable SET
> > xml.modify
> > ('replace value of
> > (/root/child/grandchild/myelement)[1] with
> > xs:dateTime("2002-05-30T09:30:10Z")')
> > WHERE id = '00000000-0000-0000-0000-000000000000'
> >
> > But I got
> > XQuery [mytable.xml.modify()]: The target of 'replace value of' must be a
> > non-metadata attribute or an element with simple typed content, found
> > 'element(myelement,xdt:untyped) ?'
>
> How does the current content of that 'myelement' look, does it contain
> child elements?
>
> --
>
> Martin Honnen --- MVP XML
> http://JavaScript.FAQTs.com/
>
 >> Stay informed about: xml update 

Roy wrote:
> myelement is an xml element with datetime type. So it looks like
> <myelement>2008-05-21T09:50:56.531Z</myelement>

Try whether

UPDATE MyTable SET
xml.modify
('replace value of
(/root/child/grandchild/myelement/text())[1] with
xs:dateTime("2002-05-30T09:30:10Z")')
WHERE id = '00000000-0000-0000-0000-000000000000'

works.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
 >> Stay informed about: xml update 

It does not complain and shows "(1 row(s) affected)". However, it I open the
table, the value is not updated.
Any idea?

"Martin Honnen" wrote:

> Roy wrote:
> > myelement is an xml element with datetime type. So it looks like
> > <myelement>2008-05-21T09:50:56.531Z</myelement>
>
> Try whether
>
> UPDATE MyTable SET
> xml.modify
> ('replace value of
> (/root/child/grandchild/myelement/text())[1] with
> xs:dateTime("2002-05-30T09:30:10Z")')
> WHERE id = '00000000-0000-0000-0000-000000000000'
>
> works.
>
> --
>
> Martin Honnen --- MVP XML
> http://JavaScript.FAQTs.com/
>
 >> Stay informed about: xml update 

Roy wrote:
> It does not complain and shows "(1 row(s) affected)". However, it I open the
> table, the value is not updated.

That sounds rather odd. Are you sure the update does not happen?

I tried a simply example with a variable of type xml and that works fine:

DECLARE @x xml;

SET @x = '<root>
<foo>2008-05-22T00:00:00Z</foo>
</root>';

SELECT @x;

SET @x.modify('
replace value of (/root/foo/text())[1]
with xs:dateTime("2002-05-30T09:30:10Z")
');

SELECT @x;


I don't understand why it would not work with a table field. Maybe
someone else does have an idea.


--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
 >> Stay informed about: xml update 

For the first error, you can tell it you are trying to set the text of the element if it is untyped.

UPDATE MyTable SET
xml.modify
('replace value of
(/root/child/grandchild/myelement/text())[1] with
xs:dateTime("2002-05-30T09:30:10Z")')
WHERE id = '00000000-0000-0000-0000-000000000000'

For the second error, I don't believe it matters whether there is only one such element in your XML; I reckon it is parsing the XPath first and doesn't believe it's guaranteed to result in exactly one instance and therefore generates the error. I've even seen this when my schema guarantees that there is only one such element and it makes no difference


> On Wednesday, May 21, 2008 11:35 AM Ro wrote:

> I have id and xml columns in mytable. I want to update an element called
> myelement with the following
>
> UPDATE MyTable SET
> xml.modify
> ('replace value of
> (/root/child/grandchild/myelement)[1] with
> xs:dateTime("2002-05-30T09:30:10Z")')
> WHERE id = '00000000-0000-0000-0000-000000000000'
>
> But I got
> XQuery [mytable.xml.modify()]: The target of 'replace value of' must be a
> non-metadata attribute or an element with simple typed content, found
> 'element(myelement,xdt:untyped) ?'
>
> If I removed "[1]" in the query, I got
> XQuery [mytable.xml.modify()]: The target of 'replace' must be at most one
> node, found 'element(myelement,xdt:untyped) *'
>
> I am sure it has only one instance of the element in the xml with the given
> id.
>
> What is the problem?


>> On Wednesday, May 21, 2008 11:44 AM Joe Fawcett wrote:

>> Is the XML column typed?
>>
>> --
>>
>> Joe Fawcett (MVP - XML)
>>
>> http://joe.fawcett.name


>>> On Wednesday, May 21, 2008 11:51 AM Ro wrote:

>>> I do not have xsd associated with the column.
>>>
>>> "Joe Fawcett" wrote:


>>>> On Wednesday, May 21, 2008 12:15 PM Martin Honnen wrote:

>>>> Roy wrote:
>>>>
>>>> How does the current content of that 'myelement' look, does it contain
>>>> child elements?
>>>>
>>>> --
>>>>
>>>> Martin Honnen --- MVP XML
>>>> http://JavaScript.FAQTs.com/


>>>>> On Wednesday, May 21, 2008 12:48 PM Ro wrote:

>>>>> myelement is an xml element with datetime type. So it looks like
>>>>> <myelement>2008-05-21T09:50:56.531Z</myelement>
>>>>>
>>>>> "Martin Honnen" wrote:


>>>>>> On Wednesday, May 21, 2008 1:11 PM Martin Honnen wrote:

>>>>>> Roy wrote:
>>>>>>
>>>>>> Try whether
>>>>>>
>>>>>> UPDATE MyTable SET
>>>>>> xml.modify
>>>>>> ('replace value of
>>>>>> (/root/child/grandchild/myelement/text())[1] with
>>>>>> xs:dateTime("2002-05-30T09:30:10Z")')
>>>>>> WHERE id = '00000000-0000-0000-0000-000000000000'
>>>>>>
>>>>>> works.
>>>>>>
>>>>>> --
>>>>>>
>>>>>> Martin Honnen --- MVP XML
>>>>>> http://JavaScript.FAQTs.com/


>>>>>>> On Wednesday, May 21, 2008 2:13 PM Ro wrote:

>>>>>>> It does not complain and shows "(1 row(s) affected)". However, it I open the
>>>>>>> table, the value is not updated.
>>>>>>> Any idea?
>>>>>>>
>>>>>>> "Martin Honnen" wrote:


>>>>>>>> On Thursday, May 22, 2008 7:06 AM Martin Honnen wrote:

>>>>>>>> Roy wrote:
>>>>>>>>
>>>>>>>> That sounds rather odd. Are you sure the update does not happen?
>>>>>>>>
>>>>>>>> I tried a simply example with a variable of type xml and that works fine:
>>>>>>>>
>>>>>>>> DECLARE @x xml;
>>>>>>>>
>>>>>>>> SET @x = '<root>
>>>>>>>> <foo>2008-05-22T00:00:00Z</foo>
>>>>>>>> </root>';
>>>>>>>>
>>>>>>>> SELECT @x;
>>>>>>>>
>>>>>>>> SET @x.modify('
>>>>>>>> replace value of (/root/foo/text())[1]
>>>>>>>> with xs:dateTime("2002-05-30T09:30:10Z")
>>>>>>>> ');
>>>>>>>>
>>>>>>>> SELECT @x;
>>>>>>>>
>>>>>>>>
>>>>>>>> I don't understand why it would not work with a table field. Maybe
>>>>>>>> someone else does have an idea.
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>>
>>>>>>>> Martin Honnen --- MVP XML
>>>>>>>> http://JavaScript.FAQTs.com/


>>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>>>> ASP.NET MaskedTextBox Custom Control
>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/39a071cd-9f96-4e81-9346-4e...181d645
 >> Stay informed about: xml update