17.10 - XMLELEMENT Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - XML Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1140-171K
Language
English (United States)
XMLELEMENT (
  NAME XML_element_name

  [, XML_namespace_declaration ]

  [, ( XML_attribute_spec [,...] ) ]

  [, XML_value_expr
    [ OPTION
      {
        { NULL | EMPTY | ABSENT | NIL } ON NULL |
          NIL ON NO CONTENT
      }
    ]
  ]

  [ RETURNING { CONTENT | SEQUENCE } ]
)
You can include only one default namespace declaration item. It can appear in any position in the list of comma-separated namespace declarations or at the end of the list.

Syntax Elements

XML_namespace_declaration
XMLNAMESPACES (
  { 'XML_namespace_URI' AS XML_namespace_prefix |
    DEFAULT 'XML_namespace_URI' |
    NO DEFAULT
  } [,...]
)

XML namespaces that are scoped when the query expressions (row and column) are evaluated.

Namespaces declared in the prolog of the queries override any namespace declarations specified here.
XML_attribute_spec
XML_attribute_value [ AS XML_attribute_name ]
XML_element_name
The name of the element.
XML_value_expr
An instance of the XML type representing the XML element content.
  • XML_value_expr should be of XML type.
  • The element to be constructed will have a name derived by applying the rules in the ANSI SQL/XML specification that results in an XML 1.1 QName.
  • If XML element content is provided, you cannot have an attribute named "xsi:nil" (where xsi maps to xml schema instance namespace identified by the namespace URI "http://www.w3.org/2001/XMLSchema-instance").
  • If XML element content is NULL, the result value is dictated by the XML content option. If XML content option is not specified, the default is EMPTY ON NULL. If XML element content is NULL and XML content option is:
    • NULL ON NULL, NULL is returned
    • ABSENT ON NULL, an empty XQuery sequence is returned
    • NIL ON NULL, an attribute xsi:nil=true is added to the element being constructed (xsi being the prefix associated with xml schema instance schema)
    • EMPTY ON NULL, an empty element is constructed according to the rest of the parameters (such as name, namespaces, or attributes) and returned
  • If the XML element content is a sequence made up solely of nodes other than text and element nodes, and XML content option is NIL ON NO CONTENT, an attribute xsi:nil=true is added to the element being constructed.
OPTION
NULL ON NULL
EMPTY ON NULL
ABSENT ON NULL
NIL ON NULL
NIL ON NO CONTENT
XML content options.
RETURNING CONTENT
The return value is a document node with one or more child element nodes.
RETURNING SEQUENCE
The return value is a sequence (an ordered collection of items, each of which can either be a valid Xquery node or atomic value). This is the default.
'XML_namespace_URI'
The URI (Uniform Resource Identifier) that identifies the XML namespace.
XML_namespace_prefix
The URI (Uniform Resource Identifier) that identifies the XML namespace.
XML_attribute_value
A value for an attribute of the element.
XML_attribute_name
A name for the attribute. The name cannot be 'xmlns' and no two attributes can have the same qualified name (for example, same namespace and local name).