Design a query strategy by using FOR XML

May include but is not limited to: views, FOR XML PATH and EXPLICIT, FOR XML… TYPE

I have a similar post on this, which you can find in the category of the exam 70-433, but here’s a quick refresher. You can retrieve relational data as XML by specifying the FOR XML clause in the end of the query. It has four options:

  • AUTO: retrieves data with a default node name of the table. When used with joins, the row names differentiate.
  • RAW: mostly for debugging and testing, returns data as xml with the node name as row.
  • PATH: lets you query relational data with XPath.
  • EXPLICIT: a somewhat complex option which lets you return data with a syntax similar to XPath. I’d recommend using PATH instead, you’d lose only legacy support with it.

There are a bunch of other options which can be used in conjunction with the FOR XML clause, here’s a list:

  • XMLDATA: appends an XML-Data Reduced schema to the beginning, but no longer used, consider XMLSCHEMA instead. Works with AUTO, RAW and EXPLICIT.
  • XMLSCHEMA: returns an inline XML schema definition. Available for AUTO and RAW.
  • ELEMENTS XSNIL/ABSENT: ABSENT is the default, it doesn’t mark NULL values. When using XSNIL, NULLS are included with an xsi:nil attribute set to true. AUTO, RAW and PATH has it.
  • BINARY BASE64: when you return binary data in the result set, you must specify this one. Available for every setting.
  • TYPE: returns the result set as an instance of type xml, instead of string. Usable with everyone.
  • ROOT: lets you specify a root name for the returned xml.
  • (‘ElementName’): you can give a name for the default xml node. Only RAW and PATH have this option.