Looping thru xml then updating xml variable in SQL

Question

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

Hi Aedz Migraso. It is perfectly OK that you accepted har07's answer as it exactly answered your question. There's only one tiny hint I'd like to give you for future issues: avoid loops (google about RBAR, procedural thinking and row based / set based thinking)! — Shnugo, Apr 07 '16 at 09:06

Shnugo · Answer 1 · 2016-04-06T15:12:55.497

Quick and dirty :-)

SELECT CAST(REPLACE(CAST(@x AS VARCHAR(MAX)),' rftID="',' rftID="q')  AS XML);

And here's a clean approach:

DECLARE @x  XML='<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>';

SELECT
(
    SELECT 'q' + R.value('@rfqID','varchar(max)') AS [@rfqID]
          ,R.value('@rftID','int') AS [@rftID]
          ,R.value('@questionDesc','varchar(max)') AS [@questionDesc]
          --other attributes similar    
    FROM @x.nodes('/root/TemplateQuestion/Row') AS A(R)
    FOR XML PATH('Row'),ROOT('TemplateQuestion'),TYPE
)
FOR XML PATH('root');

score 3 · Accepted Answer · answered Apr 06 '16 at 11:51

"How can I replace the [1] to the variable @i? I get some error with string literals when I try to use sql: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")
')

Looping thru xml then updating xml variable in SQL

2 Answers2