XML modes in SQL Server

XML stands for Extensible Markup Language. XML is a self-descriptive document, as it contains data and metadata in a hierarchical set of tags.

  • The data in XML can be either in form of attributes and/or elements with proper tags.
  • Data in an element is stored between the beginning and ending of element tags and data in an attribute stored just inside a beginning tag.
  • XML is case sensitive.
  • XML tags can’t have space between them, if so then the space gets replaced with XML junk characters and gets converted to space while shredding back to the tabular format.
  • XML has been differentiated in two parts, Typed XML and Untyped XML.
  • FOR XML statement should be at the end of a SQL statement.

There are 4 XML modes available in SQL Server. I will be discussing one by one.

  1. RAW
  2. AUTO
  3. EXPLICIT
  4. PATH

SAMPLE SCRIPT

XML_STREAMING

RAW:

  • RAW is the simplest XML mode than other modes in SQL Server.
  • Defaults to display data as all attributes in XML document under sub element <row>.

Default:

Output:

Root and Element Name:

Output:

Data as Elements:

Output:

AUTO:

  • AUTO mode is almost similar to RAW mode.
  • Default tag name is table name for each row, and can’t be changed like RAW or PATH mode.
  • AUTO mode can nest the result set by table.

Default:

Output:

 Nested:

  • Nested by column “Genre” from Genre_Master table. The nesting column must be on top of all other columns, so that it can nest with other columns while preparing the xml document.
  • As you can see, the data is been now categorized by “Genre” .

Output:

With Root and Element Name:

Output:

EXPLICIT:

  • EXPLICIT mode is little tricky to use than any other modes.
  • You could notice in below query that, there are two tables united by (UNION ALL) to form or stream the xml document. 1st table is known as the Universal table, which creates the nodes and tags for XML document and the second one is the actual data table. Both tables forms the XML document together
  • If you do specify the element (‘Books!2!author!Element’) then it considers the column as element otherwise it considers the column as an attribute of above element (‘Books!2!id’)
  • For more details about EXPLICIT mode Click here

Output:

PATH:

  • This is a very powerful XML mode.
  • Default to display data as Elements.
  • RAW and AUTO modes are great to display the XML data as all attributes or all elements but can’t be customized with attribute and elements all together in one XML document, wherein PATH and EXPLICIT modes can do that. As we have already seen, how EXPLICIT mode can customize the XML document with attributes and elements all together in one XML.
  • If you do not specify any element name then it shows <row> as upper level element name.

With Root and Element Name:

Output:

 Adding Attributes:

Output:

Adding a Sub-Element and attributes:

Output:
Msg 6852, Level 16, State 1, Line 1
Attribute-centric column ‘Details/@Genre’ must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.

This above error message is self descriptive, it’s obvious that an attribute must not be defined after an element in XML hierarchy, it should be at the top in xml hierarchy.

Output:

Name less fields:

Name less field means, when a column does not have a name or an alias for it. PATH mode treats those columns in a different way.

Output:

When you have a real column and it does have a column name also but you want to make that column as column less or do not want to give it a XML tag then put a [*] as alias after the column.

Output:

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: