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")
');