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: stackoverflow.com/questions/13234175/… — Prabhat G
– Prabhat G, Commented Feb 15, 2018 at 6:46
@PrabhatG you mean this?select @rejectpart.value('(/Rejectedparameters/parameter/name){sql:variable("@id")}', 'varchar(max)') ,i get same error — Ehsan Akbar
– Ehsan Akbar, Commented Feb 15, 2018 at 6:48
Can you also put your xml here? From what I can see, you need something like [ID=sql:variable("@id")] — Prabhat G
– Prabhat G, Commented Feb 15, 2018 at 7:01
I know...I am trying as well. Put up your xml in question please? — Prabhat G
– Prabhat G, Commented Feb 15, 2018 at 7:11

Prabhat G · Accepted Answer · 2018-02-15 07:33:00Z

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);

har07 · Accepted Answer · 2018-02-15 07:53:53Z

2

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]

answered Feb 15, 2018 at 7:53

har07

89.5k12 gold badges87 silver badges143 bronze badges

Collectives™ on Stack Overflow

append SQL variable into query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related