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)')), '')

Answer 1

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

Answer 2

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;

Answer 3

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.

Answer 4

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.

Answer 5

You can't have arrays that change shape like that. The "dynamic" shape you found in the docs refers to setup-time variation. Once setup has been finished though, sizes are fixed. So we need a way for you arrays to be of a fixed size.

If you really must re-mesh every time (which I don't recommend) then there are two possible solutions I can think of:

1. Over-allocation
2. Constraint Aggregation

Option 1 -- Over Allocation

This topic is covered in detail in this related question, but briefly what you could do is allocate an array big enough that you always have enough space. Then you can use one entry of the array to record how many active entries are in it. Any non-active entries would be set to a default value that won't violate your constraints.

You'll have to be very careful with the way you define the derivatives. For active array entries, the derivatives come from NASTRAN. For inactive ones, you could set them to 0 but note that you are creating a discrete discontinuity when an entry switches to active. This may very well give the optimizer fits when its trying to converge and derivatives of active constraints keep flipping between 0 and nonzero values.

I really don't recommend this approach, but if you absolutely must have "variable size" arrays then over-allocation is your best best.

Option 2 -- Constraint Aggregation

They key idea here is to use an aggregation function to collapse all the stress constraints into a single value. For structural problems this is most often done with a KS function. OpenMDAO has a KScomponent in its standard library that you can use.

The key is that this component requires a constant sized input. So again, over-allocation would be used here. In this case, you shouldn 't track the number of active values in the array, because you are passing that to the aggregation function. KS functions are like smooth max functions, so if you have a bunch of 0's then it shouldn't affect it.

Your problem still has a discontinuous operation going on with the re-meshing and the noisy constraint array. The KS function should smooth some of that, but not all of it. I still think you'll have trouble converging, but it should work better than raw over-allocation.

Option 3 --- The "right" answer Find a way to fix your grid, so it never changes. I know this is hard if you're using VSP to generate your discritizations, and letting NASTRAN re-grid things from there ... but its not impossible at all.

OpenVSP has a set of geometry-query functions that can be used to back-fit fixed meshes into the parametric space of the geometry. If you do that, then you can regenerate the geometry in VSP and use the parametric space to move your fixed grids with it. This is how the pyGeo tool that the University of Michigan MDO Lab does it, and it works very well.

Its a modest amount of work (though a lot less if you use pyGeo directly), but I think its well worth it. You'll get faster components and a much more stable optimization.