xml update

#71

Roy wrote:

Quote:

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/

#72

Roy wrote:

Quote:

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/

#73

Roy wrote:

Quote:

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/

#74

Roy wrote:

Quote:

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/

#75

Roy wrote:

Quote:

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/

#76

Roy wrote:

Quote:

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/

#77

Roy wrote:

Quote:

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/

#78

Roy wrote:

Quote:

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/

#79

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

Quote:

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?

Quote:

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

Is the XML column typed?

--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name

Quote:

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

I do not have xsd associated with the column.

"Joe Fawcett" wrote:

Quote:

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/

Quote:

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:

Quote:

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/

Quote:

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:

Quote:

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/

Quote:

Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET MaskedTextBox Custom Control
http://www.eggheadcafe.com/tutorials...m-control.aspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

xml update

microsoft.public.sqlserver.xml microsoft.public.sqlserver.xml

Re: xml update - 05-22-2008 , 06:06 AM

Re: xml update - 05-22-2008 , 06:06 AM

Re: xml update - 05-22-2008 , 06:06 AM

Re: xml update - 05-22-2008 , 06:06 AM

Re: xml update - 05-22-2008 , 06:06 AM

Re: xml update - 05-22-2008 , 06:06 AM

Re: xml update - 05-22-2008 , 06:06 AM

Re: xml update - 05-22-2008 , 06:06 AM

Re: xml update - 10-21-2010 , 04:51 AM