Insert XML data into the SQL Server database table

Source: Internet
Author: User
Tags sql server query
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:

 
 
  1. 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:

 
 
  1. 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:

 
 
  1. OPENXML(handleddoc,rowpattern,flagvalue)
  2. 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:

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

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

 
 
  1. USE school
  2. 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.