DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

XML File To SQL

06.18.2007
| 2626 views |
  • submit to reddit
        // XML file to SQL

CREATE TABLE XmlImportTest ( xmlFileName VARCHAR(300), xml_data xml ) 
GO 
DECLARE @xmlFileName VARCHAR(300) 
SELECT @xmlFileName = 'c:\TestXml.xml' 
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET 
EXEC(' INSERT INTO XmlImportTest(xmlFileName, xml_data) SELECT ''' + @xmlFileName + ''', xmlData FROM ( SELECT * FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA ) AS FileImport (XMLDATA) ') 
GO 
SELECT * FROM XmlImportTest 
DROP TABLE XmlImportTest