sql - modify(replace) XML for conditions

Question

I want to replace the value in an element tag, depending on its value, as well as the value of another element(in the same level as said element), both elements being inside the same parent element tag everywhere(Every parent tag is unique due to its own ID attribute). I want to do the change at various locations of this XML variable, in a stored procedure.

Being a first timer at this, I am stuck with how i could modify the elements throughout the xml. Both elements are present in the same parent element all over the document, and every one of these parent tags has a unique ID attribute.

Any suggestions would be of great help. Links to documents on how to mix and match "value()", "modify()", etc will also help.

DECLARE @xml xml = '
<SemanticModel xmlns="schemas.microsoft.com/sqlserver/2004/10/semanticmodeling"; xmlns:xsi="w3.org/2001/XMLSchema-instance"; xmlns:xsd="w3.org/2001/XMLSchema"; ID="G1">
    <Entities>
        <Entity ID="E1">
            <Fields>
                <Attribute ID="A1">
                    <Name>AAAA</Name>
                    <DataType>Float</DataType>
                    <Format>n0</Format>
                    <Column Name="AAAA_ID" />
                </Attribute>
                <Attribute ID="A2">
                    <Name>BBBB</Name>
                    <DataType>Integer</DataType>
                    <Format>n0</Format>
                    <Column Name="BBBB_ID" />
                </Attribute>
                <Attribute ID="A3">
                    <Name>KKKK</Name>
                    <Variations>
                        <Attribute ID="A4">
                            <Name>CCCC</Name>
                            <DataType>Float</DataType>
                            <Format>n0</Format>
                        </Attribute>
                        <Attribute ID="A5">
                            <Name>AAAA</Name>
                            <DataType>Float</DataType>
                            <Format>n0</Format>
                        </Attribute>
                    </Variations>
                    <Name>AAAA</Name>
                    <DataType>Float</DataType>
                    <Format>n0</Format>
                </Attribute>
            </Fields>
        </Entity>
    </Entities>'
DECLARE @i int = 0
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling' as dm, 'http://schemas.microsoft.com/analysisservices/2003/engine' as dsv, 'http://w3.org/2001/XMLSchema' as xs )
select @i = @xml.value('count(//dm:Attribute[dm:DataType="Float" and dm:Format="n0"]/dm:Format)', 'int')
select @i
while @i > 0
begin
    set @xml.modify('
         replace value of
             (//dm:Attribute[dm:DataType="Float" and dm:Format="n0"]/dm:Format/text())[1]
         with "f2"
    ')

    set @i = @i - 1
end
select @xml

I want to replace Format value "n0" to "f2" for all attributes whose Format value is"n0" and DataType is "Float".

-Thankyou

Answer 1

it's not possible to replace multiple values at once in xml in SQL Server, there're a several options:

• use loop and update attributes one by one
• split data into temporary table/table variable, update and then merge into one xml
• use xquery to rebuild xml

I think correct way for you would be loop solution:

select @i = @data.value('count(//Attribute[DataType="Float" and Format="n0"]/Format)', 'int')

while @i > 0
begin
    set @data.modify('
         replace value of
             (//Attribute[DataType="Float" and Format="n0"]/Format/text())[1]
         with "f2"
    ')

    set @i = @i - 1
end

sql fiddle demo

---

If your xml contains namepaces, simplest way to update I found would be to declare default namespace:

;with xmlnamespaces(default 'schemas.microsoft.com/sqlserver/2004/10/semanticmodeling')
select @i = @xml.value('count(//Attribute[DataType="Float" and Format="n0"]/Format)', 'int')

while @i > 0
begin
    set @xml.modify('
         declare default element namespace "schemas.microsoft.com/sqlserver/2004/10/semanticmodeling";
         replace value of
             (//Attribute[DataType="Float" and Format="n0"]/Format/text())[1]
         with "f2"
    ')

    set @i = @i - 1
end
select @xml