Sql Server pass variable to nodes function

Question

After I did some internet research I found out that nodes function for xml processing of Sql Server does not accept a variable as parameter. If I try to use a variable a get the following error:

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

This is what I am trying to do:

SET @xPath = REPLACE(@xPath,'|','/')
SET @xPath = '/' + @xPath + '/text()'
SELECT @ParsedHtml = @ParsedHtml + CONVERT(nvarchar(max),Col.query('.')) + ';' FROM @Html.nodes(@xPath)AS T(Col)

Is there any hack i can do to make nodes function accept a variable as parameter???

Community · Accepted Answer · 2017-05-23 11:58:31Z

2

You can use a variable in the path like this:

SET @xPath = REPLACE(@xPath,'|','/')

set @ParsedHtml = ''
SELECT @ParsedHtml = @ParsedHtml + CONVERT(nvarchar(max),Col.query('.')) + ';' 
FROM @Html.nodes('/*[local-name()=sql:variable("@xPath")]/text()')AS T(Col)

I don't think you can do multiple levels in one variable in the path, which it looks like you are trying to do. You'd have to specify each level with a parameter of it's own. If you need to do a dynamic number of path levels, you might look into using dynamic sql.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Jun 22, 2015 at 20:32

Brian Pressler

6,7332 gold badges21 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sql Server pass variable to nodes function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related