asked
Remove password node from XML
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.)
To clarify, you want to store the XML unchanged? But remove the password element in querying? Or not store the password element?
Querying by application, or casual querying by a user using SSMS?
In that case why not have the application handle the requirement? (just asking)
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)][1]. 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 [1]:
http://technet.microsoft.com/en-us/library/ms190254.aspx
Yeah, it depends on your scenario. As you have mentioned, multiple can be passed through temp table and the individual deletion depends on your concrete XML. This was a sample how to achieve tis. Good, that it helped.
One simple way I've found is to strip out the SOAP header: select scl.Request.query('(//*:Body)[1]') from dbo.LoggingTable However, that would not filter out password elements from the body of the message.
15 People are following this question.
