How to Insert a XML Data into Sql Table Using OPENXML SQL SERVER
Introduction:
In this article, i am going to explain about how to insert a data into sql table using OpenXml/Sql Server.
Main:
OPENXML provides a rowset view over an XML document.OPENXML allows you to read xml strings,
syntax for OPENXML,
OPENXML(idoc int[input],rowpattern varchar[input],[flags byte[input])
WITH (SchemaDeclaration | TableName)]
DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='<ROOT><EMP>
<id>1</id>
<name>James</name>
<age>32</age>
</EMP>
<EMP>
<id>2</id>
<name>Chris</name>
<age>42</age>
</EMP>
<EMP>
<id>3</id>
<name>Peter</name>
<age>23</age>
</EMP>
<EMP>
<id>4</id>
<name>Andrea</name>
<age>12</age>
</EMP>
<EMP>
<id>5</id>
<name>Christopher</name>
<age>75</age>
</EMP>
</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 EMP_Details SELECT id, name, age from
OPENXML (@idoc, '/ROOT/EMP',2)
WITH (id int, name varchar(50), age int)
Select * from EMP_Details
OPENXML(idoc int[input],rowpattern varchar[input],[flags byte[input]) WITH (SchemaDeclaration | TableName)] DECLARE @idoc int DECLARE @doc varchar(8000) SET @doc ='<ROOT><EMP> <id>1</id> <name>James</name> <age>32</age> </EMP> <EMP> <id>2</id> <name>Chris</name> <age>42</age> </EMP> <EMP> <id>3</id> <name>Peter</name> <age>23</age> </EMP> <EMP> <id>4</id> <name>Andrea</name> <age>12</age> </EMP> <EMP> <id>5</id> <name>Christopher</name> <age>75</age> </EMP> </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 EMP_Details SELECT id, name, age from OPENXML (@idoc, '/ROOT/EMP',2) WITH (id int, name varchar(50), age int) Select * from EMP_Details |
Conclusion:
Hope this helps,
Happy Coding.
Your web site is very fascinating,I would like to connect with u,could i sent electronic mail to you?