Dynamic sql from an xml
-
Hi,
I create a dynamic query based on a xml.
DECLARE @x AS XML = '<A id="xy" />';
DECLARE @q as nvarchar(4000);
DECLARE @x AS XML = '<A id="a" />';
SET @q = (SELECT 'WHERE ' + T.c.value('@id', 'nvarchar') + ' > b'
FROM@x.nodes('//A') AS T(c)
FOR XML PATH ('')
);
PRINT @q;
The value of the variable @q is:
WHERE a & gt; b
instead of
WHERE a > b
.
How can I make the variable @q to have the right value:
WHERE a > b
?
Thanks,
ioani
-
I don't understand why you need FOR XML PATH in the assignment of q. Q isn't an XML variable and it doesn't appear you need an XML output, therefore the answer is simply to take it out:
SET @q = (SELECT 'WHERE ' + T.c.value('@id', 'nvarchar') + ' > b'FROM @x.nodes('//A') AS T(c)
);
FOR XML PATH outputs the result set as XML (including character encoding)
-
SET @q = ((SELECT 'WHERE ' + T.c.value('@id', 'nvarchar') + ' > b'
FROM @x.nodes('//A') AS T(c)
FOR XML PATH (''),TYPE).value('./text()[1]','varchar(100)')
);
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
It works.
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply