MS SQL XML - using a xpath variable in a replace xquery, getting an error

Author

using a xpath variable in a replace xquery, getting an error

JCollum

2006-11-03, 7:14 pm

I'm trying to do a dynamic xpath in a query and the compiler isn't
allowing it. I'm getting a "The target of 'replace' must be at most
one node, found 'xs:string ?" error. Umm, yeah that's fine except I've
got that [1] on the end of the xpath. Seems to me that makes it one
node ya know?

So I'm stuck. I'm gonna investigate getting the nodes, ie
MyXml.nodes('/root/product/name') as NewTable(ProductName
).

This looks like the compiler thinking that it's smarter than me. I doan
like eet.

--SQL Code:

declare @nodeCount int, @xpath nvarchar(100), @pr nvarchar(100),
@newVal nvarchar(100)

set @xpath = '/path1/elem1'

--xml-column.modify('replace value of xpath-expression with new-value')
sql:variable("@xpath")
UPDATE MyTable SET MyXml.modify(
'
replace value of (sql:variable("@xpath"))[1] --<-- Error here
with "someNewStuff"
')
where MyId = 2

--END CODE

JCollum

2006-11-03, 7:14 pm

I thought I'd be able to get around it with the nodes command but no
go:

SELECT T.c.query('.') AS XmlResult
FROM @xml. nodes('sql:variable(
"@xpath")') T(c)

Same error. It works if I put in the xpath itself. Now I'm really
ticked because I spent a solid day developing a trigger in my database
that was based on xpath variables as an input. Guess what: you can't do
that. You apparently can't even build up a string or anything.

It's a literal hardcoded xpath or nothing. Now where in all of the .NET
world will you find similar behavior? Can't think of any place.

JCollum

2006-11-03, 7:14 pm

It gets more confusing. This statement:

SELECT @xml. exist('sql:variable(
"@xpath")')

works. Returns a 1. Grr.

JCollum

2006-11-07, 7:16 pm

Hi sorry, but I'm gonna bump this one. Does anyone have any suggestions
on how to get around this problem?

-- Justin

On Nov 3, 4:39 pm, "JCollum" <jcol...@gmail.com> wrote:
> It gets more confusing. This statement:
>
> SELECT @xml. exist('sql:variable(
"@xpath")')
>
> works. Returns a 1. Grr.

JCollum

2006-11-07, 7:16 pm

Found a solution:

http://blogs.msdn.com/mrorke/

Short answer is that you build a query (@query) then exec it
(Exec(@query))

On Nov 6, 9:34 am, "JCollum" <jcol...@gmail.com> wrote:[color=darkred
]
> Hi sorry, but I'm gonna bump this one. Does anyone have any suggestions
> on how to get around this problem?
>
> -- Justin
>
> On Nov 3, 4:39 pm, "JCollum" <jcol...@gmail.com> wrote:
>
>
>
>
>

JCollum

2006-11-07, 7:16 pm

One more thing for completeness: if you ever ever ever need to have a
variable in the path portion of an xpath statement you will have to
build the string then run the exec. Any sql variable in an xquery that
is in the path portion of an xpath statement will be blasted out and
replaced with nothing.

JCollum wrote:[color=darkred
]
> Found a solution:
>
> http://blogs.msdn.com/mrorke/
>
> Short answer is that you build a query (@query) then exec it
> (Exec(@query))
>
>
> On Nov 6, 9:34 am, "JCollum" <jcol...@gmail.com> wrote:

Sponsored Links