sql server - TSQL Xpath Modify XML string -

i trying modify xml string replace value of node else. end goal mass encrypting account numbers in column time being, i'm trying replace 1 in xml string.

when execute this, "command completed successfully" though trying return data.

declare @xml xml = '<optional><account>155555555</account></optional>'   set @xml.modify('replace value of (/optional/account/text())[1] "' + (               select 'abc-' + paramvalues.item.value('.', 'varchar(max)')               @xml.nodes('/optional/account') paramvalues(item)        )+ '"')  select @xml 

any thoughts why isn't replacing account number in xml string abc-account number here , returning it?

to answer specific question, "why isn't replacing account number", it's because xml function parameters must string literals. constructing parameter modify xml function, causing error condition.

normally, sql server throws error when aren't using string literal in xml function parameter, in case, must confused because of nodes() function call.

here simple example string literal works, point out in comment:

declare @xml xml = '<optional><account>155555555</account></optional>' set @xml.modify('replace value of (/optional/account/text())[1] "1"'); select @xml; 

however, if try construct modify xml function parameter, in below example, fail:

declare @xml xml = '<optional><account>155555555</account></optional>' set @xml.modify('replace value of (/optional/account/text())[1] "' + '1' + '"'); select @xml; 

the error is:

msg 8172, level 16, state 1, line 2 argument 1 of xml data type method "modify" must string literal.

apparently, if little fancier throwing nodes() in there, confuses sql server , squelches error condition:

declare @xml xml = '<optional><account>155555555</account></optional>' set @xml.modify('replace value of (/optional/account/text())[1] "' + (select '1' @xml.nodes('/optional/')) + '"'); select @xml; 

that doesn't error, rather terminates before displaying data , prints:

command(s) completed successfully.

so can't construct xml function parameter. however, can still use outside information. kiran hedge showed how using sql:variable() xquery extension function.

however, don't have go length. using data inside xml, xml processor has native access to. can this:

declare @newxmlsinglereplacement xml = '<optional><account>155555555</account></optional>'; set @newxmlsinglereplacement.modify('replace value of ((/optional/account)/text())[1] fn:concat("abc-",((/optional/account)/text())[1])'); select @newxmlsinglereplacement; 

so either kiran's or solution works fine simplified example. have more complex xml document. has multiple "rows" you'd change.

if try same code above xml document multiple account numbers, first number replaced:

declare @newxmlsinglereplacement xml ='<optional><account>155555555</account></optional><optional><account>255555555</account></optional>';     set @newxmlsinglereplacement.modify('replace value of ((/optional/account)/text())[1] fn:concat("abc-",((/optional/account)/text())[1])');     select @newxmlsinglereplacement; 

results:

<optional>   <account>abc-155555555</account> </optional> <optional>   <account>255555555</account> </optional> 

you might think remove index (1) , affect of rows. unfortunately, according microsoft's documentation, first parameter of replace value of "must identify single node". can't take list of of account numbers , operate on that.

this example:

declare @newxmlsinglereplacement xml ='<optional><account>155555555</account></optional><optional><account>255555555</account></optional>'; set @newxmlsinglereplacement.modify('replace value of ((/optional/account)/text()) fn:concat("abc-",((/optional/account)/text()))'); select @newxmlsinglereplacement; 

results in error:

msg 2389, level 16, state 1, line 2 xquery [modify()]: 'concat()' requires singleton (or empty sequence), found operand of type 'xdt:untypedatomic *'

so instead, loop on of "rows" , perform modify() operation each time. need way track progress counter. here example of that, using more complex xml prove concept.

declare @xml xml = '<optional><other>test123</other><account>155555555</account></optional><optional><other>test321</other><account>255555555</account></optional>'; declare @newxml xml = @xml, @accountcount int = 0, @counter int = 0; set @accountcount = @newxml.value('fn:count(//account)','int'); while @counter <= @accountcount begin     set @counter = @counter + 1;     set @newxml.modify('replace value of ((/optional/account)[position()=sql:variable("@counter")]/text())[1] fn:concat("abc-",((/optional/account)[position()=sql:variable("@counter")]/text())[1])'); end select  @newxml;  

results:

<optional>   <other>test123</other>   <account>abc-155555555</account> </optional> <optional>   <other>test321</other>   <account>abc-255555555</account> </optional> 

of course prefer avoid loops in sql code, if can. single statements operate on sets yield better performance.

one option shred xml , reform it, while adjusting values in process. disadvantage of method must know specifics of xml in order reconstruct it. might expensive , convoluted statement, depending on complexity of xml document. here example:

declare @xml xml = '<optional><other>test123</other><account>155555555</account></optional><optional><other>test321</other><account>255555555</account></optional>'; select     v.value('(./other)[1]','varchar(500)') other,     'abc-' + v.value('(./account)[1]','varchar(500)') account        @xml.nodes('/optional') t(v) xml path ('optional'), type; 

but that's not way reform xml. reconstruct using xml system , flwor statement support. example:

declare @xml xml = '<optional><other>test123</other><account>155555555</account></optional><optional><other>test321</other><account>255555555</account></optional>'; select @xml.query ('     $optional in //optional     return         <optional>             {$optional/other}             <account>abc-{$optional/account/text()}</account>         </optional>  '); 

but again, requires knowing , manually recreating structure of xml. there ways avoid that. next example requires minimal knowledge of existing xml. loops on nodes @ level of account node , replaces them if named "account".

declare @xml xml = '<optional><other>test123</other><account>155555555</account></optional><optional><other>test321</other><account>255555555</account></optional>'; select @xml.query ('     $optional in //optional     return         <optional>             {                 $subnode in $optional/*                     return                         if (fn:local-name($subnode) = "account")                                                         <account>abc-{$subnode/text()}</account>                         else                             $subnode             }         </optional> '); 

based on crude test set statistics time on; on these small example xml documents, appears nodes() shredding , reconstructing faster. has simplest , lowest cost query plan significant margin.