append SQL variable into query

Question

I want to append my Id (While Counter) into my XML Query as you can see :

declare @id int
declare @rejectpart xml 
set @id=0
while  (@id <12)
begin

 select @rejectpart.value('(/Rejectedparameters/parameter/name)[sql:variable("@id")]', 'varchar(max)') 
begin
select 0
end
set @id=@id+1
end

But when i want to run my query in get this error :

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

My input XML:

<Rejectedparameters>
  <parameter>
    <name>CO</name>
    <value>8.72</value>
  </parameter>
  <parameter>
    <name>CO2</name>
    <value>6.49</value>
  </parameter>
  <parameter>
    <name>HC</name>
    <value>659</value>
  </parameter>
  <parameter>
    <name>O2</name>
    <value>1.5</value>
  </parameter>
  <parameter>
    <name>BackRightBrake</name>
    <value>1.35</value>
  </parameter>
  <parameter>
    <name>BackLeftBrake</name>
    <value>0.63</value>
  </parameter>
  <parameter>
    <name>BackBrake</name>
    <value>53.33</value>
  </parameter>
  <parameter>
    <name>CarBody</name>
    <value>0</value>
  </parameter>
  <parameter>
    <name>SoundStandard</name>
    <value>0</value>
  </parameter>
  <parameter>
    <name>NoSmoke</name>
    <value>0</value>
  </parameter>
</Rejectedparameters>

not sure if its constructive, but use `{` instead of `[` and check? source: https://stackoverflow.com/questions/13234175/use-of-xml-modify-to-insert-parameters-into-specific-element-of-an-xml-column — Prabhat G, Feb 15 '18 at 06:46
@PrabhatG you mean this?select @rejectpart.value('(/Rejectedparameters/parameter/name){sql:variable("@id")}', 'varchar(max)') ,i get same error — Ehsan Akbar, Feb 15 '18 at 06:48
Can you also put your xml here? From what I can see, you need something like `[ID=sql:variable("@id")]` — Prabhat G, Feb 15 '18 at 07:01
I know...I am trying as well. Put up your xml in question please? — Prabhat G, Feb 15 '18 at 07:11
Possible duplicate of [XQuery \[value()\]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic \*'](https://stackoverflow.com/questions/19940566/xquery-value-value-requires-a-singleton-or-empty-sequence-found-oper) — Paul Karam, Feb 15 '18 at 07:28
I just flagged this as a duplicate, because I did a search for your error and got the question on SO, plus the answer there worked well with your sample data. — Paul Karam, Feb 15 '18 at 07:30
@EhsanAkbar: check my solution below. i've mentioned 2 approaches — Prabhat G, Feb 15 '18 at 07:33

Prabhat G · Accepted Answer · 2018-02-15T07:33:00.213

solution(approach1):

I guess this is how your solution should be:

select @rejectpart.value('(/Rejectedparameters/parameter/name/@id)[1]', 'varchar(max)')

approach2

While I am looking for resolution, are you expecting something like this? Run it. This will allow me to better understand your issue.

declare @xmldata xml;
set @xmldata = 
'<Rejectedparameters>
  <parameter>
    <name>CO</name>
    <value>8.72</value>
  </parameter>
  <parameter>
    <name>CO2</name>
    <value>6.49</value>
  </parameter>
  <parameter>
    <name>HC</name>
    <value>659</value>
  </parameter>
  <parameter>
    <name>O2</name>
    <value>1.5</value>
  </parameter>
  <parameter>
    <name>BackRightBrake</name>
    <value>1.35</value>
  </parameter>
  <parameter>
    <name>BackLeftBrake</name>
    <value>0.63</value>
  </parameter>
  <parameter>
    <name>BackBrake</name>
    <value>53.33</value>
  </parameter>
  <parameter>
    <name>CarBody</name>
    <value>0</value>
  </parameter>
  <parameter>
    <name>SoundStandard</name>
    <value>0</value>
  </parameter>
  <parameter>
    <name>NoSmoke</name>
    <value>0</value>
  </parameter>
</Rejectedparameters>'

select 
  pd.value('name[1]','char(11)'),
  pd.value('value[1]','char(11)')
from @xmlData.nodes('//Rejectedparameters/parameter')  as i(pd);

score 2 · Answer 2 · answered Feb 15 '18 at 07:53

SQL Server can't be sure that your XQuery returns singleton. You can wrap the entire expression in (...)[1] to fix it, so the XQuery become like the following :

((/Rejectedparameters/parameter/name)[sql:variable("@id")])[1]

But that nested parentheses is confusing, and setting position predicate on parameter makes it look a bit cleaner :

(/Rejectedparameters/parameter[sql:variable("@id")]/name)[1]

append SQL variable into query

2 Answers2