SQL Dialects Reference/SQL XML

The ISO publication (ISO/IEC 9075-14) is part of the SQL standard. Formerly it was named SQLX or SQL/XML. It defines the data type XML and functions acting on this data type as well as functions creating and handling XML objects (elements, attributes, ...) within this data type. Oracle denotes the data type XMLType. Function Description DB2 SQLite MonetDB MySQL PostgreSQL Firebird OpenLink Virtuoso Oracle MSSQLVersion Software Version(s) Supported V9 V11+ Planned 8.3+ 3.5-4.x 9-10x Proprietary XML ExtensionsXMLElement() Create an XML Element XMLElement() xmlelement() Planned xmlelement() XMLElement() XMLElement() .query()The MSSQL 2005 and higher xml data type .query() method performs this function.XMLForest()Create an XML Fragment from passed-in components. XMLForest() xmlforest() xmlforest() Planned (patch exists) XMLForest()XMLForest() FOR XML clauseThe MSSQL 2000 and higher FOR XML clause of the SQL SELECT statement performs a similar function.XMLColAttVal()Create an XML fragment and then expands the resulting XML so that each XML fragment has the name "column" with the attribute "name" Planned Planned XMLColAttVal() XMLColAttVal() FOR XML clauseExtractValue()Takes as arguments an XML instance and an XPath expression and returns a scalar value of the resultant node. Planned Planned ExtractValue() ExtractValue() .value()The MSSQL 2005 and higher xml data type .value() method performs this function.XMLTransform()Takes as arguments an XML instance and an XSL style sheet, which is itself a form of XML instance. It applies the style sheet to the instance and returns an XML. Planned Planned XMLTransform() XMLTransform() In MSSQL 2005 and higher the xml data type and SQLCLR can be used to create functions/procedures that simulate this functionality.XMLSequence()Takes input and returns either a varray of the top-level nodes in the XML, or an XMLSequence type an XML document for each row of the cursor. xmlsequence() Planned Planned XMLSequence() XMLSequence() .nodes()The MSSQL 2005 and higher xml data type .nodes() method performs this function.XMLConcat()Takes as input a series of XML instances, concatenates the series of elements for each row, and returns the concatenated series. XMLConcat Planned Planned (patch exists) XMLConcat() XMLConcat() In MSSQL 2005 and higher the + string concatenation operator can be used in conjunction with explicit conversions of xml data type instances to character data types to simulate this function.UpdateXML()Takes as arguments an XML instance and an XPath-value pair, and returns an XML instance with the updated value. Planned Planned UpdateXML() UpdateXML() .modify()The MSSQL 2005 and higher xml data type .modify() method performs this function using XML DML. Notes