Introduction
With the growing use of XML data, the need for the coexistence of relational data and XML data is also growing. The classic approach of storing XML data as physical disk files is unsuitable and tedious in many situations. No wonder modern database engines are geared to store XML documents right along with the rest of the relational data. To that end, the XML data type introduced in SQL Server 2005 is a great addition to the database engine. Prior to SQL Server 2005, developers often used VARCHAR or TEXT column types to store XML documents and fragments. Although this approach served well as far as data storage is concerned, it proved to be poor in terms of querying and manipulating the XML data. This article will give you a jump start in using the XML data type of SQL server and will teach you how XML data can be manipulated with the help of new XML Data Modification Language (XML DML).
Adding an XML Column to a Table
Before you delve any further, create a table in an SQL Server database that contains a column of type XML. Open SQL Server Management Studio and create a new database. (You also can use any existing database if you so wnt.) Then, create a new table, named EmployeesAsXml, inside the database. The EmployeesAsXml table is supposed to store details of employees in XML format. This table is shown in Figure 1:
Figure 1: Adding a column with XML data type
The table has two columns: Id and EmployeeData. The Id column is an identity column and acts as the primary key column. The EmployeeData column is of type XML. Choosing the data type as XML will allow you to store and retrieve XML documents or fragments in this column.
Storing XML Data in the Table
Now that you have created EmployeesAsXml table with XML column, you can learn to store and modify XML data into it.
Assume that you have an XML fragment, as shown below, that represents details of one employee.
<employee employeeid="1">
<firstname>Nancy</firstname>
<lastname>Davolio</lastname>
</employee>
The employeeid attribute represents a unique ID of an employee. The firstname and lastname tags represent the first name and last name of the employee, respectively.
When performing INSERT or UPDATE operations on the XML data type, you use the same INSERT and UPDATE statements of SQL. The example in Figure 2 will make it clear:
Figure 2: Inserting data in an XML column
Here, you supply the XML fragment to be inserted as a string into the INSERT statement. There also is an alternative. See the query in Figure 3:
Figure 3: Inserting data via an XML variable
In the example shown in Figure 3, you first declare a T-SQL variable of type XML (@xmldata) and then store the XML fragment into it. In the INSERT statement, you then used the XML variable.
The UPDATE on the XML column follows a similar pattern (see Figure 4).
Figure 4: Updating the XML column
IE 7
Firefox 2.0
