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".
Try this:
DECLARE @x xmlSET @x = '<age>12</age>'-- works as expectedSELECT @x.query('data(/age)')GO
DECLARE @x xmlSET @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 expectedSELECT @x.query('declare default namespace "urn:ages";/age/text()')GO
DECLARE @x xml(ages)SET @x = '<age xmlns="urn:ages">12</age>'-- works as expectedSELECT @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".
Theme design by Jelle Druyts
Pick a theme: BlogXP sqlx BlogXP sqlx
Powered by: newtelligence dasBlog 2.0.7226.0
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.
© Copyright 2008, Bob Beauchemin
E-mail