Our database contains logging of XML SOAP messages. The XML contains passwords, like: secret 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: declare @xml xml --SELECT @xml = xmlField FROM yourTable WHERE .... --Use sample data instead of real one SET @xml = N' secret ' --Modify the XML stored in the @xml varible - remove the node. --If the node doesn't exists, then nothing happens and original xml is returned SET @xml.modify('declare namespace soapenv="
http://www.w3.org/2003/05/soap-envelope"; declare namespace wsse="
http://www.w3.org/2003/05/soap-security"; delete /soapenv:Envelope/soapenv:Header/wsse:Security/wsse:UsernameToken/wsse:Password') SELECT @xml :
One simple way I've found is to strip out the SOAP header: select scl.Request.query('(//*:Body)') from dbo.LoggingTable However, that would not filter out password elements from the body of the message.