Microsoft SQL Server provides the mechanism to save XML data to the SQL table using OpenXML. We can use a stored procedure and pass a XML text as a string. It is useful when we have a large data in XML format and we want to save the whole data in different tables.
So first start declaring the variables. The first declare the document variable and a XML document variable as Varchar(8000) or Varchar(MAX) - It’s a same thing
DECLARE @idoc int DECLARE @doc varchar(8000)
Now we can set the value to the @doc variable, the @doc variable will accept the XML file as a parameter. We can also have a complete XML data in the parameter only if the XML data is less.
OpenXML takes 3 parameters:
- The handler which we have declare at the begining will holds the XML document in the memory.
- XPath variable to access the various elements of the XML document.
- The last parameter here ‘2’ allows us to access XPath as elements.
Here I have read a XML file in the document variable and store in the table. Here is the full SQL query to achieve this. You can also convert the below code to stored procedure and pass XML file or string as a parameter.
DECLARE @idoc int DECLARE @doc varchar(8000) SET @doc ='<ROOT><student> <id>1</id> <name>Prashant</name> <age>32</age> </student> <student> <id>2</id> <name>Swami</name> <age>42</age> </student> <student> <id>3</id> <name>Ash</name> <age>23</age> </student> <student> <id>4</id> <name>Kris</name> <age>12</age> </student> <student> <id>5</id> <name>Derek</name> <age>75</age> </student> </ROOT>' --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- Execute a SELECT statement that uses the OPENXML rowset provider. Insert into Students SELECT id, name, age from OPENXML (@idoc, '/ROOT/student',2) WITH (id int, name varchar(50), age int)