CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
dance2die
dance2die

Reputation: 36925

What is the type of XQuery passed to SQL Server XML Data Type methods?

According to BOL on value() Method (xml Data Type),

value() method takes two arguments

Do I need to pass varchar or nvarchar to value()? How can I find out what kind of type XQuery or SQLType expects?

End Goal: To create utility UDF/sprocs that uses XML Data Type methods.

Upvotes: 1

Views: 343

Answers (1)

Quassnoi
Quassnoi

Reputation: 425633

It works with both:

WITH    q AS
        (
        SELECT  CAST('<root><node>1</node></root>' AS XML) AS doc
        )
SELECT  doc.value('(/root/node)[1]', 'INT'),
        doc.value(N'(/root/node)[1]', 'INT')
FROM    q

Note that the XQuery is compiled during the parsing stage, i. e. you can supply only the string literal as first argument (not a column, expression or a variable).

This string literal has no "type", since type assumes a set of possible values that are known only at runtime, and XML functions accept only literal XQuery expressions which should be known at compile time.

You cannot pass them from the arguments or your functions or as variables, you can only hardcode them into the query.

Treat them as reserved words (like SELECT or UPDATE), which for some reason should be enclosed into single quotes.

You should build the whole query dynamically if you want the XQuery to be dynamic.

Upvotes: 1

Related Questions