The argument 1 of the XML data type method "value" must be a string literal

Question

I've read through SO: XML data type method “value” must be a string literal but my problem's a bit different. I have a bit of xml in a variable I want to pick apart and am given a path. originally I tried this:

declare @x xml
select @x = '....'
select @x.value('(' + @path + ')[1]', 'varchar(max)')

but, of course, that fails. then I found the sql:variable and tried this:

select @x.value('(sql:variable("@path"))[1]', 'varchar(max)')

but that curiously returns the value of @path (why?). I've been messing with it but can't get it to do the right thing.

Thoughts anyone?

Solution 1

with help from wBob on the Microsoft site, I've now got a clean solution. Performance is, of course, a concern as the whole document will get mapped for the sake of a single path but improvements are left as suggestion possibilities for the reader :)

if object_id('VMConfigVal') is not null
drop function VMConfigVal
go
create function VMConfigVal(@x xml, @path varchar(max))
returns nvarchar(max)
as
begin
    declare @ret nvarchar(max)

    ;with cte as
    (
    select  value = x.c.value('.', 'varchar(50)')
    ,       path = cast ( null as varchar(max) )
    ,       node = x.c.query('.')
    from    @x.nodes('/*') x(c)
    union all
    select  n.c.value('.', 'varchar(50)')
    ,       isnull( c.path + '/', '/' )
        +       n.c.value('local-name(.)', 'varchar(max)')
    ,       n.c.query('*')
    from    cte c
    cross   apply c.node.nodes('*') n(c)
    )
    select @ret = value from cte where path = @path
    return @ret
    end
go

so I can now do something like:

select dbo.VMConfigVal(MyXMLConfig, '/hardware/devices/IDE/ChannelCount')
from someTable

sweet!

Solution 2

Your select returns the value of @path because sql:variable() returns a literal value, so in effect you're asking SQL server to select the literal value @path from the document, which it does. The only way I know of doing what you want would be using dynamic SQL, like so:

declare @xml xml = '
<root>
    <element attr="test">blah</element>
</root>';

declare @p nvarchar(max) = '(//element/text())[1]';
declare @sql nvarchar(max) 
    = 'select @x.value(''' + @p + ''', ''nvarchar(max)'')';

exec sp_executesql @sql, @parameters = N'@x xml', @x = @xml;

But I should warn you that this is not very good practice (think about SQL injections, validating input, etc.)

Solution 3

If you only need to find a child element by name and want to abstract the name from the XPath literal here are some options:

// Returns the /root/node/element/@Value with @Name contained in @AttributeName SQL variable.
SELECT @Xml.value('(/root/node/element[@Name=sql:variable("@AttributeName")]/@Value)[1]', 'varchar(100)')

// Returns the text of the child element of /root/node with the name contained in @ElementName SQL variable.
SELECT @Xml.value('(/root/node/*[name(.)=sql:variable("@ElementName")]/text())[1]', 'varchar(100)')

// Searching the xml hierarchy for elements with the name contained in @ElementName and returning the text().
SELECT @Xml.value('(//*[name(.)=sql:variable("@ElementName")]/text())[1]', 'varchar(100)')

You need to declare @ElementName or @AttributeName SQL variable to run these. I tested the first statement, but haven't explicitly tested the other 2 statements, FYI.

Comments

[ekkis]

I'm out of luck then because I'm trying to do this within an UDF. grrr... (thanks)

[ekkis]

hmm... alternatively, I can do something like /my/path/mytag[@someattr=sql:variable("@myattr")]... so how could I create a query that looks for a given path/tag instead of looking for an attribute in the tag?

[Saulius Valatka]

You can do this: select @xml.value('(//*[local-name() = sql:variable("@elementname")])[1]', 'nvarchar(max)'), with @elementname = 'element'. Maybe with some hacking you can get it to look up by path, but I can't tell you for sure without knowing more details.

[ekkis]

I was halfway there but got stuck on figuring out what the path of a node is. I posted here for it: stackoverflow.com/questions/12414233/…

[ekkis]

I've unchecked your reply as the answer because even though you answered my question regarding why the value of @path is returned, the core question remained unanswered. However, I've got a solution I've posted here as the answer. you get upvotes for your replies though. thanks a bunch.

[ekkis]

but the point was that I didn't want to hard-code the path to the element

[edhubbell]

I had better luck with local-name() than name(.) using the format suggested in the 2nd statement. This format is a whole lot nicer & neater than using cross apply, that's for sure.