How to Use the SQL XML Data Type

By Allen G. Taylor

The XML type was introduced with SQL:2003. This means that conforming implementations can store and operate on XML-formatted data directly, without first converting it to XML from one of the other SQL data types.

The XML data type, including its subtypes, although intrinsic to any implementation that supports it, acts like a user-defined type (UDT). The subtypes are:

  • XML(DOCUMENT(UNTYPED))

  • XML(DOCUMENT(ANY))

  • XML(DOCUMENT(XMLSCHEMA))

  • XML(CONTENT(UNTYPED))

  • XML(CONTENT(ANY))

  • XML(CONTENT(XMLSCHEMA))

  • XML(SEQUENCE)

The XML type brings SQL and XML into close contact because it enables applications to perform SQL operations on XML content, and XML operations on SQL content. You can include a column of the XML type with columns of any of the other predefined types in a join operation in the WHERE clause of a query.

In true relational database fashion, your DBMS will determine the optimal way to execute the query and then will do it.

When to use the XML type

Whether or not you should store data in XML format depends on what you plan to do with that data. Here are some instances where it makes sense to store data in XML format:

  • When you want to store an entire block of data and retrieve the whole block later.

  • When you want to be able to query the whole XML document. Some implementations have expanded the scope of the EXTRACT operator to enable extracting desired content from an XML document.

  • When you need strong typing of data inside SQL statements. Using the XML type guarantees that data values are valid XML values and not just arbitrary text strings.

  • To ensure compatibility with future, as yet unspecified, storage systems that might not support existing types such as CHARACTER LARGE OBJECT, or CLOB.

  • To take advantage of future optimizations that will support only the XML type.

Here’s an example of how you might use the XML type:

CREATE TABLE CLIENT (
  ClientName  CHAR (30)  NOT NULL,
  Address1   CHAR (30),
  Address2   CHAR (30),
  City    CHAR (25),
  State    CHAR (2),
  PostalCode  CHAR (10),
  Phone    CHAR (13),
  Fax    CHAR (13),
  ContactPerson  CHAR (30),
  Comments   XML(SEQUENCE) ) ;

This SQL statement will store an XML document in the Comments column of the CLIENT table. The resulting document might look something like the following:

<Comments>
<Comment>
<CommentNo>1</CommentNo>
<MessageText>Is VetLab equipped to analyze penguin blood?</MessageText>
<ResponseRequested>Yes</ResponseRequested>
</Comment>
<Comment>
<CommentNo>2</CommentNo>
<MessageText>Thanks for the fast turnaround on the leopard seal sputum sample.</MessageText>
<ResponseRequested>No</ResponseRequested>
</Comment>
</Comments>

When not to use the XML type

Just because the SQL standard allows you to use the XML type doesn’t mean that you always should. In fact, on many occasions, it doesn’t make sense to use the XML type. Most data in relational databases today is better off in its current format than it is in XML format. Here are a couple of examples of when not to use the XML type:

  • When the data breaks down naturally into a relational structure with tables, rows, and columns

  • When you will need to update pieces of the document rather than deal with the document as a whole