June 10, 2010 at 8:11 am
Hello,
Who can help me with the following
I need to import an large number of XML formatted files into a SQL 2005 database.
These files have the following format
<?xml version="1.0" ?>
- <customerorders>
- <customer>
<number>091225</number>
<name>Smith</name>
<address>Oaklane 123</address>
<city>London</city>
</customer>
- <orders>
<partnr>83483</partnr>
<desc>valve-300-1</desc>
<number>1</number>
<price>635</price>
<partnr>77151</partnr>
<desc>valve-377-0</desc>
<number>1</number>
<price>65</price>
<partnr>75333</partnr>
<desc>jointv-39</desc>
<number>5</number>
<price>155</price>
</orders>
</customerorders>
So in each of these files there is one customer with one or more orders.
How can I import these xml files into a customer table and a orders table?
Help is very much appreciated.
Thans in advance.
John
June 16, 2010 at 8:49 am
John here is a SPR I created that might help:
I can send you the xml files if you can pm me an email address.
IF OBJECT_ID ('AxSp_Process_XML_File','P') IS NOT NULL
DROP PROCEDURE AxSp_Process_XML_File
GO
CREATE PROCEDURE AxSp_Process_XML_File @folderLoc varchar(500)
AS
BEGIN
SET NOCOUNT ON
-- Create a table to hold the customer data
IF OBJECT_ID ('tblCustomer','U') IS NOT NULL
DROP TABLE tblCustomer
CREATE TABLE tblCustomer (
row_ID int IDENTITY(1,1),
CustomerID Varchar(10),
ContactName Varchar(20),
Title Varchar(5)
)
--Create a table to hold import file names
IF OBJECT_ID ('tblImportXMLFiles','U') IS NOT NULL
DROP TABLE tblImportXMLFiles
CREATE TABLE tblImportXMLFiles (
row_ID int IDENTITY(1,1),
FileName varchar(max)
)
-- Get a list of all files to be imported
DECLARE @command varchar(550)
SET @command = 'DIR ' + @folderLoc + '\*.xml /B'
INSERT INTO tblImportXMLFiles (FileName) EXEC XP_CMDSHELL @command
--Update the FileName Field to Include the path to the file
UPDATE tblImportXMLFiles
SET FileName = @folderLoc + '\' + FileName
-- Using a cursor select from the files into the table tblCustomer
DECLARE @filename varchar(200)
DECLARE@sqlstmt nvarchar(200)
DECLARE pop_table_cursor CURSOR
FOR SELECT FileName
FROM tblImportXMLFiles
WHERE FileName IS NOT NULL
ORDER BY row_id
OPEN pop_table_cursor
FETCH pop_table_cursor INTO @filename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Because of multiple files a temporary table is needed to get the xml from the files
-- Using dynamic sql.
DECLARE @results table (result xml)
--Build the Dynamic SQL Statement to get the data from the xml file
SET @sqlstmt= 'SELECT * FROM OPENROWSET ( BULK ''' + @filename + ''', SINGLE_CLOB )AS xmlData'
-- Insert the results of the dynamic SQL Statement into the temporary table variable.
INSERT INTO @results EXEC (@sqlstmt)
DECLARE @xmlDoc XML
SELECT @xmlDoc = result FROM @results
--Create an interger representation of the xml file
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlDoc
-- INSERT THE relational Data into a table
INSERT INTO tblCustomer
SELECT *
FROM OPENXML (@hdoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20),
Title varchar(30))
FETCH pop_table_cursor INTO @filename
END
CLOSE pop_table_cursor
DEALLOCATE pop_table_cursor
--Display contents of Uploaded Files
SELECT * FROM tblCustomer
SET NOCOUNT OFF
END
GO
EXEC AxSp_Process_XML_File 'C:\WIP\XML_Example_Rev2'
MCITP SQL 2005, MCSA SQL 2012
June 17, 2010 at 1:39 am
Dear RTaylor2208,
Thank you very very much for your help, I am going to try to implement your solution.
John
June 17, 2010 at 9:18 am
Emailed you the two test xml files hope fully should give you some ideas.
MCITP SQL 2005, MCSA SQL 2012
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply