Read XML file and insert into SQL Server:

Here is the best way of stroring XML data into SQL Server. Option of storing the file in filestream data type is a nice feature.

Hey forgot to share some information. Last week, I've tried storing the data into the system using file stream data type. Tried a mechanism of storing some text content into the file(Steganography). Unfortunately, I couldn't succeed in my attempt. We couldn't retrieve the text content. Possibly it's a huge drawback with this filestream datatype.



create table XMLData(id int identity(1,1),xmlFileName varchar(100),xml_data xml,ownerdata int,updaterdata int)



select * from XMLData

GO

DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'D:\Privacy1.xml'
EXEC('
INSERT INTO XMLData(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO


SELECT * FROM XMLData

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech