MS SQL Basics Tutorial: XML documents and Database tables

SQL Server series versions, including SQL Server 7.0, do not provide XML. Support developers have previously had to use an XML parser, such as Microsoft's XML Parser (MSXML), and they must write their own code to handle the details: Extract the different elements from the XML document and put them into different parts of the relational table as needed, and then access the relational table or write code to extract the data from a database table and put it back in the XML document in the correct format. As we enjoy the benefits of XML, we often find ourselves having to cope with this work when developing Web applications and still have to rewrite the code when developing different Web applications. So what developers really need is a generic way of working with XML in a database, and that's what you can do with SQL Server 2000, which is to store XML documents in relational data tables and generate XML documents from relational data.

20.3.1 produces XML documents from relational data

In SQL Server 2000, query results can be stored as XML documents rather than as a common result set by using a SELECT statement, which enables Web applications or other applications to use XML documents directly, thereby facilitating business communication between the enterprise and the vendor.

The query results for the relational database are displayed as XML documents through the FOR XML statement. You can specify mode (RAW, AUTO, EXPLICIT) and XMLData schema (schema) for XML while using a FOR XML statement. The syntax rules for the FOR XML statement are:

For XML mode [, XMLDATA] [, elements][, BINARY BASE64]

The meaning of each parameter is described as follows: mode

Represents the XML mode that returns a result set of three values, namely Raw, AUTO, EXPLICIT. XMLDATA

Indicates that the XMLDATA mode will be returned. ELEMENTS

When this option is used, the corresponding relational table column is represented as a child of the XML document, otherwise it will match the XML attribute. You can use this option only if you are elected with auto. BINARY BASE64

Indicates that the binary data returned by the query is displayed as a 64-bit encoded form. 1 RAW Mode

When you use raw mode, each row in the query result set serves as an element of the XML document, and the Non-empty column is the property of the corresponding XML document element, and its property name is consistent with the column name.

2 AUTO Mode

The query result set is used as a nested XML element when using Auto mode. Each table name in the FROM clause acts as an XML element, and the columns in the SELECT statement are the attributes of the XML document element. If you use the elements option, the columns in the SELECT statement are child elements of the XML document element.

3 EXPLICIT Mode

When using the explicit mode, the query replication controls the format of the XML document formed by the result set returned by the query, and the nesting level of the elements. and attributes must also ensure that the XML document is well-formed (wellformed) and has an effective xml-data pattern.

20.3.2 Store XML documents in relational database tables

In an enterprise that implements the E-business model of the consumer (business to customer), customers often pay cash directly on a Web page to order products that are not stored directly in the database, but are based on security and are considered highly efficient for data conversion under unified standards, and then saved in an XML document and then deposited in the database. This requires extracting the data information from the XML document and depositing it in the relational database. The OPENXML statement in SQL Server 2000 provides the ability to store XML documents in relational database tables.

OPENXML is a rowset provider that is very similar to tables and views. Extracting XML document data by OPENXML is as simple and efficient as reading relational row result set data. You must first call the sp_xml_preparedocument system procedure before using OPENXML, whose primary role is to parse the XML document and return the file pointer for that document. The file pointer is then passed to OPENXML, which converts the XML document into a result set based on the arguments that came. Finally, the memory space occupied by the XML document is freed by calling the Sp_xml_removedocument system procedure. Refer to Figure 20-2 for its specific implementation process.

See the full set of "MS SQL Basics Tutorials"