Reference back to the previous entry. Now that we know the rules, let's try them out:
— snip —
Data(),text() and string() accessors
XQuery has a function fn:data() to extract scalar, typed values from nodes, a node test text() to return text nodes, and the function fn:string() that returns the string value of a node. Their usages are sometimes confusing. Guidelines for their proper use in SQL Server 2005 are as follows. Consider the XML instance <age>12</age>.
Untyped XML: The path expression /age/text() returns the text node "12". The function fn:data(/age) returns the string value "12" and so does fn:string(/age).
Typed XML: The expression /age/text() returns static error for any simple typed <age> element. On the other hand, fn:data(/age) returns integer 12, while fn:string(/age) yields the string "12".
— snip —
Try this:
DECLARE @x xml
SET @x = '<age>12</age>'
— works as expected
SELECT @x.query('data(/age)')
GO
DECLARE @x xml
SET @x = '<age>12</age>'
— fails
— Msg 2211, Level 16, State 1, Line 6
— XQuery [query()]: Singleton (or empty sequence) required, found operand of type 'element(age,xdt:untypedAny) *'
SELECT @x.query('string(/age)')
GO
Oh. This message looks familiar. It turns out that XQuery functions are strongly typed also. Here's the definition of fn:string and fn:data:
fn:string($arg as item()?) as xs:string
fn:data($arg as item()*) as xdt:anyAtomicType*
The "item()*" means that data takes a sequence of 0-n items. "item()?" means that string only takes a sequence of 0-1 item. Let's fix it then.
SELECT @x.query('string(/age[1])')
Cool. Here's the test for comprehension. Let's try this with typed XML.
— start with a schema collection
CREATE XML SCHEMA COLLECTION ages AS
'<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="urn:ages"
xmlns:tns="urn:ages">
<xs:element name="age" type="xs:int"/>
</xs:schema>
'
GO
DECLARE @x xml(ages)
SET @x = '<age xmlns="urn:ages">12</age>'
— fails as expected
SELECT @x.query('
declare default namespace "urn:ages";
/age/text()')
GO
DECLARE @x xml(ages)
SET @x = '<age xmlns="urn:ages">12</age>'
— works as expected
SELECT @x.query('
declare default namespace "urn:ages";
data(/age)')
GO
DECLARE @x xml(ages)
SET @x = '<age>12</age>'
— fails ??!
SELECT @x.query('string(/age)')
GO
Why does the last query (against strongly typed XML) fail, even though there is a schema? How can you fix it? There are two different "right answers".