Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Importing the OFAC SDN list via XML Expand / Collapse
Author
Message
Posted Monday, February 5, 2007 1:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:16 AM
Points: 160, Visits: 247

For those not familiar with the OFAC (Office of Foreign Assets Control), they maintain a list of Specially Designated Nationals (SDN) in ASCII and now XML with an XSD layout that financial institutions check against.

This list is updated aperiodically (I've only seen additions, never deletions or updates).

I am wanting to automate the updating of this list into a SQL table.

To view the XML/XSD go here: http://www.treas.gov/offices/enforcement/ofac/sdn/index.shtml

Unfortunately, they do not provide a webservice type of interface, simply the XSD/XML.

Originally, I manually created the SQL table and then used DTS to import the data. Since they can update the XSD, I was looking to automate this process a bit more. Has anyone already tackled this task in a more automated fashion that would be willing to share their methodology?




Post #342592
Posted Thursday, February 8, 2007 8:00 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
Post #343479
Posted Wednesday, December 12, 2007 2:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:21 AM
Points: 16, Visits: 36
my name is Pablo Cecere.
I have to deal with the OFAC list to import it to SQL Server.
If you didnt do it yet, we can work together.
Pablo
Post #432551
Posted Wednesday, December 12, 2007 4:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:16 AM
Points: 160, Visits: 247
Pablo,
I posted a query on Guru.com and found someone that had already written a C# app to import the XML data into SQL. They gave me a very reasonable quote and it works very well. For the time it saved, its a no-brainer.

His contact info if you want: Mark Gerlach [mark AT efgtech DOT com]
(modified email for those nasty spam bots)



Post #432605
Posted Wednesday, January 2, 2008 10:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:21 AM
Points: 16, Visits: 36
I tried contacting this guy, but he didnt answer my emails.
Can you try to contact him? Or help me get this ?
Thanks a lot
Pablo

Happy New Year
Post #438057
Posted Wednesday, January 2, 2008 10:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:16 AM
Points: 160, Visits: 247
I will send him an email with a link to this forum posting and ask him to contact you.


Post #438059
Posted Thursday, January 3, 2008 10:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:21 AM
Points: 16, Visits: 36
Tim, after importing the list ...
did you programmed a Stored Procedure or something to match the names?
Do you match exact names, or did something different?

Thanks
Pablo
Happy New Year!!
Post #438522
Posted Thursday, January 3, 2008 11:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:16 AM
Points: 160, Visits: 247
The OFAC data is imported from the XML file the USTD provides to its own database (multiple tables). The import program drops/recreates the tables each time.

The main identity table (sdnentry) has a unique ID that stays the same from version to version from the USTD.

I have a separate table to maintain the one to many relationship between our clients and the potential matches. So each time the OFAC data is updated and we compare against our current client list, we know which ones were potential matches before and only need to check/verify any new matches. I actually run the check once each day as one of our daily processes. This compares any new clients that were added during the day.

As for what I use to compare - the data in the OFAC list is 'ugly' to be polite, so doing a straight string compare would miss anything unless they matched exactly. So, I use a simply SOUNDEX methodology. Nothing special - but a heck of a lot better than what the USTD/SEC websites provide.

To do the SOUNDEX comparison, I use the TSQL DIFFERENCE function with a value of 4. I have found that gets any matches that are close and accounts for the most common english misspellings in names. Its not sophisticated, but it works.

I did find a commercial library (no longer have the name) that worked exceptionally well- but they wanted a huge sum to license (over 25k min) and it was very restrictive in its use.



Post #438529
Posted Saturday, January 5, 2008 3:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:21 AM
Points: 16, Visits: 36
I am working on the import process. Hopefully finished by tomorrow.
Do you want to see the way I am doing it.
It is a stored procedure that checks if the SDN list has been updated, and the imports the new list if necessary.

Interested?
Post #439274
Posted Saturday, January 5, 2008 9:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:16 AM
Points: 160, Visits: 247
I appreciate the offer, but no need - our methdology is customized to our needs and works well.




Post #439304
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse