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

Question

If i pass @count variable i am getting this error

Below is my query

DECLARE @Error_Description NVARCHAR(Max)
DECLARE @Count VARCHAR(20)
DECLARE @x NVARCHAR(Max)

SELECT @Error_Description = 'The external columns for Excel Source are out of synchronization with the data source columns. 
The column "szReferencceNumber" needs to be added to the external columns.
The column "SMSa" needs to be added to the external columns.
The column "as" needs to be added to the external columns.'

SELECT @Count = (LEN(@Error_Description) - LEN(REPLACE(@Error_Description, '"', ''))) / LEN('"')

SELECT @Count

SELECT COALESCE(LTRIM(CAST(('<X>' + REPLACE(@Error_Description, '"', '</X><X>') + '</X>') AS XML).value('(/X)[' + @Count + ']', 'varchar(128)')), '')

@Larnu I need the String in between quotes. if i run this query it is wrking fine. `SELECT COALESCE(LTRIM(CAST((''+REPLACE(@Error_Description,'"' ,'')+'') AS XML).value('(/X)[2]', 'varchar(128)')), '')` — Subhash Manikanta Kumar Mogili, Dec 30 '19 at 11:22
I replicated the issue as @Larnu Do you prefer storing data in a table variable. then we can use cross apply for this. — kiran gadhe, Dec 30 '19 at 11:36
As the error message says the first parameter needs to be a string literal. Not an expression such as `'(/X)[' + @Count + ']'` - so you could do this by constructing the whole SQL string dynamically and concatenating the `@Count` in - but if you explain your actual end requirement of what you are trying to do there is probably a better way — Martin Smith, Dec 30 '19 at 11:38
There are multiple strings inside quotes (`"`) though @SubhashManikantaKumarMogili . You don't explain what your *real* goal is, so it's much harder for us to help you. — Thom A, Dec 30 '19 at 11:39
@Larnu I need these words "szReferencceNumber", "SMS", "as" from the above string into column-wise dynamically. I have tried hardcoded way using below query `SELECT COALESCE(LTRIM(CAST((''+REPLACE(@Error_Description,'"' ,'')+'') AS XML).value('(/X)[2]', 'varchar(128)')), '') AS Column1, COALESCE(LTRIM(CAST((''+REPLACE(@Error_Description,'"' ,'')+'') AS XML).value('(/X)[4]', 'varchar(128)')), '') AS Column2, COALESCE(LTRIM(CAST((''+REPLACE(@Error_Description,'"' ,'')+'') AS XML).value('(/X)[6]', 'varchar(128)')), '') AS Column3` — Subhash Manikanta Kumar Mogili, Dec 30 '19 at 11:59
Will your data type always be a `MAX`, or would an `nvarchar(4000)` be sufficient? What version of SQL Server are you using? — Thom A, Dec 30 '19 at 12:00

score 0 · Answer 1 · answered Dec 30 '19 at 12:01

The first parameter to value must be a string literal. To select the nodes with a dynamic index you can do the following

SELECT 
n.value('.', 'varchar(128)') as Result
from (SELECT CAST(('<X>' + REPLACE(@Error_Description, '"', '</X><X>') + '</X>') AS XML)) ca(x)
CROSS APPLY x.nodes('(/X)') n(n)
WHERE n.value('for $l in . return count(../*[. << $l]) + 1', 'int') %2 = 0

This returns the value for every second node. So achieves your desired results of getting the values enclosed in quotes.

Result
---------------------
szReferencceNumber
SMSa
as

score 0 · Answer 2 · answered Dec 30 '19 at 12:09

if you're using 2012+, and you can use nvarchar(4000) (not MAX), you could get a copy of DelimitedSplitN4K_LEAD and grab rows where the value of ItemNumber is even:

DECLARE @Error_Description nvarchar(4000);

SELECT @Error_Description = N'The external columns for Excel Source are out of synchronization with the data source columns. 
The column "szReferencceNumber" needs to be added to the external columns.
The column "SMSa" needs to be added to the external columns.
The column "as" needs to be added to the external columns.';

SELECT DS.Item
FROM dbo.DelimitedSplitN4K_LEAD(@Error_Description,'"') DS
WHERE DS.ItemNumber % 2 = 0;

If you're on SQL server 2016+, then you could use some JSON manipulation (which supports MAX values):

SELECT OJ.value
FROM (VALUES(@Error_Description))V(Error_Description)
     CROSS APPLY (VALUES('["' + REPLACE(REPLACE(REPLACE(V.Error_Description,'"','","'),NCHAR(13),''),NCHAR(10),'')+ '"]'))R(JSON)
     CROSS APPLY OPENJSON(R.JSON) OJ
WHERE OJ.[Key] % 2 = 1;

score 0 · Answer 3 · answered Jan 14 '20 at 10:32

You can use your @Count within the XQuery predicate, but not via concatenation. There is sql:variable():

TheXml.value('(/X)[sql:variable("@Count") cast as xs:int?][1]', 'varchar(128)')

It would help to declare the variable @Count as INT in order to avoid the XQuery cast.

Hint: You need the final [1] to enforce the singleton .value() demands for.

score 0 · Answer 4 · answered May 19 '22 at 20:45

this is all based on the @Shnugo answer above, thanks a lot Shnugo

I have a long script saved in to a temp table

select * from #Radhe

I want to print the whole script.

DECLARE @SQL NVARCHAR(MAX)
DECLARE @XML3 XML
--load the script to XML
SELECT @XML3 = (SELECT #Radhe.Item AS x FROM #Radhe FOR XML PATH(''))


--print line by line
declare @i int = 1
select @sql = 'radhe'

while @sql is not null
   begin
      SELECT  @sql = @xml3.value('(/x/text())[sql:variable("@i") 
                     cast as xs:int?][1]', 'varchar(max)')
      print @sql
      select @i = @i + 1
      if @i > 10000 --limit it to 10000 lines 
         set @sql = null
  end

and it works. It took me a long time to get this done. Hope I can help a fellow DBA or developer.

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

4 Answers4

Linked