sqlsql-serverxmlt-sqlxml-dml

Looping thru xml then updating xml variable in SQL


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


Solution

  • "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")
    ')