Guidelines for Using xml Data Type Methods

The following list contains guidelines for using the xml data type methods:

  • The xml data type methods cannot be used in the PRINT statement as shown in the following example. The xml data type methods are treated as subqueries, and subqueries are not allowed in the PRINT statement. As a result, the following example returns an error:
    DECLARE @x xml
    SET @x = '<root>Hello</root>'
    PRINT @x.value('/root[1]', 'varchar(20)') -- will not work because this is treated as a subquery (select top 1 col from table) 
    A solution is to first assign the result of the value() method to a variable of xml type and then specify the variable in the query.
    DECLARE @x xml
    DECLARE @c varchar(max)
    SET @x = '<root>Hello</root>'
    SET @c = @x.value('/root[1]', 'varchar(11)')
    PRINT @c                                                      
  • The xml data type methods are treated internally as subqueries. Because GROUP BY requires a scalar and does not allow aggregates and subqueries, you cannot specify the xml data type methods in the GROUP BY clause. A solution is to call a user-defined function that uses XML methods inside of it.
  • When reporting errors, xml data type methods raise a single error in the following format:
    Msg errorNumber, Level levelNumber, State stateNumber:
    XQuery [database.table.method]: description_of_error
    For example:
    Msg 2396, Level 16, State 1:
    XQuery [xmldb_test.xmlcol.query()]: Attribute may not appear outside of an element
  • The exist() method returns 1 for the XQuery expression that returns a nonempty result. If you specify the true() or false() functions inside the exist() method, the exist() method will return 1, because the functions true() and false() return Boolean True and False, respectively. That is, they return a nonempty result). Therefore, exist() will return 1 (True), as shown in the following example:
    declare @x xml
    set @x=''
    select @x.exist('true()') 

Community Additions