Our database contains logging of XML SOAP messages. The XML contains passwords, like:
For troubleshooting, we'd like to offer a list of messages. However the password should be filtered out.
Is there a way in SQL Server to query XML messages while removing all elements called "password" (preferably regardless of namespace.)
The easiest way would be by using XSLT, unfortunately XSLT is not supported in T-SQL XML.
But with multiple steps during querying, if the password node is always under specific node location, you can achieve this by using the **`delete (XML DML).
Lets say, first you retrieve the XML from table into a @xml variable. Then you invoke the 1`delete (XML DML)` on the variable and finally return the modified XML.
Something like the sample below:
One simple way I've found is to strip out the SOAP header:
However, that would not filter out password elements from the body of the message.
answered Nov 22, 2013 at 12:25 PM