How to Import XML into SQL Server 2005 or SQL Server 2008
In previous XML on SQL Server tutorials, sql developers see how to SELECT from XML in SQL Server 2005 or later versions using T-SQL XML commands.
In SQL XML data can be queried with new T-SQL XML query improvements easier than ever.
In this MSSQL XML tutorial, I will try to give T-SQL XML query samples that can be used with dealing sql xml data and import XML to SQL Server.
Now let's ask this question to ourselves as SQL programmers.
What happens if we have some XML data somewhere outside the SQL Server, like an XML file in a file folder?
How can sql developers import XML into SQL Server 2005 or SQL2008 from an XML file or from a flat file in a file folder?
Importing XML into SQL Server 2005 will enable sql programmers to develop t-sql code using imported xml data.
We can use two methods to insert XML into SQL Server 2005 (or SQL Server 2008) using OPENROWSET which are very similar to each other.
Only the second method uses sp_xml_preparedocument, OPENXML and sp_xml_removedocument T-SQL advanced stored procedures and functions.
Let's save the SQL Server 2008 books XML data in an xml file named sql-server-2008-books.xml on c drive.
Later execute the below t-sql script in order to connect to XML file from SQL engine, read XML data into an sql XML variable.
After the XML data is imported into XML variable @xml, we can execute the prior sql xml select statements again to get the list of MS SQL Server 2008 books.
How to Import XML into SQL Server 2005 / SQL Server 2008 using OPENROWSET
This is an easier xml import solution for SQL Server developers using t-sql.
Of course sql developers can use SSIS XML import methods for more advanced solutions.
But if your XML data is not very complex and xml data is not very big in sizes, t-sql OPENROWSET command will be enough for your xml import requirements.
DECLARE @X XML
SELECT
@X = books
FROM OPENROWSET (BULK 'c:\sql-server-2008-books.xml', SINGLE_BLOB) AS Import(books)
Select
[Book Name].value('.','varchar(100)') AS Book
From @X.nodes('/books/category/book') Books([Book Name])
How to Insert XML into SQL Server using sp_xml_preparedocument
And the second T-SQL Select from XML data query sample which can be used for importing XML into SQL Server 2005 or later versions is as follows :
DECLARE @H INT
DECLARE @X XML
SELECT
@X = books
FROM OPENROWSET (BULK 'c:\sql-server-2008-books.xml', SINGLE_BLOB) AS Import(books)
EXEC sp_xml_preparedocument @H OUTPUT, @X
SELECT *
FROM OPENXML( @h, '/books/category/book',1)
WITH (
Book varchar(50) '.'
)
EXEC sp_xml_removedocument @H
The above XML import solution requires to create an xml document handler which is created by sp_xml_preparedocument. After using OPENXML for importing XML into SQL Server, the xml file handler is deleted by sp_xml_removedocument.
Both T-SQL Select scripts will give the same results.
SQL developers or SQL Server database administrators can execute a SELECT ... INTO ... statement or INSERT INTO ... SELECT ... statements to store XML data in SQL tables imported from XML data files.
I tried to show with examples how to load xml into SQL Server 2005 or later versions like MS SQL Server 2008 or SQL Server 2008 R2.
Since with SQL2005, new XML enhancements are introduced to T-SQL developers, sql programmers are enable to work with XML data easier when compared to SQL Server 2000.
I hope SQL programmers will like using these methods in order to migrate or import data from XML file into SQL Server database.
If you want to work with XML data imported into SQL Server database, you can refer to SQL XML tutorial SQL XML Query in SQL Server 2008.