Data Accessor Functions - data (XQuery)
Applies to: SQL Server
Returns the typed value for each item specified by $arg.
Syntax
fn:data ($arg as item()*) as xdt:untypedAtomic*
Arguments
$arg
Sequence of items whose typed values will be returned.
Remarks
The following applies to typed values:
The typed value of an atomic value is the atomic value.
The typed value of a text node is the string value of the text node.
The typed value of a comment is the string value of the comment.
The typed value of a processing instruction is the content of the processing-instruction, without the processing instruction target name.
The typed value of a document node is its string value.
The following applies to attribute and element nodes:
If an attribute node is typed with an XML schema type, its typed value is the typed value, accordingly.
If the attribute node is untyped, its typed value is equal to its string value that is returned as an instance of xdt:untypedAtomic.
If the element node has not been typed, its typed value is equal to its string value that is returned as an instance of xdt:untypedAtomic.
The following applies to typed element nodes:
If the element has a simple content type, data() returns the typed value of the element.
If the node is of complex type, including xs:anyType, data() returns a static error.
Although using the data() function is frequently optional, as shown in the following examples, specifying the data() function explicitly increases query readability. For more information, see XQuery Basics.
You cannot specify data() on constructed XML, as shown in the following:
declare @x xml
set @x = ''
select @x.query('data(<SomeNode>value</SomeNode>)')
Examples
This topic provides XQuery examples against XML instances stored in various xml type columns in the AdventureWorks database.
A. Using the data() XQuery function to extract typed value of a node
The following query illustrates how the data() function is used to retrieve values of an attribute, an element, and a text node:
WITH XMLNAMESPACES (
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS p1,
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm)
SELECT CatalogDescription.query(N'
for $pd in //p1:ProductDescription
return
<Root
ProductID = "{ data( ($pd//@ProductModelID)[1] ) }"
Feature = "{ data( ($pd/p1:Features/wm:Warranty/wm:Description)[1] ) }" >
</Root>
') as Result
FROM Production.ProductModel
WHERE ProductModelID = 19
This is the result:
<Root ProductID="19" Feature="parts and labor"/>
As mentioned, the data() function is optional when you are constructing attributes. If you do not specify the data() function, it is implicitly assumed. The following query produces the same results as the previous query:
WITH XMLNAMESPACES (
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS p1,
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm)
SELECT CatalogDescription.query('
for $pd in //p1:ProductDescription
return
<Root
ProductID = "{ ($pd/@ProductModelID)[1] }"
Feature = "{ ($pd/p1:Features/wm:Warranty/wm:Description)[1] }" >
</Root>
') as Result
FROM Production.ProductModel
WHERE ProductModelID = 19
The following examples illustrate instances in which the data() function is required.
In the following query, $pd/p1:Specifications/Material returns the <Material
> element. Also, data($pd/p1:Specifications/ Material) returns character data typed as xdt:untypedAtomic, because <Material
> is untyped. When the input is untyped, the result of data() is typed as xdt:untypedAtomic.
SELECT CatalogDescription.query('
declare namespace p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
for $pd in //p1:ProductDescription
return
<Root>
{ $pd/p1:Specifications/Material }
{ data($pd/p1:Specifications/Material) }
</Root>
') as Result
FROM Production.ProductModel
WHERE ProductModelID = 19
This is the result:
<Root>
<Material>Aluminum Alloy</Material>Aluminum Alloy
</Root>
In the following query, data($pd/p1:Features/wm:Warranty) returns a static error, because <Warranty
> is a complex type element.
WITH XMLNAMESPACES (
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS p1,
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm)
SELECT CatalogDescription.query('
<Root>
{ /p1:ProductDescription/p1:Features/wm:Warranty }
{ data(/p1:ProductDescription/p1:Features/wm:Warranty) }
</Root>
') as Result
FROM Production.ProductModel
WHERE ProductModelID = 23