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

ablsysadminAsked:
Who is Participating?
 
ablsysadminAuthor Commented:
The est solution was to buy Novixsys software to strip and break up the xml data into a usable format
0
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
brad2575Commented:
SQLXMLODB
or
SQLXMLODB4.0
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.