Type System (XQuery)

  • Article

XQuery is a strongly-typed language for schema types and a weakly-typed language for untyped data. The predefined types of XQuery include the following:

This topic also describes the following:

Built-in Types of XML Schema

The built-in types of XML schema have a predefined namespace prefix of xs. Some of these types include xs:integer and xs:string. All these built-in types are supported. You can use these types when you create an XML schema collection.

When querying typed XML, the static and dynamic type of the nodes is determined by the XML schema collection associated with the column or variable that is being queried. For more information about static and dynamic types, see Expression Context and Query Evaluation (XQuery). For example, the following query is specified against a typed xml column (Instructions). The expression uses instance of to verify that the typed value of the LotSize attribute returned is of xs:decimal type.

SELECT Instructions.query('
   DECLARE namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
   data(/AWMI:root[1]/AWMI:Location[@LocationID=10][1]/@LotSize)[1] instance of xs:decimal
') AS Result
FROM Production.ProductModel
WHERE ProductModelID=7

This typing information is provided by the XML schema collection associated with the column. For more information, see xml Data Type Representation in the AdventureWorks Database.

Types Defined in XPath Data Types Namespace

The types defined in the http://www.w3.org/2004/07/xpath-datatypes namespace have a predefined prefix of xdt. The following applies to these types:

  • You cannot use these types when you are creating an XML schema collection. These types are used in the XQuery type system and are used for static typing. You can cast to the atomic types, for example, xdt:untypedAtomic, in the xdt namespace.
  • When querying untyped XML, the static and dynamic type of element nodes is xdt:untyped, and the type of attribute values is xdt:untypedAtomic. The result of a query() method generates untyped XML. This means that the XML nodes are returned as xdt:untyped and xdt:untypedAtomic, respectively.
  • The xdt:dayTimeDuration and xdt:yearMonthDuration types are not supported.

In the following example, the query is specified against an untyped XML variable. The expression, data(/a[1]), returns a sequence of one atomic value. The data() function returns the typed value of the element <a>. Because the XML being queried is untyped, the type of the value returned is xdt:untypedAtomic. Therefore, instance of returns true.

DECLARE @x xml
SET @x='<a>20</a>'
SELECT @x.query( 'data(/a[1]) instance of xdt:untypedAtomic' )

Instead of retrieving the typed value, the expression (/a[1]) in the following example returns a sequence of one element, element <a>. The instance of expression uses the element test to verify that the value returned by the expression is an element node of xdt:untyped type.

DECLARE @x xml
SET @x='<a>20</a>'
-- Is this an element node whose name is "a" and type is xdt:untyped.
SELECT @x.query( '/a[1] instance of element(a, xdt:untyped?)')
-- Is this an element node of type xdt:untyped.
SELECT @x.query( '/a[1] instance of element(*, xdt:untyped?)')
-- Is this an element node?
SELECT @x.query( '/a[1] instance of element()')

Note

When you are querying a typed XML instance and the query expression includes the parent axis, the static type information of the resulting nodes is no longer available. However, the dynamic type is still associated with the nodes.

Typed Value vs. String Value

Every node has a typed value and a string value. For typed XML data, the type of the typed value is provided by the XML schema collection associated with the column or variable that is being queried. For untyped XML data, the type of the typed value is xdt:untypedAtomic.

You can use the data() or string() function to retrieve the value of a node:

In the following XML schema collection, the <root> element of the integer type is defined:

CREATE XML SCHEMA COLLECTION SC AS N'
<schema xmlns="http://www.w3.org/2001/XMLSchema">
      <element name="root" type="integer"/>
</schema>'
GO

In the following example, the expression first retrieves the typed value of /root[1] and then adds 3 to it.

DECLARE @x xml(SC)
SET @x='<root>5</root>'
SELECT @x.query('data(/root[1]) + 3')

In the next example, the expression fails, because the string(/root[1]) in the expression returns a string type value. This value is then passed to an arithmetic operator that takes only numeric type values as its operands.

-- Fails because the argument is string type (must be numeric primitive type).
DECLARE @x xml(SC)
SET @x='<root>5</root>'
SELECT @x.query('string(/root[1]) + 3')

The following example computes the total of the LaborHours attributes. The data() function retrieves the typed values of LaborHours attributes from all the <Location> elements for a product model. According to the XML schema associated with the Instruction column, LaborHours is of xs:decimal type.

SELECT Instructions.query(' 
DECLARE namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
             sum(data(//AWMI:Location/@LaborHours)) 
') AS Result 
FROM Production.ProductModel 
WHERE ProductModelID=7

This query returns 12.75 as the result.

Note

The explicit use of the data() function in this example is for illustration only. If it is not specified, sum() implicitly applies the data() function to extract the typed values of the nodes.

Sequence Type Matching

An XQuery expression value is always a sequence of zero or more items. An item can be either an atomic value or a node. The sequence type refers to the ability to match the sequence type returned by a query expression with a specific type. For example:

  • If the expression value is atomic, you may want to know if it is an integer, decimal, or string type.
  • If the expression value is an XML node, you may want to know if it is a comment node, a processing instruction node, or a text node.
  • You may want to know if the expression returns an XML element or an attribute node of a specific name and type.

You can use the instance of Boolean operator in sequence type matching. For more information about the instance of expression, see SequenceType Expressions (XQuery).

Comparing the Atomic Value Type Returned by an Expression

If an expression returns a sequence of atomic values, you may have to find the type of the value in the sequence. The following examples illustrate how sequence type syntax can be used to evaluate the atomic value type returned by an expression.

Example A

The empty() sequence type can be used in a sequence type expression to determine whether the sequence returned by the specified expression is an empty sequence.

In the following example, the XML schema allows the <root> element to be nilled:

CREATE XML SCHEMA COLLECTION SC AS N'
<schema xmlns="http://www.w3.org/2001/XMLSchema">
      <element name="root" nillable="true" type="byte"/>
</schema>'
GO

Now, if a typed XML instance specifies a value for the <root> element, instance of empty() returns False.

DECLARE @var XML(SC1)
SET @var = '<root>1</root>'
-- The following returns False
SELECT @var.query('data(/root[1]) instance of  empty() ')
GO

If the <root> element is nilled in the instance, its value is an empty sequence and the instance of empty() returns True.

DECLARE @var XML(SC)
SET @var = '<root xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />'
SELECT @var.query('data(/root[1]) instance of  empty() ')
GO

Example B

Sometimes, you may want to evaluate the sequence type returned by an expression before processing. For example, you may have an XML schema in which a node is defined as a union type. In the following example, the XML schema in the collection defines the attribute a as a union type whose value can be of decimal or string type.

-- Drop schema collection if it exists.
-- DROP XML SCHEMA COLLECTION SC.
-- GO
CREATE XML SCHEMA COLLECTION SC AS N'
<schema xmlns="http://www.w3.org/2001/XMLSchema">
  <element name="root">
    <complexType>
       <sequence/>
         <attribute name="a">
            <simpleType>
               <union memberTypes="decimal string"/>
            </simpleType>
         </attribute>
     </complexType>
  </element>
</schema>'
GO

Before processing a typed XML instance, you may want to know the type of the attribute a value. In the following example, the attribute a value is a decimal type. Therefore, instance of xs:decimal returns True.

DECLARE @var XML(SC)
SET @var = '<root a="2.5"/>'
SELECT @var.query('data((/root/@a)[1]) instance of xs:decimal')
GO

Now, change the attribute a value to a string type. The instance of xs:string will return True.

DECLARE @var XML(SC)
SET @var = '<root a="Hello"/>'
SELECT @var.query('data((/root/@a)[1]) instance of xs:string')
GO

Example C

This example illustrates the effect of cardinality in a sequence expression. The following XML schema defines a <root> element that is of byte type and is nillable.

CREATE XML SCHEMA COLLECTION SC AS N'
<schema xmlns="http://www.w3.org/2001/XMLSchema">
      <element name="root" nillable="true" type="byte"/>
</schema>'
GO

In the following query, because the expression returns a singleton of byte type, instance of returns True.

DECLARE @var XML(SC)
SET @var = '<root>111</root>'
SELECT @var.query('data(/root[1]) instance of  xs:byte ') 
GO

If you make the <root> element nil, its value is an empty sequence. That is, the expression, /root[1], returns an empty sequence. Therefore, instance of xs:byte returns False. Note that the default cardinality in this case is 1.

DECLARE @var XML(SC)
SET @var = '<root xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></root>'
SELECT @var.query('data(/root[1]) instance of  xs:byte ') 
GO
-- result = false

If you specify cardinality by adding the occurrence indicator (?), the sequence expression returns True.

DECLARE @var XML(SC)
SET @var = '<root xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></root>'
SELECT @var.query('data(/root[1]) instance of  xs:byte? ') 
GO
-- result = true

Note that the testing in a sequence type expression is completed in two stages:

  1. First, the testing determines whether the expression type matches the specified type.
  2. If it does, the testing then determines whether the number of items returned by the expression matches the occurrence indicator specified.

If both are true, the instance of expression returns True.

Example D

In the following example, a query is specified against an Instructions column of xml type in the AdventureWorks database. It is a typed XML column because it has a schema associated with it. For more information, see xml Data Type Representation in the AdventureWorks Database. The XML schema defines the LocationID attribute of the integer type. Therefore, in the sequence expression, the instance of xs:integer? returns True.

SELECT Instructions.query(' 
declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
data(/AWMI:root[1]/AWMI:Location[1]/@LocationID) instance of xs:integer?') as Result 
FROM Production.ProductModel 
WHERE ProductModelID = 7

Comparing the Node Type Returned by an Expression

If an expression returns a sequence of nodes, you may have to find the type of the node in the sequence. The following examples illustrate how sequence type syntax can be used to evaluate the node type returned by an expression. You can use the following sequence types:

  • item() – Matches any item in the sequence.
  • node() – Determines whether the sequence is a node.
  • processing-instruction() – Determines whether the expression returns a processing instruction.
  • comment() – Determines whether the expression returns a comment.
  • document-node() – Determines whether the expression returns a document node.

The following examples illustrate these sequence types.

Example A

In this example, several queries are executed against an untyped XML variable. These queries illustrate the use of sequence types.

DECLARE @var XML
SET @var = '<?xml-stylesheet href="someValue" type="text/xsl" ?>
<root>text node
  <!-- comment 1 --> 
  <a>Data a</a>
  <!-- comment  2 -->
</root>'

In the first query, the expression returns the typed value of element <a>. In the second query, the expression returns element <a>. Both are items. Therefore, both queries return True.

SELECT @var.query('data(/root[1]/a[1]) instance of item()')
SELECT @var.query('/root[1]/a[1] instance of item()')

All the XQuery expressions in the following three queries return the element node child of the <root> element. Therefore, the sequence type expression, instance of node(), returns True, and the other two expressions, instance of text() and instance of document-node(), return False.

SELECT @var.query('(/root/*)[1] instance of node()')
SELECT @var.query('(/root/*)[1] instance of text()')
SELECT @var.query('(/root/*)[1] instance of document-node()')

In the following query, the instance of document-node() expression returns True, because the parent of the <root> element is a document node.

SELECT @var.query('(/root/..)[1] instance of document-node()') -- true

In the following query, the expression retrieves the first node from the XML instance. Because it is a processing instruction node, the instance of processing-instruction() expression returns True.

SELECT @var.query('(/node())[1] instance of processing-instruction()')

Implementation Limitations

These are the specific limitations:

  • document-node() with content type syntax is not supported.
  • processing-instruction(name) syntax is not supported.

Element Test

An element test is used to match the element node returned by an expression to an element node with a specific name and type. You can use these element tests:

element ()
element(ElementName)
element(ElementName, ElementType?) 
element(*, ElementType?)

Attribute Test

The attribute test determines whether the attribute returned by an expression is an attribute node. You can use these attribute tests.

attribute()

attribute(AttributeName)

attribute(AttributeName, AttributeType)

Test Examples

The following examples illustrate scenarios in which element and attribute tests are useful.

Example A

The following XML schema defines the CustomerType complex type where <firstName> and <lastName> elements are optional. For a specified XML instance, you may have to determine whether the first name exists for a particular customer.

CREATE XML SCHEMA COLLECTION SC AS N'
<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="myNS" xmlns:ns="myNS">
  <complexType name="CustomerType">
     <sequence>
        <element name="firstName" type="string" minOccurs="0" 
                  nillable="true" />
        <element name="lastName" type="string" minOccurs="0"/>
     </sequence>
  </complexType>
  <element name="customer" type="ns:CustomerType"/>
</schema>
'
GO
DECLARE @var XML(SC)
SET @var = '<x:customer xmlns:x="myNS">
<firstName>SomeFirstName</firstName>
<lastName>SomeLastName</lastName>
</x:customer>'

The following query uses an instance of element (firstName) expression to determine whether the first child element of <customer> is an element whose name is <firstName>. In this case, it returns True.

SELECT @var.query('declare namespace x="myNS"; 
     (/x:customer/*)[1] instance of element (firstName)')
GO

If you remove the <firstName> element from the instance, the query will return False.

You can also use the following:

  • The element(ElementName, ElementType?) sequence type syntax, as shown in the following query. It matches a nilled or non-nilled element node whose name is firstName and whose type is xs:string.

    SELECT @var.query('declare namespace x="myNS"; 
(/x:customer/*)[1] instance of element (firstName, xs:string?)')

  • The element(*, type?) sequence type syntax, as shown in the following query. It matches the element node if its type is xs:string, regardless of its name.

    SELECT @var.query('declare namespace x="myNS"; (/x:customer/*)[1] instance of element (*, xs:string?)')
GO

Example B

The following example illustrates how to determine whether the node returned by an expression is an element node with a specific name. It uses the element() test.

In the following example, the two <Customer> elements in the XML instance that are being queried are of two different types, CustomerType and SpecialCustomerType. Assume that you want to know the type of the <Customer> element returned by the expression. The following XML schema collection defines the CustomerType and SpecialCustomerType types.

CREATE XML SCHEMA COLLECTION SC AS N'
<schema xmlns="http://www.w3.org/2001/XMLSchema"
          targetNamespace="myNS"  xmlns:ns="myNS">
  <complexType name="CustomerType">
    <sequence>
      <element name="firstName" type="string"/>
      <element name="lastName" type="string"/>
    </sequence>
  </complexType>
  <complexType name="SpecialCustomerType">
     <complexContent>
       <extension base="ns:CustomerType">
        <sequence>
            <element name="Age" type="int"/>
        </sequence>
       </extension>
     </complexContent>
    </complexType>
   <element name="customer" type="ns:CustomerType"/>
</schema>
'
GO

This XML schema collection is used to create a typed xml variable. The XML instance assigned to this variable has two <customer> elements of two different types. The first element is of CustomerType and the second element is of SpecialCustomerType type.

DECLARE @var XML(SC)
SET @var = '
<x:customer xmlns:x="myNS">
   <firstName>FirstName1</firstName>
   <lastName>LastName1</lastName>
</x:customer>
<x:customer xsi:type="x:SpecialCustomerType" xmlns:x="myNS" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <firstName> FirstName2</firstName>
   <lastName> LastName2</lastName>
   <Age>21</Age>
</x:customer>'

In the following query, the instance of element (*, x:SpecialCustomerType ?) expression returns False, because the expression returns the first customer element that is not of SpecialCustomerType type.

SELECT @var.query('declare namespace x="myNS"; 
    (/x:customer)[1] instance of element (*, x:SpecialCustomerType ?)')

If you change the expression of the previous query and retrieve the second <customer> element (/x:customer)[2]), the instance of will return True.

Example C

This example uses the attribute test. The following XML schema defines the CustomerType complex type with CustomerID and Age attributes. The Age attribute is optional. For a specific XML instance, you may want to determine whether the Age attribute is present in the <customer> element.

CREATE XML SCHEMA COLLECTION SC AS N'
<schema xmlns="http://www.w3.org/2001/XMLSchema"
       targetNamespace="myNS" xmlns:ns="myNS">
<complexType name="CustomerType">
  <sequence>
     <element name="firstName" type="string" minOccurs="0" 
               nillable="true" />
     <element name="lastName" type="string" minOccurs="0"/>
  </sequence>
  <attribute name="CustomerID" type="integer" use="required" />
  <attribute name="Age" type="integer" use="optional" />
 </complexType>
 <element name="customer" type="ns:CustomerType"/>
</schema>
'
GO

The following query returns True, because there is an attribute node whose name is Age in the XML instance that is being queried. The attribute(Age) attribute test is used in this expression. Because attributes have no order, the query uses the FLWOR expression to retrieve all the attributes and then test each attribute by using the instance of expression. The example first creates an XML schema collection to create a typed xml variable.

DECLARE @var XML(SC)
SET @var = '<x:customer xmlns:x="myNS" CustomerID="1" Age="22" >
<firstName>SomeFName</firstName>
<lastName>SomeLName</lastName>
</x:customer>'
SELECT @var.query('declare namespace x="myNS"; 
FOR $i in /x:customer/@*
RETURN
    IF ($i instance of attribute (Age)) THEN
        "true"
        ELSE
        ()')   
GO

If you remove the optional Age attribute from the instance, the previous query will return False.

You can specify attribute name and type (attribute(name,type)) in the attribute test.

SELECT @var.query('declare namespace x="myNS"; 
FOR $i in /x:customer/@*
RETURN
    IF ($i instance of attribute (Age, xs:integer)) THEN
        "true"
        ELSE
        ()')

Alternatively, you can specify the attribute(*, type) sequence type syntax. This matches the attribute node if the attribute type matches the specified type, regardless of the name.

Implementation Limitations

These are the specific limitations:

  • In the element test, the type name must be followed by the occurrence indicator (?).
  • element(ElementName, TypeName) is not supported.
  • element(*, TypeName) is not supported.
  • schema-element() is not supported.
  • schema-attribute(AttributeName) is not supported.
  • Explicitly querying for xsi:type or xsi:nil is not supported.

See Also

Concepts

SequenceType Expressions (XQuery)
XQuery Basics

Other Resources

Using SQL Server Profiler

Help and Information

Getting SQL Server 2005 Assistance