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 Thursday, May 22, 2008 1:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:21 AM
Points: 16, Visits: 36
pcecere is my user. did you send it right>???
Post #505427
Posted Monday, September 1, 2008 3:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 16, 2009 2:51 PM
Points: 1, Visits: 5
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
Post #562099
Posted Saturday, November 8, 2008 11:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 9, 2008 1:15 AM
Points: 1, Visits: 4
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
Post #599519
Posted Sunday, November 9, 2008 8:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:21 AM
Points: 16, Visits: 36
sorry, i just saw your message.
my email is pcecereatgmail
send me a message, pls.
Pablo
Post #599629
Posted Thursday, February 5, 2009 7:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 9:53 AM
Points: 1, Visits: 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
Post #651373
Posted Thursday, February 5, 2009 9:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:21 AM
Points: 16, Visits: 36
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??
Post #651394
Posted Friday, March 28, 2014 12:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 9:01 AM
Points: 1, Visits: 74
hi Don't know if you still have the process, if you have one can you please share it.
Post #1556121
Posted Saturday, March 29, 2014 6:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 2,011, Visits: 5,485
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;


Post #1556255
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse