September 10, 2008 at 6:03 am
I would like import data from an xml file into a Sql 2005 and insert it in the right format into a table
this is where I got the files from
http://www.un.org/sc/committees/1267/consolist.shtml
I've tried creating an xml table and try to select the data but I get errors?
Whats the best way to import and format this file ? The excel spreadsheet has the desired output that I'm trying to get to
--==============================================================
--
--==============================================================
--==== Import Xml file into sql table
IF OBJECT_ID('tbLoadXml') IS NOT NULL
DROP TABLE tbLoadXml
GO
CREATE TABLE tbLoadXml
(
XmlColXML
)
--INSERT DATA
DECLARE @x XML
SELECT @x = CAST (Bulkcolumn AS XML)
FROM OPENROWSET(BULK 'C:\FILES\consolidatedlist.xml', SINGLE_BLOB) AS x
I get the following error when I run the statement above...
-- insert into table
--INSERT tbUnsecList
SELECT
x.VALUE('FIRST_NAME','VARCHAR(100)') AS FIRSTNAME,
x.VALUE('SECOND_NAME','VARCHAR(100)') AS SURNAME
--==============================================================
--
--==============================================================
--==== Create the table to hold the data
IF OBJECT_ID('tbUnsecList') IS NOT NULL
DROP TABLE tbUnsecList
GO
CREATE TABLE tbUnsecList
(
FIRSTNAMEVARCHAR(100)
,SURNAMEVARCHAR(100)
,BIRTHDATESTARTVARCHAR(100)
,BIRTHDATESTARTVARCHAR(100)
,BIRTHDATEENDVARCHAR(100)
,NATIONALITYVARCHAR(100)
,EXTRACTDATEVARCHAR(100)
,PLACEOFBIRTHVARCHAR(500)
,SECONDNAMEVARCHAR(100)
,THIRDNAME3VARCHAR(100)
,TITLE1VARCHAR(100)
,TITLE2VARCHAR(100)
,DESIGNATIONVARCHAR(150)
,ALIASGOODQUALITYVARCHAR(100)
,ALIASLOWQUALITYVARCHAR(100)
,PASSPORTNOVARCHAR(100)
,IDNUMBERVARCHAR(100)
,ADDRESSVARCHAR(100)
,LISTEDONVARCHAR(100)
)
--==============================================================
--
--==============================================================
--==== Insert Data into final table from the Xml staging table
September 12, 2008 at 3:54 am
I have imported the file into the Xml data type and can view it as well.
How do I turn the imported XML Into A Relational Format and insert into my Sql table ?
--==============================================================
--
--==============================================================
--==== Import Xml file into sql table
IF OBJECT_ID('tbLoadXml') IS NOT NULL
DROP TABLE tbLoadXml
GO
CREATE TABLE tbLoadXml
(
xmlFileName VARCHAR(300) NOT NULL
,xml_data XML NOT NULL
)
GO
--==============================================================
--
--==============================================================
--==== Stage Data into xml table
DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'C:\FILES\consolidatedlist.xml'
---– dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('INSERT INTO tbLoadXml(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO
--SELECT * FROM tbLoadXml
--==============================================================
--
--==============================================================
--==== Create the table to hold the data
IF OBJECT_ID('tbUnsecList') IS NOT NULL
DROP TABLE tbUnsecList
GO
CREATE TABLE tbUnsecList
(
NAME1 VARCHAR(100)
,NAME2 VARCHAR(100)
,NAME3 VARCHAR(100)
,NAME4 VARCHAR(100)
,SURNAMEVARCHAR(100)
,DOBSTARTVARCHAR(100)
,DOBENDVARCHAR(100)
,NATIONALITYVARCHAR(100)
,POB VARCHAR(500)
,TITLE1VARCHAR(100)
,TITLE2VARCHAR(100)
,DESIGNATIONVARCHAR(500)
,ALIASGOODQUALITYAKAVARCHAR(100)
,ALIASLOWQUALITYAKAVARCHAR(100)
,PASSPORTNOVARCHAR(100)
,NATIONALIDENTIFICATIONVARCHAR(100)
,ADDRESSVARCHAR(100)
,LISTEDONVARCHAR(100)
,OTHERINFORMATION VARCHAR(500)
,EXTRACTDATEDATETIME
DEFAULT(GETDATE())
)
--==============================================================
--
--==============================================================
--==== “parse” out the XML file into a relational format & import
--==== into a tbUnsecList the Xml staging table
INSERT tbUnsecList
SELECT .............
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply