DECLARE	@xml NVARCHAR(4000)

/*******************************************************************************
  Recreate xml staging tables
*******************************************************************************/

CREATE TABLE	#File
		(
			xmlData NTEXT
		)

CREATE TABLE	#Parts
		(
			rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			xmlPart NVARCHAR(4000)
		)

CREATE TABLE	#Data
		(
			rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			Ombudsnummer NVARCHAR(50),
			SkadeAnmNr NVARCHAR(50),
			PersNr NVARCHAR(12),
			RegNr NVARCHAR(15),
			Namn NVARCHAR(80),
			Adress1 NVARCHAR(40),
			Adress2 NVARCHAR(40),
			PostNr NVARCHAR(15),
			Postort NVARCHAR(40),
			Datum NVARCHAR(10),
			Bilmarke NVARCHAR(40),
			Bilmodell NVARCHAR(40)
		)

/*******************************************************************************
  Import XML file
*******************************************************************************/

DECLARE	@cmd NVARCHAR(4000),
	@rc INT

BULK INSERT	#File
FROM		'\\uncpath\Documentation\Logs\theFile.xml'
WITH		(
			FIELDTERMINATOR = '0x00',
			ROWTERMINATOR = '0x00',
			TABLOCK
		)

EXEC	@rc = master.dbo.xp_cmdshell @cmd, NO_OUTPUT

/*******************************************************************************
  Extract XML file parts
*******************************************************************************/

DECLARE	@filpos INT,
	@partpos INT,
	@index INT

SET	@filpos = 1

WHILE @filpos > 0
	BEGIN
		SELECT	@xml = SUBSTRING(xmldata, @filpos, 4000)
		FROM	#File

		SET	@partpos = CHARINDEX('<ROOT>', @xml)

		IF @partpos = 0
			BREAK

		SET	@index = CHARINDEX('</ROOT>', @xml)

		IF @index = 0
			BREAK

		INSERT	#Parts
			(
				xmlPart
			)
		SELECT	SUBSTRING(@xml, @partpos, @index - @partpos + 7)

		SET	@filpos = @filpos + @index + 7
	END

/*******************************************************************************
  Extract XML data
*******************************************************************************/

DECLARE	@rowID INT,
	@hDoc INT

SELECT	@rowID = MAX(rowID)
FROM	#Parts

WHILE @rowID > 0
	BEGIN
		SELECT	@xml = xmlPart
		FROM	#Parts
		WHERE	rowID = @rowID

		EXEC	sp_xml_preparedocument @hDoc OUTPUT, @xml

		INSERT	#Data
			(
				Ombudsnummer,
				SkadeAnmNr,
				PersNr,
				RegNr,
				Namn,
				Adress1,
				Adress2,
				PostNr,
				Postort,
				Datum,
				Bilmarke,
				Bilmodell
			)
		SELECT	OMBUDSNR,
			SKADEANMNR,
			PERSNR,
			REGNR,
			NAMN,
			ADRESS1,
			ADRESS2,
			POSTNR,
			POSTORT,
			DATUM,
			BILMARKE,
			BILMODELL
		FROM	OPENXML(@hDoc, '/ROOT', 2)
		WITH	(
				OMBUDSNR NVARCHAR(50),
				SKADEANMNR NVARCHAR(50),
				PERSNR NVARCHAR(12),
				REGNR NVARCHAR(15),
				NAMN NVARCHAR(80),
				ADRESS1 NVARCHAR(40),
				ADRESS2 NVARCHAR(40),
				POSTNR NVARCHAR(15),
				POSTORT NVARCHAR(40),
				DATUM NVARCHAR(10),
				BILMARKE NVARCHAR(40),
				BILMODELL NVARCHAR(40)
			)

		EXEC	sp_xml_removedocument @hDoc

		SET	@rowID = @rowID - 1
	END

SELECT	*
FROM	#Data