Importing the OFAC SDN list via XML

Question

Post reply

Importing the OFAC SDN list via XML

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

divyeshkhatri Valued Member Points: 55 More actions · Answer 1

Hello...

I've created import routines for SDn list.

Let me know if you are interested...

I can help you.

Divyesh

pcecereatgmaildotcom SSChasing Mays Points: 648 More actions · Answer 2

i am very interested.

tell me a little bit more about it

Thanks

Pablo

divyeshkhatri Valued Member Points: 55 More actions · Answer 3

Hi Pablo..

I have application that requires importing SDN list and keeping it up-to-date. If a new name is added it should be inserted into tables and if any name is removed from the list, it should be marked "inactive" but should still be kept in the database.

I created SSIS package that downloads XML file from FTP site and wrote stored procs to process the files into SQL Tables.

If you have the requirements and would like to share with me let me know... I can do it for you...

Divyesh

pcecereatgmaildotcom SSChasing Mays Points: 648 More actions · Answer 4

i need to do exactly the same.

there is not a lot to invent about this.

pls, see my nick, and contact me so we can arrange somthing about it.

thanks

Pablo

divyeshkhatri Valued Member Points: 55 More actions · Answer 5

Hi Pablo...

I sent email to your gmail but it bounced back..

pcecereatgmaildotcom SSChasing Mays Points: 648 More actions · Answer 6

pcecereatgmaildotcom

SSChasing Mays

Points: 648

May 22, 2008 at 1:17 pm

#819335

pcecere is my user. did you send it right>???

abad79 Valued Member Points: 59 More actions · Answer 7

Hi i have the same issue in my company.

Can you help me with the source code of your solution to import to SQL 2005.

Thanks a lot for all your help

Best Regards

Carlos Jaramillo

omarovigh SSC Rookie Points: 29 More actions · Answer 8

HI I AM DAHAR AND I AM WORKING WITH SNDLIST SO I NEED YOUR HELP SINCE YOU HAVE DONE ALREADY THANKS FOR YOUR HELP I NEED THE INFORMATION BECAUSE I NEED TO EXPORT TO A TABLE.

SO IF YOU CAN HELP ME I WILL BE HAPPY.

THANKS

pcecereatgmaildotcom SSChasing Mays Points: 648 More actions · Answer 9

sorry, i just saw your message.

my email is pcecereatgmail

send me a message, pls.

Pablo

jigneshp SSC Journeyman Points: 75 More actions · Answer 10

Hello,

I have same requirements for SDN check. If you have already done work and could help me i will appreciate it

Thanks,

Jignesh

pcecereatgmaildotcom SSChasing Mays Points: 648 More actions · Answer 11

I am not working at that place anymore.

I dont have access to that code. Sorry

I can guide you, if you tell me something more about where you are now...

What have you done??

pradeep.pokharel SSC Rookie Points: 31 More actions · Answer 12

hi Don't know if you still have the process, if you have one can you please share it.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 13

This might help;

USE tempdb;

GO

CREATE TABLE dbo.TBL_XML_DOC

( XML_DOC_ID int identity(1,1) primary key clustered not null

,XML_DOCUMENT xml);

GO

INSERT INTO dbo.TBL_XML_DOC(XML_DOCUMENT)

SELECT * FROM OPENROWSET(

BULK 'C:\Download\sdn.xml',

SINGLE_BLOB) AS x;

;WITH XMLNAMESPACES ( DEFAULT 'http://tempuri.org/sdnList.xsd')

SELECT

XD.XML_DOC_ID

,ROW_NUMBER() OVER (PARTITION BY RN.ODE.value('./uid[1]','INT') ORDER BY (SELECT NULL)) AS X_RID

,RN.ODE.value('./uid[1]' ,'INT' ) AS uid

,RN.ODE.value('./lastName[1]' ,'NVARCHAR(256)' ) AS lastName

,RN.ODE.value('./sdnType[1]' ,'NVARCHAR(256)' ) AS sdnType

,PRO.GRAM.value('program[1]' ,'NVARCHAR(256)' ) AS program

,AK.A.value('uid[1]' ,'NVARCHAR(256)' ) AS AKA_uid

,AK.A.value('type[1]' ,'NVARCHAR(256)' ) AS type

,AK.A.value('category[1]' ,'NVARCHAR(256)' ) AS category

,AK.A.value('lastName[1]' ,'NVARCHAR(256)' ) AS lastName

,DOBI.TEM.value('uid[1]' ,'NVARCHAR(256)' ) AS DOB_uid

,DOBI.TEM.value('dateOfBirth[1]' ,'NVARCHAR(256)' ) AS DOB_dateOfBirth

,DOBI.TEM.value('mainEntry[1]' ,'NVARCHAR(256)' ) AS DOB_mainEntry

,PLO.BIRTH.value('uid[1]' ,'NVARCHAR(256)' ) AS POB_uid

,PLO.BIRTH.value('placeOfBirth[1]' ,'NVARCHAR(256)' ) AS POB_placeOfBirth

,PLO.BIRTH.value('mainEntry[1]' ,'NVARCHAR(256)' ) AS POB_mainEntry

--,RN.ODE.query('(.)')

FROM dbo.TBL_XML_DOC XD

OUTER APPLY XML_document.nodes('sdnList/sdnEntry') AS RN(ODE)

OUTER APPLY RN.ODE.nodes('programList') AS PRO(GRAM)

OUTER APPLY RN.ODE.nodes('akaList/aka') AS AK(A)

OUTER APPLY RN.ODE.nodes('dateOfBirthList/dateOfBirthItem') AS DOBI(TEM)

OUTER APPLY RN.ODE.nodes('placeOfBirthList/placeOfBirthItem') AS PLO(BIRTH)

GO

DROP TABLE dbo.TBL_XML_DOC;

😎