Batch Data Writing-Batch writing XML data to the database

There are many requirements for batch writing data to the database, and there are also many methods, which are used in. net Program A better way is to use Bulk copy to copy a large amount of data to the database. If the data source is an XML file, use SQL bulk copy to import the XML data to the datatable. The following method can directly upload the XML data to SQL Server for processing, you can also directly use the T-SQL to import data from XML files to the database.
Here, we mainly use two system stored procedures: sp_xml_preparedocument and sp_xml_removedocument.
Sp_xml_preparedocument: Read the XML file, use the MSXML analyzer (msxmlsql. dll) to analyze the XML data, and provide the analyzed documents for use. The data obtained after analysis is represented in a tree structure for each node (element, attribute, text, comment, etc.) in the XML file. The analyzed data is stored in the internal cache of SQL Server.
Sp_xml_removedocument: releases the memory occupied by the document based on the document handle.
Create an XML string and a table first: <? XML version = "1.0"?>
<Root >
< User ID = "1" Name = "Sbqcel" />
< User ID = "2" Name = "Peaceli" />
< User ID = "3" Name = "Sheepchang" />
</Root >

Create   Table Users
(
Userid Int ,
Username Varchar ( 20 )
)

The solution is simple: Declare   @ Hdoc   Int -- Document handle
Declare   @ Xmlstring   Varchar ( 200 ) -- XML string
Set   @ Xmlstring   = '<? XML version = "1.0"?>
<Root>
<User id = "1" name = "sbqcel"/>
<User id = "2" name = "peaceli"/>
<User id = "3" name = "sheepchang"/>
</Root> '
-- Analyze XML strings using the system stored procedure sp_xml_preparedocument
Exec Sp_xml_preparedocument @ Hdoc Output, @ Xmlstring
-- Use openxml to query data from SQL Server's internal Cache
Insert   Into Users Select   *   From Openxml ( @ Hdoc , N ' /Root/user ' )
With  
(
ID Int ,
Name Varchar ( 10 )
)
-- Use the system stored procedure sp_xml_removedocument to release memory
Exec Sp_xml_removedocument @ Hdoc

If you want to use a T-SQL to directly read an XML file and import the data into the table, you need to do some additional processing: first use xp_cmdshell to read the XML data to a temporary table (variable table ), then combine each row of data into a string and use the above method for processing. Note that xp_mongoshell processes data in rows. The following is a simple example: -- Define a variable table to temporarily store the data obtained after calling xp_cmdshell.
Declare   @ Temp   Table
(
ID Int   Identity ( 1 , 1 ),
Xmlstring Varchar ( 200 )
)
-- Use xp_cmdshell to insert XML file data to the variable table
Insert   Into   @ Temp   Exec Master. DBO. xp_mongoshell ' Type E: \ A. xml '
Declare   @ Loop   Int , -- Cyclic count
@ Count   Int , -- Number of rows
@ Xmlstring   Varchar ( 200 ) -- Store XML strings
Select @ Loop   =   1 , -- Starting from row 1st
@ Xmlstring = '' , -- Initialization
@ Count   = ( Select   Count ( 1 ) From   @ Temp ) -- Obtain the number of rows
-- Create a loop and combine the data into a string for processing
While ( @ Loop <= @ Count )
Begin
Select   @ Xmlstring   =   @ Xmlstring   + Xmlstring From   @ Temp   Where ID =   @ Loop
Set   @ Loop   =   @ Loop   +   1
End
-- View processing results
Select   @ Xmlstring

After the above method is used, the data in an XML file is combined into a string, and then the data can be imported to the database using the above method.

A better way to process XML files is to use OpenRowSet to read data and put it into an XML-type variable., Then process

Code

Declare   @ Hdoc   Int -- Document handle
Declare   @ XML XML
Select   @ XML   = Bulkcolumn From   OpenRowSet ( Bulk   ' E: \ A. xml ' , Single_blob) As   Temp

-- Analyze XML strings using the system stored procedure sp_xml_preparedocument
Exec Sp_xml_preparedocument @ Hdoc Output, @ XML
-- Use openxml to query data from SQL Server's internal Cache
Select   *   From Openxml ( @ Hdoc , N ' /Root/user ' )
With  
(
ID Int ,
Name Varchar ( 10 )
)
-- Use the system stored procedure sp_xml_removedocument to release memory
Exec Sp_xml_removedocument @ Hdoc