Insert XML data into the SQL Server database table

6.3 insert XML data into the SQL Server database table. We can use the OPENXML FUNCTION IN THE SYSTEM Stored Procedure sp_xml_preparedocument to insert the data in the XML document to the database. The system stored procedure sp_xml_preparedocument is used to create an internal representation of the XML document that can be inserted into the database. This stored procedure returns

6.3 insert XML data into the SQL Server database table. We can use the OPENXML function of sp_xml_preparedocument IN THE SYSTEM storage process to insert the data in the XML document into the database. The system stored procedure sp_xml_preparedocument is used to create an internal representation of the XML document that can be inserted into the database. This stored procedure returns

6.3 insert XML data into the SQL Server database table

We can use the OPENXML function of sp_xml_preparedocument to insert data in the XML document to the database. The system stored procedure sp_xml_preparedocument is used to create an internal representation of the XML document that can be inserted into the database. This stored procedure returns a handle that can access the internal representation of the XML document. On the other hand, the system stored procedure sp_xml_removedocument can be used to delete the internal representation of an XML document.

The syntax of sp_xml_preparedocument is as follows:

 
 

  
  
sp_xml_preparedocument handleddoc OUTPUT，xmltext 
 
 

Where:

● Handleddoc represents the integer of the XML document handle.

● Xmltext indicates the text value of the original XML document.

The syntax of sp_xml_removedocument is as follows:

 
 

  
  
sp_xml_removedocument handleddoc 
 
 

Where:

Handleddoc represents the integer of the XML document handle.

The above two stored procedures can use the function OPENXML. the syntax of the function OPENXML is as follows:

 
 

  
  
OPENXML(handleddoc，rowpattern，flagvalue) 
  
  
With tablename 
 
 

Where:

● Handleddoc represents the integer of the XML document handle.

● Rowpattern indicates the variable length string value used to identify the node XPath mode of the XML document.

● Flagvalue indicates the integer mapped between XML data and related row sets. If the value is 1, it indicates that you want to map fields in the Database Based on attributes. If the value is 2, it indicates that you want to map fields in the database based on elements.

● Tablename indicates the table name in the database.

The system stored procedure sp_xml_preparedocument reads the text in the XML document and processes it with the MSXML parser. After processing, XML documents are displayed in a tree structure with elements, attributes, and text. The OPENXML function applies the tree structure and generates a row set that contains all parts of the XML document. Using OPENXML and INSERT statements, you can INSERT data in a row set to a table. The following is an example.

Instance 6-1 inserts XML data into the SQL Server database table in the form of attributes

(1) enter the following code in the SQL Server Query analyzer window:

 
 

  
  
USE school 
  
  
LECT * FROM student 
 
 

After running, check the data in the student table before insertion, as shown in 6-1.

(2) re-enter the following code:

 
 

  
  
1 USE school 
  
  
2 DECLARE @doc varchar(1000) 
  
  
3 DECLARE @idoc int 
  
  
4 SET @doc='  
  
  
5 
  
  
6 sex="female" age="21"> 
  
  
7 
  
  
8 ' 
  
  
9 exec sp_xml_preparedocument @idoc output，@doc 
  
  
10 select * from openxml(@idoc，'/ROOT/student'，1) 
  
  
11 with(id int，name varchar(40)，sex varchar(20)， 
  
  
12 age int) 
  
  
13 insert student 
  
  
14 select * from openxml(@idoc，'/ROOT/student') 
  
  
15 with student 
  
  
16 exec sp_xml_removedocument @idoc 
 
 

In the Code 4th ~ In line 8, a variable @ doc is created and XML data is stored in the variable. Then, Row 3 executes the system stored procedure sp_xml_preparedocument to create an internal representation of the XML document that can insert data into the database. The system stored procedure returns a handle stored in the variable @ idoc, you can use this handle to access the internal representation of the XML document. Row 10th uses a select statement to access the internal representation of the XML document and display all the data in the document.

(3) run the program. The result is 6-2.

(4) re-enter the following code.

 
 

  
  
USE school 
  
  
SELECT * FROM student 
 
 

After running, view the data in the inserted student table, as shown in 6-3.

-3 clearly shows that the XML data has been inserted into the database.

Instance 6-2 inserts XML data into the SQL Server database table in the form of Elements

Next, we continue to insert data in the previous instance, but this time we insert data in the form of elements.

Enter the following code in the query window:

 
 

  
  
1 DECLARE @doc varchar(1000) 
  
  
2 DECLARE @idoc int 
  
  
3 SET @doc='  
  
  
4  
  
  
5  6  
  
  
6  sun  
  
  
7  male  
  
  
8 24 
  
  
9 
  
  
10 ' 
  
  
11 exec sp_xml_preparedocument @idoc output，@doc 
  
  
12 select * from openxml(@idoc，'/ROOT/student',2) 
  
  
13 with(id int，name varchar(40)，sex varchar(20)， 
  
  
14 age int) 
  
  
15 insert student 
  
  
16 select * from openxml(@idoc，'/ROOT/student'，2) 
  
  
17 with student 
  
  
18 exec sp_xml_removedocument @idoc 
 
 

In the Code 3rd ~ In row 10, a variable @ doc is created and XML data is stored in the variable. Then, Row 3 executes the system stored procedure sp_xml_preparedocument to create an internal representation of the XML document that can insert data into the database. The system stored procedure returns a handle stored in the variable @ idoc, you can use this handle to access the internal representation of the XML document. Row 12th uses a select statement to access the internal representation of the XML document and displays all the data in the document. The only difference between the code in this section and the code in instance 6-1 is that the XML data stored in the variable appears in the form of elements rather than attributes.

Run the program, as shown in result 6-4.

Enter the following code again:

 
 

  
  
USE school 
  
  
SELECT * FROM student 
 
 

After running, view the data in the inserted student table, as shown in 6-5.

We can clearly see that the XML data has been inserted into the database.

Through the above example, we can see that in SQL Server 2005, The OPENXML function is enhanced, it can pass XML data to sp_xml_preparedocument stored procedure, the new data type can be used in the WITH clause.