Manage XML Data

This objective may include, but is not limited to: XML datatype, XML indexes, XML schema collections

When you want to work with XML data in SQL Server, you have the xml data type at hand. This one has some useful functions to query and modify XML data, but more of this later. There are two types of the xml data type, namely untyped and typed. The difference is that you can use an XML schema to enforce rules when working with typed xml.

To create an XML schema in SQL Server, you use the CREATE XML SCHEMA COLLECTION order. This looks as follows:

AS N’<?xml version=”1.0” encoding=”utf-16” ?>
<xsd:schema xmlns:xsd=””>
 <xsd:element name=”Person”>
     <xsd:element name=”FirstName” type=”xsd:string” />
     <xsd:element name=”LastName” type=”xsd:string” />

Now you can enforce the validity of any XML you create against this schema by declaring an xml type as follows:
Continue reading


Transform XML data into relational data

May include but is not limited to: OPENXML, sp_xml_preparedocument, sp_xml_removedocument

SQL Server allows you to retrieve XML data as relational data (a process known as shredding) . To implement this behavior, you’d need three methods, mentioned in the “May include but is not limited to” section, but for the sake of Google and SEO: OPENXML for the SELECT clause, the sp_xml_preparedocument  system stored procedure to prepare your documents, and the sp_xml_removedocument system stored procedure to remove the XML document from the server memory. This is essential, because your loaded XML documents will use server resources as long as the server won’t restarts, and you can easily run out of memory.

The syntax of sp_xml_preparedocument is somewhat easy. It accepts three values, and integer handle, which is an OUTPUT value, an nvarchar(max) which is the string representation of your XML document, and an optional flags parameter, which is very good, and certainly covered in SQL Server Books Online in great detail.

A simple example of using sp_xml_preparedocument:
Continue reading

Retrieve relational data as XML

May include but is not limited to: FOR XML

SQL Server has support for the XML data type for quite long time. I’m not a big fan of XML, but you are unable to evade it, especially when you work in the web business, so let’s get into it.

The question is why you’d need relational data in an XML format. One good answer (among thousand possible candidates) is interoperability. XML is well understood, and is everywhere, so having data in this format definitely makes sense.

This objective covers the FOR XML clause, which you can append to your SELECT clauses to retrieve and control the format of your data. FOR XML has the following options:

  • RAW: each row of the data is represented as a single node, and the columns as attributes. However, you can fine-tune this behavior.
  • AUTO: similar to RAW, but default node name for each row is the name of the table (and not the generic “row”).
  • PATH: enables you to format your result with XPath specified as the column alias.
  • EXPLICIT: provides the finest grain of control, you can define the layout of your XML document in a syntax similar to XPath. The notation is: ColumnName AS [ElementName!TagNumber!AttributeName!Directive].

Continue reading