ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

OPENXML function [String]

Function 

Generates a result set from an XML document.

Syntax 

OPENXML ( xml-expression,
 xpath-query [, flags [, namespace-declaration ] ] )
WITH ( column-name column-type [ xpath-query ],... )

Parameters 

xml-expression    The XML on which the result set is based. This can be any string expression, such as a constant, variable, or column.

xpath-query    A string containing an XPath query. XPath allows you to specify patterns that describe the structure of the XML document you are querying. The XPath pattern included in this argument selects the nodes from the XML document. Each node that matches the XPath query in the second argument generates one row in the table.

Metaproperties can only be specified in WITH clause xpath-query arguments. A metaproperty is accessed within an XPath query as if it was an attribute. If a namespace-declaration is not specified, then by default the prefix mp is bound to the Uniform Resource Identifier (URI) urn:ianywhere-com:asa-xpath-metaprop. If a namespace-declaration is specified, this URI must be bound to mp or some other prefix in order to access metaproperties in the query. OPENXML supports the following metaproperties:

• @mp:id    returns an ID for a node that is unique within the XML document. The ID for a given node in a given document may change if the database server is restarted. The value of this metaproperty increases with document order.

• @mp:localname    returns the local part of the node's name, or NULL if the node does not have a name.

• @mp:prefix    returns the prefix part of the node's name, or NULL if the node does not have a name or if the name is not prefixed.

• @mp:namespaceuri    returns the URI of the namespace that the node belongs to, or NULL if the node is not in a namespace.

• @mp:xmltext    returns a subtree of the XML document in XML form. For example, when you match an internal node, you can use this metaproperty to return an XML string, rather than the concatenated values of the descendant text nodes.

flags    Indicates the mapping that should be used between the XML data and the result set when an XPath query is not specified in the WITH clause. If the flags parameter is not specified, the default behavior is to map attributes to columns in the result set. The flags parameter can have one of the following values:

namespace-declaration    An XML document. The in-scope namespaces for the query are taken from the root element of the document. If namespaces are specified, then you must include a flags argument, even if all the xpath-query arguments are specified.

WITH clause    Specifies the schema of the result set and how the value is found for each column in the result set. WITH clause xpath-query arguments are matched relative to the matches for the xpath-query in the second argument. If a WITH clause expression matches more than one node, then only the first node in the document order is used. If the node is not a text node, then the result is found by appending all the text node descendants. If a WITH clause expression does not match any nodes, then the column for that row is NULL.

The OPENXML WITH clause syntax is similar to the syntax for selecting from a stored procedure.

For information about selecting from a stored procedure, see FROM clause.

column-name    The name of the column in the result set.

column-type    The data type of the column in the result set. The data type must be compatible with the values selected from the XML document.

For a list of data types, see SQL Data Types.

Usage 

OPENXML parses the xml-expression and models the result as a tree. The tree contains a separate node for each element, attribute, and text node, or other XML construct. The XPath queries supplied to OPENXML are used to select nodes from the tree, and the selected nodes are then mapped to the result set.

The XML parser used by OPENXML is non-validating, and does not read the external DTD subset or external parameter entities.

When there are multiple matches for a column expression, the first match in the document order (the order of the original XML document before it was parsed) is used. NULL is returned if there are no matching nodes. When an internal node is selected, the result is all the descendant text nodes of the internal node concatenated together.

Columns of type BINARY, LONG BINARY, IMAGE, and VARBINARY are assumed to be base64-encoded format and are decoded automatically. If you generate XML using the FOR XML clause, these types are base64-encoded, and can be decoded using OPENXML.

For information about the FOR XML clause and encoding binary data, see FOR XML and binary data.

OPENXML supports a subset of the XPath syntax, as follows:

• The child, self, attribute, descendant, descendant-or-self, and parent axes are fully supported.

  For descriptions, see Using XPath expressions.

• Both abbreviated and unabbreviated syntax can be used for all supported features. For example, 'a' is equivalent to 'child::a' and '..' is equivalent to 'parent::node()'.

• Name tests can use wildcards. For example, 'a/*/b'.

• The following Kind tests are supported: node(), text(), processing-instruction(), and comment().

• Qualifiers of the form expr1[expr2] can be used, where expr2 is any supported XPath expression. A qualifier evaluates TRUE if expr2 matches one or more nodes. For example, 'a[b]' finds a nodes that have at least one b child.

For information about the XPath query language, see http://www.w3.org/TR/xpath.

Standards and compatibility 

• Sybase    Not supported by Adaptive Server Enterprise.

See also 

Importing XML using OPENXML

Example 

The following query generates a result set from the XML document supplied as the first argument to OPENXML:

SELECT * FROM OPENXML( '<products>
                 <prod_type id="301">Tee Shirt</prod_type>
                 <prod_type id="401">Baseball Cap</prod_type>
                 </products>',
                 '/products/prod_type' )
WITH ( prod_name LONG VARCHAR 'text()', prod_id CHAR(3) '@id')

This query generates the following result:

For more examples of using OPENXML, see Importing XML using OPENXML.