Batch xml files into one table in SQL 2005

1

Greetings everyone,

I do not normally work with XML yet but can this be done where you have 100 xml files and you can import the data into one SQL Table? If so, how can I do it? Note ... using SQL 2005. or do I need a later version to do it?

Greatly appreciate your help :wink:

2

couple of ways of ingesting that xml data into sql tables

  1. SSIS
    https://www.mssqltips.com/sqlservertip/3141/importing-xml-documents-using-sql-server-integration-services/
  2. Powershell
  3. TSQL itself
DECLARE @messagebody XML 

SELECT @messagebody = BulkColumn 
FROM OPENROWSET(BULK 'C:\Work\Products1000.XML', SINGLE_CLOB) AS X 

INSERT INTO [dbo].[Products] 
select a.value(N'(./ProductID)[1]', N'int') as [ProductID], 
a.value(N'(./ProductName)[1]', N'nvarchar(40)') as [ProductName], 
a.value(N'(./SupplierID)[1]', N'int') as [SupplierID], 
a.value(N'(./CategoryID)[1]', N'int') as [CategoryID]
from @messagebody.nodes('/Products/row') as r(a);
1 Like