How to insert bulk data into database?
The bulk data can be inserted by using OPENXML. The following samples shows different ways of reading the xml file using OPENXML and inserting the data.
<subjects>
<subject id="100" category=”tech”>
<name cost=”234”>ASP.NET</name>
</subject>
<subject id="200" category=”tech”>
<name cost=”234”>C#</name>
</subject>
<subject id="300" category=”Science”>
<name cost=”234”>Chemistry</name>
</subject>
</subjects>
The following example shows how to read the attributes at one level. i.e. id and category
DECLARE @XMLFile varchar(255)
DECLARE @i int
SET @XMLFile =
'<subjects>
<subject id="100" category="tech">
<name cost="123">ASP.NET</name>
</subject>
<subject id="200" category="tech">
<name cost="">C#</name>
</subject>
<subject id="200">
<name>Science</name>
</subject>
</subjects>'
EXEC sp_xml_preparedocument @i output, @XMLFile
SELECT *
FROM OPENXML(@i, 'subjects/subject')
WITH (id varchar(500), category varchar(500))
The above example will give you the results as follows:
id category
---------------
100 tech
200 tech
200 NULL
The following example shows how to read the attaributes from two levels. i.e. id, category and cost
DECLARE @XMLFile varchar(255)
DECLARE @i int
SET @XMLFile =
'<subjects>
<subject id="100" category="tech">
<name cost="123">ASP.NET</name>
</subject>
<subject id="200" category="tech">
<name cost="">C#</name>
</subject>
<subject id="200">
<name>Science</name>
</subject>
</subjects>'
EXEC sp_xml_preparedocument @i output, @XMLFile
SELECT *
FROM OPENXML(@i, 'subjects/subject/name', 2)
WITH (id varchar(500) '../@id',
category varchar(500) '../@category',
cost varchar(500) '@cost')
The above example will give you the results as follows:
id category cost
-----------------------
100 tech 123
200 tech
200 NULL NULL
The following shows how to read the attributes and node values:
DECLARE @XMLFile varchar(255)
DECLARE @i int
SET @XMLFile =
'<subjects>
<subject id="100" category="tech">
<name cost="123">ASP.NET</name>
</subject>
<subject id="200" category="tech">
<name cost="">C#</name>
</subject>
<subject id="200">
<name>Science</name>
</subject>
</subjects>'
EXEC sp_xml_preparedocument @i output, @XMLFile
SELECT *
FROM OPENXML(@i, 'subjects/subject', 3)
WITH (id varchar(500), category varchar(500), [name] varchar(500))
The above example will give you the results as follows:
id category name
-----------------------
100 tech ASP.NET
200 tech C#
200 NULL Science
These results can be iserted into a table by just adding insert statement before the select statement as follows:
INSERT INTO <TableName>(comuns names...)
SELECT *
FROM OPENXML(@i, 'subjects/subject', 3)
WITH (id varchar(500), category varchar(500), [name] varchar(500))
No comments:
Post a Comment