Insert XML data into SQL Server

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
Ebook Download
View all
Learn
View all