using variable in the xpath for xquery (value)

Question

set @xml = '
<body>
    <Record>
        <A>a</A>
        <B>b</B>
        <C>c</C>
    </Record>
    <Record>
        <A>d</A>
        <B>e</B>
        <C>f</C>
    </Record>
    <Record>
        <A>g</A>
        <B>h</B>
        <C>i</C>
    </Record>
</body>
'

i'm trying to use this syntax but doesn't work

i would like to use variable with xpath in the xquery (value)

SET @xmlQuery = '/Record[2]/A'

set @nodevalue =  (@xml.value('(//*[local-name()=sql:variable("@xmlQuery")])/text())[1]', 'nvarchar(50)'))

what is wrong?

...is this something like Microsoft's bastardization of XQuery, rather than the real/legitimate language as defined by the standard? (If you're asking things that depend on a specific vendor implementation, please tag appropriately). — Charles Duffy, Sep 08 '16 at 21:51
...I mean, XQuery variables have names like `$foo`, not `@foo`, and the syntax for defining a variable uses `declare`, not `set`, and `nvarchar` isn't a valid data type there. Which is to say -- this doesn't look like XQuery at all. — Charles Duffy, Sep 08 '16 at 21:54
in reality the value of @xmlQuery comes from a table. in short i would get the value using variable if possible instead of using literal string, i know that could be achieved using dynamic sql but i don't know how to assign the value coming from the exec sp_executesql. please see http://stackoverflow.com/questions/39399317/save-result-of-exec-sp-excutequery-using-xpath — zanza67, Sep 08 '16 at 21:55
Okay, so yes, this is Microsoft SQL Server. Should tag this question that way too. — Charles Duffy, Sep 08 '16 at 21:56
(...and SQL Server only supports "a subset of" XQuery, which explains why it didn't look like the real language to me). — Charles Duffy, Sep 08 '16 at 21:57
so you mean that is not possible to achieve what i would like to do? — zanza67, Sep 08 '16 at 22:00
I don't know if it's possible. I know standard XQuery (particularly the 3.0 version of the language). I don't know SQL Server (which implements only a subset of the 1.0 version). All I was saying, above, is that your question should be asked with tagging (if not content) that makes it clear that you're asking about SQL Server, not about XQuery in general, since the SQL Server version of XQuery and the standard version are very different things. — Charles Duffy, Sep 08 '16 at 22:02
would you mind to check the other question i made ? http://stackoverflow.com/questions/39399317/save-result-of-exec-sp-excutequery-using-xpath — zanza67, Sep 08 '16 at 22:07

score 0 · Answer 1 · answered Sep 09 '16 at 03:23

There is no dynamic path capability in SQL Server XML. Your options are

Split the path into separate components, such as:

declare @xml xml;

declare @Node1 sysname = N'Record', @Node2 sysname = N'A', @Node1Number int = 2;

set @xml = '<body>
    <Record>
        <A>a</A>
        <B>b</B>
        <C>c</C>
    </Record>
    <Record>
        <A>d</A>
        <B>e</B>
        <C>f</C>
    </Record>
    <Record>
        <A>g</A>
        <B>h</B>
        <C>i</C>
    </Record>
</body>';

select @xml.value('(/body/
*[local-name() = sql:variable("@Node1") and position() = sql:variable("@Node1Number")]/
*[local-name() = sql:variable("@Node2")]/
text())[1]', 'nvarchar(50)');

However, if your path comes as a whole string and you don't want to write a parser, the only option is a dynamic SQL:

declare @Path nvarchar(max) = N'/body/Record[2]/A', @Sql nvarchar(max);

set @Sql = N'select t.c.value(''./text()[1]'', ''nvarchar(50)'') from @xml.nodes('
+ quotename(@Path, '''')
+ ') t(c);'

exec sys.sp_executesql @Sql, N'@xml xml', @xml = @xml;

Personally, I don't like either of them, and would recommend to rethink the whole task. Chances are, there is a better solution than dynamically querying XML on the database side.

thank you Roger, i'm green bean in sql so would you mind to suggest a better solution please? — zanza67, Sep 09 '16 at 08:04
@zanza67, I mean that better solution should lie outside of SQL (most probably). Whether such a solution exists, however, depends on your task' details. — Roger Wolf, Sep 09 '16 at 08:57

using variable in the xpath for xquery (value)

1 Answers1