SQL Dialects Reference/SQL XML
Appearance
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]- ↑ The MSSQL 2005 and higher
xml
data type.query()
method performs this function. - ↑ a b The MSSQL 2000 and higher
FOR XML
clause of the SQLSELECT
statement performs a similar function. - ↑ The MSSQL 2005 and higher
xml
data type.value()
method performs this function. - ↑ In MSSQL 2005 and higher the
xml
data type and SQLCLR can be used to create functions/procedures that simulate this functionality. - ↑ The MSSQL 2005 and higher
xml
data type.nodes()
method performs this function. - ↑ In MSSQL 2005 and higher the
+
string concatenation operator can be used in conjunction with explicit conversions ofxml
data type instances to character data types to simulate this function. - ↑ The MSSQL 2005 and higher
xml
data type.modify()
method performs this function using XML DML.