Skip to the content

Hoe importeer ik een XML-file of bestand in SQL Server

Het importeren van XML in SQL Server is niet rechtstreeks mogelijk en dat is ook logisch omdat XML nu eenmaal een ander soort manier van dataopslag is dan de SQL Server.

Om XML te kunnen importeren in de tabellen en velden van een SQL Server database moeten we de XML parsen wat inhoudt dat we het gaan zien als een tekstbestand en vervolgens de data op gaan knippen in stukjes die we wel kunnen verwerken.

Het onderstaande voorbeeld is uitstekend geschikt om bestanden die regelmatig worden geplaatst op een vaste locatie met een vaste naam automatisch te importeren middels een taak. Zelf gebruik ik deze code om bijvoorbeeld data uit financiële software pakketten te importeren in RE-IN-VENT.

--tijdelijke tabel verwijderen
IF OBJECT_ID(N'tempdb..#XmlImportTest', N'U') IS NOT NULL 
DROP TABLE #XmlImportTest;

IF OBJECT_ID(N'tempdb..#XmlTarget', N'U') IS NOT NULL 
DROP TABLE #XmlTarget;
			
/*In deze tabel slaan we de ruwe xml data op*/
CREATE TABLE #XmlImportTest
(
	xmlFileName VARCHAR(300),
	xml_data_raw VARCHAR(MAX),
	xml_data xml
)
/*In deze tijdelijke tabel slaan we in dit blog de output op*/
/*Dit kan ook direct de tabel zijn in uw database*/
CREATE TABLE #XmlTarget
(
	[Id] INT,
	[Name] VARCHAR(80) NULL,
	[StreetName] VARCHAR(80) NULL,
	[ZippCode] VARCHAR(16) NULL,
	[Residence] VARCHAR(80) NULL,
	[Function] VARCHAR(80) NULL
)
/*Wat is het pad inclusief de bestandsnaam waar het xml bestand is opgeslagen*/
DECLARE @xmlFileName VARCHAR(300)
SELECT  @xmlFileName = 'D:\Development\Blog\Blog20150919.xml'

/*Importeer nu het xml bestand in de tabel #XmlImportTest als text*/
EXEC('
INSERT INTO #XmlImportTest(xmlFileName, xml_data_raw)
SELECT ''' + @xmlFileName + ''', xmlData FROM 
	(
		SELECT CONVERT(VARCHAR(MAX), BulkColumn)  
		FROM  OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
	) AS XmlTarget (XMLDATA)')

/*Nu zetten we de tekst om naar xml en daarvoor gebruiken we de CONVERT functie*/
UPDATE #XmlImportTest SET xml_data = CONVERT(XML,xml_data_raw)

/*Nu kunnen we de data uit de xml verwerken naar de doeltabel*/
/*Atributen voorzien van een @*/
INSERT INTO #XmlTarget([Id],[Name],[StreetName], [ZippCode], [Residence], [Function])
SELECT 	
tab.col.value('./@Id[1]',		'INT')			AS'ID',	
tab.col.value('./Name[1]',		'varchar(80)')	AS'Name',
tab.col.value('./Address[1]',	'varchar(80)')	AS'Addrress',
tab.col.value('./ZipCode[1]',	'varchar(16)')	AS'ZipCode',
tab.col.value('./Residence[1]',	'varchar(80)')	AS'Residence',
tab.col.value('./@Function[1]',	'varchar(80)')	AS'FunctionName'
FROM [#XmlImportTest] 
CROSS APPLY
xml_data.nodes('//Employee')AS tab(col)

/*Het resultaat in de tabel*/
SELECT * FROM #XmlTarget

De opgeslagen procedure uitgelegd

In het voorbeeld maak ik gebruik van tijdelijke tabellen. In je eigen versie kun je de tijdelijke tabellen vervangen voor vaste tabellen of een combinatie van beide.

Je kunt de voorbeeld XML hier bekijken waarbij je in de code de verwijzing aan dient te passen naar de locatie op je eigen computer.

<?xml version="1.0" standalone="yes"?>
<Employees>
	<Employee Id="1" Function="Programmeur">
		<Name>Reinder Stolte</Name>
		<Address>Zeilmakersstraat 31J</Address>
		<ZipCode>8601WT</ZipCode>
		<Residence>Sneek</Residence>
		<TimeSheet>
			<StartTime>2015-09-19 08:00</StartTime>
			<EndTime>2015-09-19 17:00</EndTime>
		</TimeSheet>
	</Employee>
	<Employee Id="2" Function="Programmeur">
		<Name>Wouter van Ackooij</Name>
		<Address>Zeilmakersstraat 31J</Address>
		<ZipCode>8601WT</ZipCode>
		<Residence>Sneek</Residence>
		<TimeSheet>
			<StartTime>2015-09-19 08:00</StartTime>
			<EndTime>2015-09-19 17:00</EndTime>
		</TimeSheet>
	</Employee>
</Employees>

SELECT @xmlFileName = 'D:\Development\Blog\Blog20150919.xml'

De xml elementen en attributen

Ieder niveau van de elementen wordt voorafgegaan door een slash teken. In ons voorbeeld zijn we op zoek naar de data in de node <Employee> wat het tweede niveau is in de XML-file, immers <Employee> is kind van het element <Employees>. Om de reden plaatsen we in het voorbeeld twee slash tekens voor de elementnaam.

xml_data.nodes('//Employee')AS tab(col)

Een geneste tabel

Voor het ophalen van elementen uit de geselecteerde tabel geldt dezelfde regel als hierboven. Stel in ons voorbeeld is een geneste tabel Timesheet opgenomen in de Medewerker tabel <Employee> met daarin de starttijd en de eindtijd. In dat geval neem je het element <TimeSheet> op in het select statement.

tab.col.value('./TimeSheet[1]/StartTime[1]', 'varchar(80)') AS'Starttijd',

tab.col.value('./TimeSheet[1]/EndTime[1]', 'varchar(80)') AS'Eindtijd'