Import multiple XML docs into one SQL table - possible?

[TheWkndr]

I am an 'advanced novice' in SQL and 'complete novice' in XML (but I have a good XML guy helping me on this) - no formal training but a lot of on-the-job learning. I'd like to know if what we want to do is even possible before we get too far down the path.
We have multiple versions of the same XML doc - one for each of our customers. The layout of each doc is identical but they each have differing data. We would like to combine the data from each of these docs into one SQL table - a row for each customer - so it can be accessed by another system.

I have set up the process from the MSDN examples to bring in one document to a table, but cannot find anything on bringing in multiple docs to the same table at once. Is this even possible?

Thank you

 

[bikerboy718]

You can combine the data in a sql table. Make sure that you match the xml names to the column names when sending it to the table.

Normally I have done the xml docs one at a time. However if you combine the xml documents and structure them properly you can get them all the records in at one time.

here is some code for the xml to a table:

declare pxml varchar(400)
select pxml = 	'<ROOT><Customer Customerid = "8838714", FullName = "Mary Collette", Address = "1st main Street, New York NY 10048" /> 
	<Customer Customerid = "8838714", FullName = "Mary Jane", Address = "777 Putnam Ave, Greenwich, CT 06777" /> 
	</ROOT>'
	
EXEC sp_xml_preparedocument @iXML OUTPUT, @pXml

INSERT INTO Customer(customerID, name,address)
SELECT CustomerID, Name, Address
FROM	OPENXML (@iXML, '/Root/Customer', 1)
	WITH 	(CustomerID 	BIGINT
		, name 	VARCHAR(50)
	        , address	VARCHAR(50))

EXEC sp_xml_removedocument @iXML

With Great Power Comes Great Responsibility!!!

Michael

 

[bikerboy718]

I forgot to add this in the code:
DECLARE @iXML INT

With Great Power Comes Great Responsibility!!!

Michael

 

[mrdenny]

You can also bulk insert the xml docs into a table to get the data in to the SQL Server initially.

I do this here for an app that creats an XML doc for each machine on the network.

I would recommend placing the table which will hold all the RAW xml docs into it's own file group and it's own file so that you don't have to worry about problems with fragmentation within the data file.

I do something like this.

CREATE TABLE Table1
(id int IDENTITY(1,1),
xmlDoc xml)
INSERT INTO Table1
(xmlDoc)
SELECT convert(xml, BulkColumn, 2)
FROM OPENROWSET(Bulk 'c:\file.xml', SINGLE_BLOW) a

I use this code within a loop (and as dynamic SQL) so I can load in the files with the filename as a variable.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com