Dynamic sql from an xml

• ioani

  Ten Centuries

  Points: 1295

  More actions

  September 15, 2011 at 4:38 am

  #390413

  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

• HowardW

  One Orange Chip

  Points: 29109

  More actions

  September 15, 2011 at 6:00 am

  #1381867

  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)

• Mark Cowne

  One Orange Chip

  Points: 26981

  More actions

  September 15, 2011 at 6:01 am

  #1381870

  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

• ioani

  Ten Centuries

  Points: 1295

  More actions

  September 15, 2011 at 6:12 am

  #1381877

  It works.

  Thanks