WL#2410: SELECT ... FOR XML

SELECT ... FOR XML as implemented by MSSQL.
In a nutshell, it returns a result set as a single row/col (XML encoded string).

Some extra syntax info
(taken from http://www32.brinkster.com/srisamp/sqlArticles/article_20.htm)

... FOR XML RAW | AUTO | EXPLICIT [, XMLDATA] [, ELEMENTS] [, BINARY BASE64]

The XML RAW is the most simplest of the forms and probably the most fastest of
all the options. This format provides the output of each row within a generic
node called "row". All the columns of data are transformed as attributes.
SELECT title_id, title FROM titles FOR XML RAW


The XML AUTO method is the next possible form and it provides more control over
the XML fragment than the RAW mode.
SELECT title_id, title FROM titles FOR XML AUTO


Notice that each row is named after the table from where the row came. Note that
all columns are still rendered as attributes. The main difference between RAW
and AUTO lies in the fact in how they handle joins.

SELECT authors.au_id, titles.title_id
  FROM authors
  INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
  INNER JOIN titles ON titles.title_id = titleauthor.title_id
  FOR XML RAW


Same query with FOR XML AUTO

	


The difference is immediately apparent. The RAW format does not show anything in
the output that indicates that a join has happened. All the columns are rendered
as attributes. In the AUTO format however, notice how the output is nested
within the "authors" element, which provides a clean understanding of the
relatioship between the tables involved in the join.


In all the outputs thus far, we have seen the columns of the table being
rendered as attributes. The ELEMENTS option renders each column as an element

Query as above but with FOR XML AUTO, ELEMENTS

	409-56-7008
	
		BU1032
	


-----

(Jon Stephens comments:)

Being able to produce XML output on a per-SELECT basis could be a very useful
extension, as currently this can be done only on a per-conneciton basis.
However, I have some misgivings as regards implementing the MSSQL syntax and
behaviour verbatim, the main one being that both FOR XML RAW and FOR XML AUTO
allow the creation of invalid XML. The reason for this is that (My)SQL column
names can contain characters which are illegal to use in XML element or
attribute names. This is why the behaviour of the --xml option for mysql and
mysqldump was changed in 5.0.3.

In fact, I would go one step further than the current --xml fix, and use an
output format like this:


  
  


since column values can contain characters which might be invalid unless
converted to their equivalent character entities.

-----

A quick & easy way to do this would be to implement using a PROCEDURE - like
PROCEDURE ANALYSE() - but with the grammar of MSSQL.
(idea by Bar, phrased by Arjen)

-----