• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2349
  • Last Modified:

How to import data from XML to a sql database table???

I'm out of my depth on this one.
I was given the UN Security list, the US security  list and the EU security list.
I need to extract the data out of this xml file and insert it into a database. This process is going to be a monthly thing so I don't want to spend to much time on the issue.The problem is that the structure of the data within the xml files is not uniform accross all records.
if some body can please point me in the right direction so that I can get this data into database. Please explain in laymans terms
http://www.un.org/sc/committees/1267/consolist.shtml
http://www.treas.gov/offices/enforcement/ofac/sdn/sdn.xml
http://ec.europa.eu/external_relations/cfsp/sanctions/list/consol-list.htm

Any Help will be greatly appreciated
Sample one
*********************************
 <INDIVIDUALS>
- <INDIVIDUAL>
  <DATAID>111345</DATAID> 
  <VERSIONNUM>10</VERSIONNUM> 
  <FIRST_NAME>MOUSTAFA</FIRST_NAME> 
  <SECOND_NAME>ABBES</SECOND_NAME> 
  <UN_LIST_TYPE>Al-Qaida</UN_LIST_TYPE> 
  <REFERENCE_NUMBER>QI.A.163.04.</REFERENCE_NUMBER> 
  <LISTED_ON>2004-03-17T00:00:00</LISTED_ON> 
  <NAME_ORIGINAL_SCRIPT>E57AI 9('3</NAME_ORIGINAL_SCRIPT> 
  <COMMENTS1>Sentenced to three years and six months of imprisonment by the Tribunal of Naples on 19 May 2005. Released on 30 Jan. 2006 due to an order suspending the sentence. Returned to Algeria where he resides as at Nov. 2008.</COMMENTS1> 
- <NATIONALITY>
  <VALUE>Algerian</VALUE> 
  </NATIONALITY>
- <LIST_TYPE>
  <VALUE>UN List</VALUE> 
  </LIST_TYPE>
- <LAST_DAY_UPDATED>
  <VALUE>2004-11-26T00:00:00</VALUE> 
  <VALUE>2007-12-21T00:00:00</VALUE> 
  <VALUE>2008-12-02T00:00:00</VALUE> 
  </LAST_DAY_UPDATED>
- <INDIVIDUAL_ALIAS>
  <QUALITY>Good</QUALITY> 
  <ALIAS_NAME>Mostafa Abbes</ALIAS_NAME> 
  </INDIVIDUAL_ALIAS>
- <INDIVIDUAL_ADDRESS>
  <COUNTRY>Algeria</COUNTRY> 
  <NOTE>(as at Nov. 2008)</NOTE> 
  </INDIVIDUAL_ADDRESS>
- <INDIVIDUAL_ADDRESS>
  <STREET>Via Padova</STREET> 
  <CITY>82- Milan</CITY> 
  <COUNTRY>Italy</COUNTRY> 
  <NOTE>(previous address as at Mar. 2004)</NOTE> 
  </INDIVIDUAL_ADDRESS>
- <INDIVIDUAL_DATE_OF_BIRTH>
  <TYPE_OF_DATE>Exact</TYPE_OF_DATE> 
  <DATE>1962-02-05T00:00:00</DATE> 
  </INDIVIDUAL_DATE_OF_BIRTH>
- <INDIVIDUAL_PLACE_OF_BIRTH>
  <CITY>Osniers</CITY> 
  <COUNTRY>Algeria</COUNTRY> 
  </INDIVIDUAL_PLACE_OF_BIRTH>
  <INDIVIDUAL_DOCUMENT /> 
  <SORT_KEY>ABBES MOUSTAFA</SORT_KEY> 
  <SORT_KEY_LAST_MOD>2008-12-02T00:00:00</SORT_KEY_LAST_MOD> 
  </INDIVIDUAL>
 
sample two
*************************************
<?xml version="1.0" standalone="yes" ?> 
- <sdnList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://tempuri.org/sdnList.xsd">
- <publshInformation>
  <Publish_Date>02/11/2009</Publish_Date> 
  <Record_Count>4193</Record_Count> 
  </publshInformation>
- <sdnEntry>
  <uid>737</uid> 
  <lastName>EMPRESA CUBANA DE AVIACION</lastName> 
  <sdnType>Entity</sdnType> 
- <programList>
  <program>CUBA</program> 
  </programList>
- <akaList>
- <aka>
  <uid>585</uid> 
  <type>a.k.a.</type> 
  <category>strong</category> 
  <lastName>CUBANA AIRLINES</lastName> 
  </aka>
  </akaList>
- <addressList>
- <address>
  <uid>440</uid> 
  <address1>Belas Airport</address1> 
  <city>Luanda</city> 
  <country>Angola</country> 
  </address>
  </addressList>
  </sdnEntry>
- <sdnEntry>
  <uid>738</uid> 
  <lastName>EMPRESA CUBANA DE AVIACION</lastName> 
  <sdnType>Entity</sdnType> 
- <programList>
  <program>CUBA</program> 
  </programList>
- <akaList>
- <aka>
  <uid>586</uid> 
  <type>a.k.a.</type> 
  <category>strong</category> 
  <lastName>CUBANA AIRLINES</lastName> 
  </aka>
  </akaList>
- <addressList>
- <address>
  <uid>441</uid> 
  <address1>Dobrininskaya No. 7, Sec 5</address1> 
  <city>Moscow</city> 
  <country>Russia</country> 
  </address>
  </addressList>
  </sdnEntry>
- <sdnEntry>
  <uid>739</uid> 
  <lastName>EMPRESA CUBANA DE AVIACION</lastName> 
  <sdnType>Entity</sdnType> 
- <programList>
  <program>CUBA</program> 
  </programList>
- <akaList>
- <aka>
  <uid>587</uid> 
  <type>a.k.a.</type> 
  <category>strong</category> 
  <lastName>CUBANA AIRLINES</lastName> 
  </aka>
  </akaList>
- <addressList>
- <address>
  <uid>442</uid> 
  <address1>Corrientes 545 Primer Piso</address1> 
  <city>Buenos Aires</city> 
  <country>Argentina</country> 
  </address>
  </addressList>
  </sdnEntry>
- <sdnEntry>
  <uid>740</uid> 
  <lastName>EMPRESA CUBANA DE AVIACION</lastName> 
  <sdnType>Entity</sdnType> 
- <programList>
  <program>CUBA</program> 
  </programList>
- <akaList>
- <aka>
  <uid>588</uid> 
  <type>a.k.a.</type> 
  <category>strong</category> 
  <lastName>CUBANA AIRLINES</lastName> 
  </aka>
  </akaList>
- <addressList>
- <address>
  <uid>443</uid> 
  <address1>Frankfurter TOR 8-A</address1> 
  <city>Berlin</city> 
  <country>Germany</country> 
  </address>
  </addressList>
  </sdnEntry>
- <sdnEntry>
  <uid>741</uid> 
  <lastName>EMPRESA CUBANA DE AVIACION</lastName> 
  <sdnType>Entity</sdnType> 
- <programList>
  <program>CUBA</program> 
  </programList>
- <akaList>
- <aka>
  <uid>589</uid> 
  <type>a.k.a.</type> 
  <category>strong</category> 
  <lastName>CUBANA AIRLINES</lastName> 
  </aka>
  </akaList>
- <addressList>
- <address>
  <uid>444</uid> 
  <address1>1 Place Ville Marie, Suite 3431</address1> 
  <city>Montreal</city> 
  <country>Canada</country> 
  </address>
  </addressList>
  </sdnEntry>
- <sdnEntry>
  <uid>742</uid> 
  <lastName>EMPRESA CUBANA DE AVIACION</lastName> 
  <sdnType>Entity</sdnType> 
- <programList>
  <program>CUBA</program> 
  </programList>
- <akaList>
- <aka>
  <uid>590</uid> 
  <type>a.k.a.</type> 
  <category>strong</category> 
  <lastName>CUBANA AIRLINES</lastName> 
  </aka>
  </akaList>
- <addressList>
- <address>
  <uid>445</uid> 
  <address1>Parizska 17</address1> 
  <city>Prague</city> 
  <country>Czech Republic</country> 
  </address>
  </addressList>
  </sdnEntry>
- <sdnEntry>
  <uid>743</uid> 
  <lastName>EMPRESA CUBANA DE AVIACION</lastName> 
  <sdnType>Entity</sdnType> 
- <programList>
  <program>CUBA</program> 
  </programList>
- <akaList>
- <aka>
  <uid>591</uid> 
  <type>a.k.a.</type> 
  <category>strong</category> 
  <lastName>CUBANA AIRLINES</lastName> 
  </aka>
  </akaList>
- <addressList>
- <address>
  <uid>446</uid> 
  <address1>Paseo de la Republica 126</address1> 
  <city>Lima</city> 
  <country>Peru</country> 
  </address>
  </addressList>
  </sdnEntry>
- <sdnEntry>
  <uid>744</uid> 
  <lastName>EMPRESA CUBANA DE AVIACION</lastName> 
  <sdnType>Entity</sdnType> 
- <programList>
  <program>CUBA</program> 
  </programList>
- <akaList>
- <aka>
  <uid>592</uid> 
  <type>a.k.a.</type> 
  <category>strong</category> 
  <lastName>CUBANA AIRLINES</lastName> 
  </aka>
  </akaList>
- <addressList>
- <address>
  <uid>447</uid> 
  <address1>Piarco Airport</address1> 
  <city>Port au Prince</city> 
  <country>Haiti</country> 
  </address>
  </addressList>
  </sdnEntry>
- <sdnEntry>
  <uid>745</uid> 
  <lastName>EMPRESA CUBANA DE AVIACION</lastName> 
  <sdnType>Entity</sdnType> 
- <programList>
  <program>CUBA</program> 
  </programList>
- <akaList>
- <aka>
  <uid>593</uid> 
  <type>a.k.a.</type> 
  <category>strong</category> 
  <lastName>CUBANA AIRLINES</lastName> 
  </aka>
  </akaList>
- <addressList>
- <address>
  <uid>448</uid> 
  <address1>c/o Anglo-Caribbean Shipping Co. Ltd.</address1> 
  <address2>Ibex House, The Minories</address2> 
  <city>London</city> 
  <postalCode>EC3N 1DY</postalCode> 
  <country>United Kingdom</country> 
  </address>
  </addressList>
  </sdnEntry>

Open in new window

0
ablsysadmin
Asked:
ablsysadmin
  • 3
  • 3
  • 2
1 Solution
 
brad2575Commented:
Have you tried the wizard to import the data?  You can use the wizard to import the data then create package that you can re-use every month.
0
 
ablsysadminAuthor Commented:
What Data source would you use?
0
 
brad2575Commented:
SQLXMLODB
or
SQLXMLODB4.0
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ablsysadminAuthor Commented:
Yet another simple question how does SQLXMLOLEDB work?
0
 
brad2575Commented:
That I do not know.

But I found this on Microsoft's site:

http://msdn.microsoft.com/en-us/library/ms172675.aspx
0
 
Anthony PerkinsCommented:
Have you looked at OPENXML?  I am assuming you are still using SQL Server 2000.
0
 
ablsysadminAuthor Commented:
The est solution was to buy Novixsys software to strip and break up the xml data into a usable format
0
 
Anthony PerkinsCommented:
In the interest of enhancing the knowledgebase the correct name is Novixys Software, Inc. (http://www.novixys.com/)  They put out a software called Exult Professional Edition for SQL Server which is useful for the novice users who are not very familiar with the XML support natively provided by MS SQL Server.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now