Insert XML document into SQL Server database (1)

We can use the openxml function and the two system stored procedures sp_xml_preparedocument and sp_xml_removedocument in SQL Server 2000 to map the XML document to the corresponding fields and insert them into the database.

The system stored procedure sp_xml_preparedocument is used to create an internal representation of an XML document that can be inserted into the database, that is, a file number, the system stored procedure returns an internal file number that can be used to access the XML document. sp_xml_removedocument is used to delete the file number of an XML document. it can also be understood that sp_xml_preparedocument is used to allocate memory space for the XML document. The file number is a pointer to the bucket, and the XML file is operated by the file number, sp_xml_removedocument is used to reclaim memory space.

Sp_xml_preparedocument Syntax:
Sp_xml_preparedocument handledoc output, xmltextstring
Handledoc indicates the file number of the XML document to be processed as the output of the stored procedure );
Xmltextstring indicates the XML file to be processed, expressed as a string.

Syntax of sp_xml_removedocument stored procedure:
Sp_xml_removedocument handledoc
Handledoc indicates the file number of the XML document to be deleted

Syntax of openxml functions:
Openxml (handledoc, rowpattern, flagvalue) with tablename/definition structure
Handledoc indicates the file number of the XML document;
Rowpattern indicates a string used to identify XML document validity and node XPath mode. If an XML root node is books, its direct sub-node is book, the value of this string is '/books/Book ';
Flagvalue indicates the ing Relationship Between XML documents and table fields. If the value is 1, The rowpattern attribute is used as the field for ing, if the value is 2, the child element of rowpattern is mapped as a field;
Tablename/definition structure indicates the name of the table to be inserted. It can also be a defined table structure (see the following example ).

Declare @ Doc varchar (1000)
Declare @ IDOC int

 

Set @ Doc = '<root>
<Ticket>
<Passengername> Tom Wilkins </passengername>
<Age> 20 </age>
<Source> Chicago </source>
<Destination> Miami </destination>
<Dateoftravel> 2001-09-10 </dateoftravel>
<Class> first class </class>
<Fare> 200 </fare>
<Status> confirmed </status>
</Ticket>
</Root>'

Exec sp_xml_preparedocument @ IDOC output, @ Doc

Select *
From openxml (@ IDOC, '/root/ticket', 1)
With (passengername varchar (30 ),
Age int,
Source char (20 ),
Destination char (20 ),
Dateoftravel datetime,
Class char (20 ),
Fare money,
Status char (20)
)
Insert ticket

Select *
From openxml (@ IDOC, '/root/ticket ')
With ticket

Exec sp_xml_removedocument @ IDOC