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