MS SQL XML - Error on xml.modify(...) to insert a new node in existing value

Author

Error on xml.modify(...) to insert a new node in existing value

Brice Prunier

2005-09-21, 7:23 am

I have a SQL table named Persons with a Typed Xml field ( XmlValue )
In a record my XmlValue field is
<Person xmlns="http://copro/Copro.Persons"
oid="ca14dfd5-94df-40f7-8c49-48e74b98e98d">
<Reference>pl-001</Reference>
<Arrival>
<Date>1992-08-27Z</Date>
<Dept>Finance</Dept>
</Arrival>
<TypedAddress " type="Delivery">
<Zip>111</Zip>
<Town>Town1</Town>
</TypedAddress>
<TypedAddress type="Office">
<Zip>222</Zip>
<Town>Town2</Town>
</TypedAddress>
<Name>
<Civility type="MISS"/>
<First>Xxx</First>
<Last>Yyyy</Last>
</Name>
</Person>

I want to insert a node between the two <TypedAddress> with the following
statement

UPDATE Persons SET XmlValue.modify( '
declare namespace per="http://copro/Copro.Persons";
insert
<per:TypedAddress type="Extra">
<per:Zip>3333</per:Zip>
<per:Town>Town3</per:Town>
</per:TypedAddress>
before
/per:Person[per:Reference="pl-001"]/per:TypedAddress[@type="Office"][1]' )
WHERE 'pl-001'=Reference

the return error is the following:
XQuery [Persons.XmlValue.modify()]: The target of 'insert' must be a single
node, found
'element(per{[url]http://copro/Copro.Persons[/ url]}:TypedAddress,p
er{[url]http://copro/Copro.Persons[/ url]}:TypedAddressTy
pe) *'

my before statement return a single a node ! ( there is child nodes but the
root is single ).

Does it means it wont be possible to handle collection ?

Because in my case ( i make it on purpose ) previous and following nodes for
TypedAddress are complex type too.

I do hope i'm wrong and there is a way to fix it...

Kent Tegels

2005-09-21, 7:23 am

Hello Brice,

You're really close. Try:

before (/per:Person[per:Reference="pl-001"]/per:TypedAddress[@type="Office"])[1]'
)

Yes, the parens really do matter -- with this, you're creating a sequence
and then asking for the first member of it.

Note that can't verify that this will work against your schema since I don't
have access to that, but it worked fine for me untyped.

Thanks!

Kent Tegels
DevelopMentor
Blogging @ http://staff.develop.com/ktegels/

Han

2005-09-21, 7:23 am

Kent's xpath should return singleton or empty sequence always/wherever.

Xpath like

(/any-xpath)[1]

is statically a singleton or empty sequence. It just means that search the
first node satisfying the condition in the whole document.

On the other hand,

/any-xapth[1]

just searches the node not having any preceding-sibling. Number of such
nodes can be more than one.

Example,

declare @x xml
set @x='<a><b><c/></b><b><c/></b></a>'

select @x.query('count((/a/b/c)[1]), count(/a/b/c[1])')

--
Pohwan Han. Seoul. Have a nice day.
"Kent Tegels" <ktegels@develop.com> wrote in message
news:bc59bc32a69b8c7
8c8e1cffb675@news.microsoft.com...
> Hello Brice,
>
> You're really close. Try:
>
> before
> (/per:Person[per:Reference="pl-001"]/per:TypedAddress[@type="Office"])[1]'
> )
>
> Yes, the parens really do matter -- with this, you're creating a sequence
> and then asking for the first member of it.
>
> Note that can't verify that this will work against your schema since I
> don't have access to that, but it worked fine for me untyped.
>
> Thanks!
>
> Kent Tegels
> DevelopMentor
> Blogging @ http://staff.develop.com/ktegels/
>
>

Sponsored Links