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].

And you have eight switches for these clauses, as seen in the following table:

  • XMLDATA: deprecated, forget it.
  • XMLSCHEMA: returns an inline XML schema definition. Can be used in conjunction with the AUTO and RAW clause.
  • ELEMENTS: format the XML output as XML elements instead of attributes. Accepts two values: XSINIL, which specifies columns with NULLS to have an xsi:nil attribute, and ABSENT, the default, which doesn’t mark columns with NULLS. Can be used with AUTO, RAW and PATH.
  • BINARY BASE64: binary values will be returned in a Base64-encoded form. Can be used with all clauses.
  • TYPE: specifies that the resulting XML should be returned in the SQL data type xml format. Particularly useful if you form XML in a subquery. Can be used with all clauses,
  • ROOT: appends a single-top level root element to the result, therefore creates a valid XML document. Can be used with all clauses.
  • (‘ElementName’): lets you specify the default XML element name. Can be used with AUTO, RAW and PATH.

Now a little code, just to get the taste of it:

SELECT * FROM Production.Product
FOR XML RAW, ELEMENTS

Results in:

<row>
<ProductID>1</ProductID>

</row>
<row>
...
</row>

SELECT * FROM Production.Product
FOR XML AUTO, ROOT(‘AdventureWorks’)

The result:

<AdventureWorks>
<Production.Product ProductID=”1”…/>
</Production.Product …/>
</AdventureWorks>

I believe that with a little practice, you can catch up with the syntax, so I wouldn’t write more about this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s