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.