Creating Nested Nodes

I am trying to write a stored procedure that will accept an XPATH to an element or an attribute, that is held in an SQL XML field, and then either updates or creates it setting its value to a @.value, also passed in.

Issue is the end point or the path to the end point may not entirely exist

For instance if i passed the following to the stored procedure, i would expect the node to be updated with the value.

xpath : /root/doc[@.ID=1]/Resource/Media[@.ID]

Value to be set : 10

I would assume that in order to set or update the node, that it would have to exist first. ?

Also, is it possible to create one XQuery that will check the path sent and then update or create it. ?

Thanks

Currently we do not allow variable parameters to be passed to the xml datatype .exist, .query() and .modify() methods. That means in order for you xpath to be parametric, you will have to construct dynamic queries. You can either do this on the client side or in a stored procedure that then calls sp_executesql. However, this opens you up to security risks (sql injection), so is not an ideal solution.