March 30, 2011 at 12:04 pm
I have an XML package similar to the following:
<package>
<header>
<recipient>123456</recipient>
</header>
<contact>
<first_name>John</first_name>
<middle_name />
<last_name>Doe</last_name>
<ssn>123456789</ssn>
<spouse>
<first_name>Jane</first_name>
<middle_name />
<last_name>Doe</last_name>
<ssn>987654321</ssn>
</spouse>
<drivers>
<driver is-lead="true">
<drivers_license>
<state_province />
<number>DLNUM123</number> />
</drivers_license>
</driver>
<driver>
<drivers_license>
<state_province />
<number>DLNUM456</number>
</drivers_license>
</driver>
</drivers>
</contact>
</package>
I need to find and replace all occurrances of the driver license number and the SSN values. Is there a way to iterate through the XML and find all occurrances of these elements? I am especially interested in the SSN and spouse SSN elements which are different levels in the XML.
I have done simple search and replace XQuery statements, but only when I know the exact path to the element.
The first take is to check for the existence of each possible path and change it if it exists. I am hoping there is a more generic way to locate the element names no matter where they are in the schema.
Thanks in advance.
March 31, 2011 at 6:34 am
Would something along these lines work?
DECLARE @xml XML = N'<package>
<header>
<recipient>123456</recipient>
</header>
<contact>
<first_name>John</first_name>
<middle_name />
<last_name>Doe</last_name>
<ssn>123456789</ssn>
<spouse>
<first_name>Jane</first_name>
<middle_name />
<last_name>Doe</last_name>
<ssn>987654321</ssn>
</spouse>
<drivers>
<driver is-lead="true">
<drivers_license>
<state_province />
<number>DLNUM456</number> />
</drivers_license>
</driver>
<driver>
<drivers_license>
<state_province />
<number>DLNUM456</number>
</drivers_license>
</driver>
</drivers>
</contact>
</package>' ;
SELECT @xml AS before
WHILE @xml.exist('//number[.!="masked"]') = 1
BEGIN
SET @xml.modify('
replace value of (
//number[.!="masked"]/text()
)[1]
with "masked"
') ;
END ;
SELECT @xml AS [after] ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 31, 2011 at 11:10 am
Thanks!
This does what I need it to do. However, I am having trouble putting it into a set-based statement that performs this on the XML data column in each row of a table. Is this one of those times when I should go with a cursor?
March 31, 2011 at 1:07 pm
I did have to use a cursor to make it work like I wanted.
Thanks for the help!
April 1, 2011 at 8:12 am
I looked around for a way to do the update all in one statement and I think it's possible using the query() method on the XML data type column and a serious FLWOR expression...but I am a newbie to XQuery too so it's beyond my skill level to write it at this point. Structurally I think the UPDATE would look something like this:
UPDATE dbo.table_name
SET xml_column = xml_column.query('FLWOR expression with lots of nested for loops to query
the xml column and return the same xml structure while masking the
sensitive data')
WHERE xml_column.exists('xml query to test the column has not yet been cleansed') = 1 ;
Do you see any issue with the above approach? It assumes the XML structure is well-known and that change to the structure is controlled since the FLWOR expression will in essence reconstruct the XML from itself so it's risky in case new elements are added but the expression is not updated. I doubt this would be practical or better than what you're doing with the cursor (although I would look into using a while loop instead of a cursor) but it would be cool to know if something like it is feasible or had been done by someone.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply