WL#2410: SELECT ... FOR XML
Affects: Server-7.1 — Status: Un-Assigned — Priority: Medium
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 <row title_id="PC1035" title="But Is It User Friendly?"/> 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 <titles title_id="PC1035" title="But Is It User Friendly?"/> 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 <row au_id="409-56-7008" title_id="BU1032"/> Same query with FOR XML AUTO <authors au_id="409-56-7008"> <titles title_id="BU1032"/> </authors> 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 <authors> <au_id>409-56-7008</au_id> <titles> <title_id>BU1032</title_id> </titles> </authors> ----- (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: <row> <col name="title_id" value="PC1035"/> <col name="title" value="But Is It User Friendly?"/> </row> 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) -----
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.