修改(替换)XML 条件 [英] modify(replace) XML for conditions

问题描述

我想替换元素标签中的值，取决于它的值，以及另一个元素的值(与所述元素在同一级别)，两个元素都在同一个父元素标签内(每个父元素)标签是独一无二的，因为它有自己的 ID 属性).我想在存储过程中对这个 XML 变量的不同位置进行更改.

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.

作为第一个计时器，我一直在思考如何修改整个 xml 中的元素.这两个元素都存在于整个文档的同一个父元素中，并且这些父标签中的每一个都有一个唯一的 ID 属性.

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.

任何建议都会有很大帮助.有关如何混合和匹配value()"、modify()"等文档的链接也会有所帮助.

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

我想将格式值为n0"且数据类型为Float"的所有属性的格式值n0"替换为f2".

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

-谢谢

推荐答案

在 SQL Server 中无法同时替换 xml 中的多个值，有几个选项:

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

• 使用循环并一一更新属性
• 将数据拆分成临时表/表变量，更新后合并合并成一个 xml
• 使用 xquery 重建 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 小提琴演示

如果您的 xml 包含命名空间，我发现最简单的更新方法是声明 default 命名空间:

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

这篇关于修改(替换)XML 条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！