How to use the XML features in SQL Server 2000

Source: Internet
Author: User
Tags join

In this article, we will discuss how to return XML from SQL Server through a-SQL FOR XML clause. This article will introduce several different ways to return XML data and schema information through a few examples, and also introduce ways to transform XML into more satisfying formats. It then discusses OPENXML, as well as ways to join XML documents to database tables and extract XML from datasets using the WriteXml and Getxml methods.

SQL Server 2000 provides some XML functionality for transforming relational rowset XML documents, reading XML documents, and bulk loading data through XML. For example, you can pass an XML document to a stored procedure, link the XML to some tables, return a rowset, or even modify the data in the database. The expanding functionality of XML in today's enterprise systems facilitates the introduction of OPENXML functions and for XML statements. Some of these features not only support XML, but also improve performance when loading data in bulk.

In this article, we will discuss how to return XML from SQL Server through a-SQL FOR XML clause. This article will introduce several different ways to return XML data and schema information through a few examples, and also introduce ways to transform XML into more satisfying formats. It then discusses OPENXML, as well as ways to join XML documents to database tables and extract XML from datasets using the WriteXml and Getxml methods. These examples of SQL, as well as sample asp.net projects that perform some of these examples and export them as text files, can be downloaded from the MSDN Magazine Web site. The sample project also contains code for inserting and updating records from XML to the database.

return XML

When used in a SELECT statement, the FOR XML clause instructs SQL Server to return the data as XML, as opposed to a standard rowset. You can specify the return mode: RAW, auto, or explicit. Each pattern provides different ways of converting XML (Figure 1 gives an overview of the various patterns).

Figure 1 FOR XML Schema overview

Mode Description
RAW Each record in a rowset is converted to an XML element called a row. The <row> element will contain an attribute that represents the column being retrieved.
AUTO The rowset record can be converted to a nested XML element named after the table in the FROM clause. Each column retrieved will be represented as a property
EXPLICIT Provides many controls for formatting XML. However, the syntax of the explicit mode is much more complicated. XSLT is a more common method of XML conversion.

For example, if you use for XML raw to query the Employees table of the Northwind database, it returns each employee row in the <row> element. Each column contained in a SELECT statement is represented as an attribute of the <row> element. The following for XML Raw query selects two employee records, and then returns in RAW format:

SELECT EmployeeID,
FirstName, LastName
FROM Employees
WHERE LastName LIKE 'D%'
FOR XML RAW
<row EmployeeID="1" FirstName="Nancy" LastName="Davolio"/>
<row EmployeeID="9" FirstName="Anne" LastName="Dodsworth"/>

You can use the FOR XML Auto clause to modify the SELECT statement. This time, the element is named employees, which matches the name of the source table. Columns are still properties of the main element:

SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE LastName LIKE 'D%'
FOR XML AUTO
<Employees EmployeeID="1" FirstName="Nancy" LastName="Davolio"/>
<Employees EmployeeID="9" FirstName="Anne" LastName="Dodsworth"/>

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.