Given the following XML in a table column:
<UserFields>
<UserField>
<Name>UserName</Name>
<Value>test_user</Value>
</UserField>
<UserField>
<Name>Age</Name>
<Value>24</Value>
</UserField>
<UserField>
<Name>Password</Name>
<Value>test1234!</Value>
</UserField>
<UserField>
<Name>AgentPassword</Name>
<Value>1234Test!</Value>
</UserField>
</UserFields>
<ActionAttributes>
<Action>
<Name>LoginPassword</Name>
<Value>Test123!</Value>
</Action>
</ActionAttributes>
I want to query the XML to find any element that has a <Name> node that has text() containing the word password and replace the text of the <Value> node with asterisks. In the sample, there would be three replacements and I would wind up with the following XML.
<UserFields>
<UserField>
<Name>UserName</Name>
<Value>test_user</Value>
</UserField>
<UserField>
<Name>Age</Name>
<Value>24</Value>
</UserField>
<UserField>
<Name>Password</Name>
<Value>********</Value>
</UserField>
<UserField>
<Name>AgentPassword</Name>
<Value>********</Value>
</UserField>
</UserFields>
<ActionAttributes>
<Action>
<Name>LoginPassword</Name>
<Value>********</Value>
</Action>
</ActionAttributes>
I was able to come up with this:
SET @MyDoc.modify('
replace value of (//UserField[Name="Password"]/Value/text())[1]
with "********"
');
The problem with this code is:
- It hardcodes the name of the parent element (‘UserField’).
- It hardcodes the name of the value of
<Name>so it only finds ‘password’ and not other variations.
In English, I would say:
“For any node that has <Name> and <Value> child nodes. If child node <Name> contains the word ‘password’, then replace the text of the child node <Value> with asterisks.”
4
XPath expression must define a single node
Expression1
Identifies a node whose value is to be updated. It must identify only a single node. That is, Expression1 must be a static singleton. If the XML is typed, the type of the node must be a simple type. When multiple nodes are selected, an error is raised.
Using sql variables could be an alternative to avoid hardcoding the predicate in the expression
DECLARE @Name VARCHAR(50) = 'Password';
DECLARE @NewValue VARCHAR(50) = '********';
SET @MyDoc.modify('
replace value of (//UserField[Name = sql:variable("@Name")]/Value/text())[1]
with sql:variable("@NewValue")
');