Post reply

Importing the OFAC SDN list via XML

  • May 21, 2008 at 4:21 pm

    #818907

    Hello...

    I've created import routines for SDn list.

    Let me know if you are interested...

    I can help you.

    Divyesh

  • May 21, 2008 at 4:30 pm

    #818911

    i am very interested.

    tell me a little bit more about it

    Thanks

    Pablo

  • May 22, 2008 at 8:43 am

    #819174

    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

  • May 22, 2008 at 9:54 am

    #819223

    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

  • May 22, 2008 at 1:14 pm

    #819333

    Hi Pablo...

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

  • May 22, 2008 at 1:17 pm

    #819335

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

  • September 1, 2008 at 3:45 pm

    #865755

    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

  • November 8, 2008 at 11:33 pm

    #896017

    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

  • November 9, 2008 at 8:54 pm

    #896106

    sorry, i just saw your message.

    my email is pcecereatgmail

    send me a message, pls.

    Pablo

  • February 5, 2009 at 7:26 pm

    #939285

    Hello,

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

    Thanks,

    Jignesh

  • February 5, 2009 at 9:31 pm

    #939298

    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??

  • March 28, 2014 at 12:42 pm

    #1701289

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

  • March 29, 2014 at 6:50 pm

    #1701402

    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;

    😎

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

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