CODEWITHSUNDEEP
sql-serverxml
Ehsan Akbar
Ehsan Akbar

Reputation: 7301

append SQL variable into query

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>

Upvotes: 0

Views: 432

Answers (2)

har07
har07

Reputation: 89285

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]

Upvotes: 2

Prabhat G
Prabhat G

Reputation: 3029

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

Upvotes: 2

Related Questions