Pages Menu
Categories Menu

Posted by on May 26, 2014 in SQL | 0 comments

Bulk Importing Data From Xml

Data that is stored in XML can be loaded into a SQL Server database with the following approach.

Let’s assume we need to periodically update our web store database with the new broomstick models for the school year at Hogwarts. Our simple table is described below:

CREATE TABLE [dbo].[Products](
	[Id] [nvarchar](128) NOT NULL,
	[Name] [nvarchar](128) NOT NULL,
	[Type] [nvarchar](128) NOT NULL,
	[Cost] [float] NOT NULL)

We are provided with Xml from our suppliers that looks like this:

<?xml version="1.0" encoding="UTF-16" ?>
<Root>
	<Product>
		<Id>1</Id>
		<Name>Nimbus 2000</ Name >
		<Type>Broomstick</Type>
		<Cost>190</ Cost >
	</Product>
	<Product>
		<Id>2</No>
		<Name>Firebolt</ Name >
		<Type>Broomstick</Type>
		<Cost>210</ Cost >
	</Product>
</Root>

We can run a script like the following to import merge the data from our XML to our SQL tableā€¦

DECLARE @TempProduct TABLE
		([Id] [nvarchar](128) NOT NULL,
		[Name] [nvarchar](max) NULL,
      		[Type] [nvarchar](max) NULL,
		[Cost] [float] NULL)

	DECLARE @Input XML
	SELECT @input = CAST(BulkColumn AS XML)
	FROM OPENROWSET (BULK 'C:\Temp\Products.xml', SINGLE_BLOB) AS XMLDATA

	INSERT INTO @TempProduct
		([Id]
      		,[Name]
      		,[Type]
		,[Cost])
		SELECT
      			Products.value('(Id)[1]', 'nvarchar(128)')
			,Products.value('(Name)[1]', 'nvarchar(128)')
			,Products.value('(Type)[1]', 'nvarchar(128)')
			--Convert xml string to float...
			,CASE WHEN ISNUMERIC(Products.value('(Cost)[1]', 'nvarchar(128)') + 'e0') = 1
			    THEN CAST(Products.value('(Cost)[1]', 'nvarchar(128)') AS float)
			    ELSE 0
			END
		FROM @input.nodes('/Root/Product') AS Tbl(Products)

	--Merge our temp table into our real table...
	MERGE [dbo].[Products] AS T
	USING @TempProduct AS S
		ON (T.No = S.No)
		WHEN NOT MATCHED BY TARGET
			THEN INSERT
				([Id]
      				,[Name]
      				,[Type]
				,[Cost])
			VALUES
				(S.[Id]
      				,S[Name]
      				,S.[Type]
				,S.[Cost])
		WHEN MATCHED
			THEN UPDATE SET
				T.[ Id] = S.[ Id]
      				,T.[ Name] = S.[ Name]
      				,T.[ Type] = S.[ Type]
				,T.[ Cost] = S.[ Cost]
		WHEN NOT MATCHED BY SOURCE
			THEN DELETE
	OUTPUT $action, inserted.*, deleted.*;