Remove password node from XML

Our database contains logging of XML SOAP messages. The XML contains passwords, like:

 <soapenv:Envelope ...>
     <wsse:Security ...>
         <wsse:UsernameToken ...>
             <wsse:Password ...>secret</wsse:Password>

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.)

more ▼

asked Nov 22, 2013 at 11:07 AM in Default

avatar image

330 3 5 10

To clarify, you want to store the XML unchanged? But remove the password element in querying?

Or not store the password element?

Nov 22, 2013 at 11:55 AM Kev Riley ♦♦

@KevRiley: Storage should remain unchanged, I'd like to remove the password element in querying.

Nov 22, 2013 at 12:24 PM Andomar

Querying by application, or casual querying by a user using SSMS?

Nov 22, 2013 at 01:13 PM Kev Riley ♦♦

@KevRiley: By application (we'll have control over the SQL)

Nov 22, 2013 at 01:19 PM Andomar

In that case why not have the application handle the requirement? (just asking)

Nov 22, 2013 at 01:26 PM Kev Riley ♦♦
show all comments (comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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'<soapenv:Envelope xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope">
         <wsse:Security xmlns:wsse="http://www.w3.org/2003/05/soap-security">
 --Modify the XML stored in the @xml varible - remove the <wsse:Password> node. 
 --If the <wsse:Password> 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')

more ▼

answered Nov 22, 2013 at 08:00 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

Thanks, it's about 10-30 messages per query, but I could pump them through a temporary table. "delete //*:Password" seems to remove all password elements regardless of namespace.

Nov 23, 2013 at 04:54 PM Andomar

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.

Nov 23, 2013 at 05:10 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 22, 2013 at 12:25 PM

avatar image

330 3 5 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 22, 2013 at 11:07 AM

Seen: 8548 times

Last Updated: Nov 23, 2013 at 05:10 PM

Copyright 2018 Redgate Software. Privacy Policy