My xml looks like the ff:
<root>
<TemplateQuestion>
<Row rfqID="1" rftID="1" questionDesc="Question 1" responseType="1" rfqDisplayOrder="1" deletedBit="0" />
<Row rfqID="2" rftID="1" questionDesc="Question 2" responseType="2" rfqDisplayOrder="2" deletedBit="0" />
<Row rfqID="3" rftID="1" questionDesc="Question 3" responseType="3" rfqDisplayOrder="3" deletedBit="0" />
</TemplateQuestion>
</root>
Now my goal is to make the rfqID to have the letter "q" before it. So the results should be like the ff:
<root>
<TemplateQuestion>
<Row rfqID="q1" rftID="1" questionDesc="Question 1" responseType="1" rfqDisplayOrder="1" deletedBit="0" />
<Row rfqID="q2" rftID="1" questionDesc="Question 2" responseType="2" rfqDisplayOrder="2" deletedBit="0" />
<Row rfqID="q3" rftID="1" questionDesc="Question 3" responseType="3" rfqDisplayOrder="3" deletedBit="0" />
</TemplateQuestion>
</root>
I am achieving that by doing this:
declare @xml XML
set @xml = (select dbo.udfGetXMLVal(1))
declare @nodeCount int
declare @i int
declare @qid nvarchar(20)
set @i = 1
select @nodeCount = @xml.value('count(/root/TemplateQuestion/Row/@rfqID)','int')
while(@i <= @nodeCount)
begin
select @qid = x.value('@rfqID[1]', 'VARCHAR(20)')
from @xml.nodes('/root/TemplateQuestion/Row[position()=sql:variable("@i")]') e(x)
set @qid = 'q' + @qid
select @qid
Set @xml.modify('replace value of (/root/TemplateQuestion/Row/@rfqID)[1] with sql:variable("@qid")')
set @i = @i + 1
end
Im having problems with this line:
Set @xml.modify('replace value of (/root/TemplateQuestion/Row/@rfqID)[1] with sql:variable("@qid")')
How can I replace the [1] to the variable @i? I get some error with string literals when I try to use sql:variable.
Any help you could provide would be greatly appreciated. Thank you
"How can I replace the
[1]
to the variable@i
? I get some error with string literals when I try to usesql:variable
"
You can do it like this (tested and works in SQL Server 2008R2) :
Set @xml.modify('
replace value of ((/root/TemplateQuestion/Row/@rfqID)[sql:variable("@i")] )[1]
with sql:variable("@qid")
')