章节大纲

  • ISO 发布物 (ISO/IEC 9075-14) 是 SQL 标准的一部分。它以前被称为 SQLX 或 SQL/XML。它定义了 XML 数据类型以及作用于此数据类型的函数，还有在此数据类型中创建和处理 XML 对象（元素、属性等）的函数。Oracle 将该数据类型称为 XMLType。

    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]

    注释

    1. ↑ MSSQL 2005 及以上版本的 xml 数据类型的 .query() 方法 实现了该功能。

    2. ↑ MSSQL 2000 及以上版本的 SELECT 语句中的 FOR XML 子句 实现了类似功能。

    3. ↑ MSSQL 2005 及以上版本的 xml 数据类型的 .value() 方法 实现了该功能。

    4. ↑ 在 MSSQL 2005 及以上版本中，可以使用 xml 数据类型和 SQLCLR 创建模拟该功能的函数或存储过程。

    5. ↑ MSSQL 2005 及以上版本的 xml 数据类型的 .nodes() 方法 实现了该功能。

    6. ↑ 在 MSSQL 2005 及以上版本中，可以使用字符串连接运算符 +，结合将 xml 数据类型实例显式转换为字符类型，以模拟该功能。

    7. ↑ MSSQL 2005 及以上版本的 xml 数据类型的 .modify() 方法 使用 XML DML 来实现此功能。

    Notes

    1. ↑ The MSSQL 2005 and higher xml data type .query() method performs this function.
    2. ↑ Jump up to: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.