Home Forums Programming XML Importing the OFAC SDN list via XML RE: Importing the OFAC SDN list via XML

  • 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;

    😎