Use SQL local variable for a XML Node

Question

I am trying to pass a local variable within a XML.modify function as a node within my SQL Script as the xml trying to be modified is dynamic. See code below

DECLARE
    @XML VARCHAR(MAX) = 'ns:Messages/ns:T001.0_NewSPIDRequests/ns:T001.0_NewSPIDRequest/ns:D2009_SWConnectionRef'

;WITH XMLNAMESPACES('urn:bridgeall-com:cmaservice:data:v3' AS ns)
UPDATE XMLMessageFeeder
SET theXML.modify('replace value of (/ns:Submission/*[local-name(.) = sql:variable("@XML")]/text())[1] with ("New Value")')

SELECT theXML 
FROM XMLMessageFeeder

The issue I'm having is the above doesn't update the the value of that node yet if I was to set the replace as the following:

SET theXML.modify
             ('
                   replace value of (/ns:Submission/ns:Messages/ns:T001.0_NewSPIDRequests/ns:T001.0_NewSPIDRequest/ns:D2009_SWConnectionRef/text())[1]
                  with ("New Value")
             ')

This works. From my knowledge (which is little) they are both passing the same replace code yet using a variable doesnt seem to work.

Can someone point me in the right direction?

EDIT Answer can be found below in the comments.

Mikael Eriksson · Accepted Answer · 2015-12-16 13:11:22Z

1

local-name(.) returns the name of the current node. You can not use local-name(.) to compare against a path expression. Only one node at a time.

answered Dec 16, 2015 at 13:11

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Blahwoo Over a year ago

so having all nodes within one variable is basically a dead end? I would have to have multiple variables which contain each individual node that im looking for. EG Variable1 = T001.0_NewSPIDRequests, Variable2 = T001.0_NewSPIDRequest etc, then within the replace value have it something along the lines of

(/ns:Submission/ns:Messages/ns:*[local-name() = sql:variable("@Variable1")]/ns:*[local-name() = sql:variable("@Variable2")]

Mikael Eriksson Over a year ago

@Blahwoo Yes. You can do it with multiple variables but you have to decide the depth of the path beforehand (looks like four in your case). You could of course build the update statement dynamically and execute using sp_executesql. Or if the final leaf node name is enough to find the node you are looking for you can use // to do a deep search for a node. Something like //*[local-name(.) = sql:.....

Blahwoo Over a year ago

Thanks for that! That has help me out alot :)

Gottfried Lesigang Over a year ago

@Blahwoo, I just pushed you over the magic 15 rep points so you might vote up here too :-)

Collectives™ on Stack Overflow

Use SQL local variable for a XML Node

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related