SQL Dialects Reference/SQL XML

From Wikibooks, open books for an open world
Jump to navigation Jump to search

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 MSSQL
Version Software Version(s) Supported V9 N/A V11+ Planned 8.3+ N/A 3.5-4.x 9-10x Proprietary XML Extensions
XMLElement() Create an XML Element XMLElement() N/A xmlelement() Planned xmlelement() N/A XMLElement() XMLElement() .query()[1]
XMLForest() Create an XML Fragment from passed-in components. XMLForest() N/A xmlforest() xmlforest() Planned (patch exists) N/A XMLForest() XMLForest() FOR XML clause[2]
XMLColAttVal() Create an XML fragment and then expands the resulting XML so that each XML fragment has the name "column" with the attribute "name" N/A N/A Planned Planned N/A XMLColAttVal() XMLColAttVal() FOR XML clause[2]
ExtractValue() Takes as arguments an XML instance and an XPath expression and returns a scalar value of the resultant node. N/A N/A Planned Planned N/A ExtractValue() ExtractValue() .value()[3]
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. N/A N/A Planned Planned N/A XMLTransform() XMLTransform() N/A[4]
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. N/A xmlsequence() Planned Planned N/A XMLSequence() XMLSequence() .nodes()[5]
XMLConcat() Takes as input a series of XML instances, concatenates the series of elements for each row, and returns the concatenated series. XMLConcat N/A N/A Planned Planned (patch exists) N/A XMLConcat() XMLConcat() N/A[6]
UpdateXML() Takes as arguments an XML instance and an XPath-value pair, and returns an XML instance with the updated value. N/A N/A Planned Planned N/A UpdateXML() UpdateXML() .modify()[7]

Notes[edit | edit source]

  1. The MSSQL 2005 and higher xml data type .query() method performs this function.
  2. a b The MSSQL 2000 and higher FOR XML clause of the SQL SELECT statement performs a similar function.
  3. The MSSQL 2005 and higher xml data type .value() method performs this function.
  4. In MSSQL 2005 and higher the xml data type and SQLCLR can be used to create functions/procedures that simulate this functionality.
  5. The MSSQL 2005 and higher xml data type .nodes() method performs this function.
  6. 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.
  7. The MSSQL 2005 and higher xml data type .modify() method performs this function using XML DML.