前端之家

  1. 首页
  2. 问答

T-SQL：通过搜索同级（对等）来更新特定的XML节点

• 问答

我有如下所示的XML，它们表示SQL表数据：

<Project>
    <DataSource version="4" type="LiveDatabaseSource">
        <ServerName>(local)</ServerName>
    </DataSource>
    <Tables>
        <value>
            <TableType>Generated</TableType>
            <PopulationDetails version="2" type="PopulationDetails">
                <PopulationType>RowCount</PopulationType>
                <RowCount>10000</RowCount>
                <ProportionTableExists>False</ProportionTableExists>
                <Proportion>0</Proportion>
                <TimeToPopulate>0</TimeToPopulate>
            </PopulationDetails>
            <Name>Table1</Name>
            <Schema>dbo</Schema>
        </value>
        <value>
            <TableType>Generated</TableType>
            <PopulationDetails version="2" type="PopulationDetails">
                <PopulationType>RowCount</PopulationType>
                <RowCount>10000</RowCount>
                <ProportionTableExists>False</ProportionTableExists>
                <Proportion>0</Proportion>
                <TimeToPopulate>0</TimeToPopulate>
            </PopulationDetails>
            <InvalidRowBehaviour>SkipRow</InvalidRowBehaviour>
            <Included>False</Included>
            <Append>False</Append>
            <Name>Table2</Name>
            <Schema>dbo</Schema>
        </value>
    </Tables>
</Project>

这位于SQL Server的单个XML列中，还有更多<value>条目代表数据库中的表。我需要通过搜索PopulationDetails.RowCount中的值来更新Name：即，我想使用T-SQL将Table2更新为60,000的行数。

我可以通过Name找到正确的节点，但是在更新其对等PopulationDetails.Name节点中的值时遇到了麻烦。到目前为止，我所拥有的只是更新第n条记录的能力：

UPDATE dbo.SQLGenXML
SET GenXML.modify('replace value of 
(/Project/Tables/value/PopulationDetails/RowCount/text())    [1] with ("60000")')
WHERE id = 1;

有很多示例可以根据属性查找节点并更新值，但是这种类型的对等级别搜索（或者我的google-foo很烂）

gaochong0908 回答：T-SQL：通过搜索同级（对等）来更新特定的XML节点

这是您要找的东西。

  

SQL 

-- DDL and sample data population,start
DECLARE @SQLGenXML TABLE (ID INT IDENTITY PRIMARY KEY,GenXML XML);
INSERT INTO @SQLGenXML (GenXML)
VALUES
(N'<Project>
    <DataSource version="4" type="LiveDatabaseSource">
        <ServerName>(local)</ServerName>
    </DataSource>
    <Tables>
        <value>
            <TableType>Generated</TableType>
            <PopulationDetails version="2" type="PopulationDetails">
                <PopulationType>RowCount</PopulationType>
                <RowCount>10000</RowCount>
                <ProportionTableExists>False</ProportionTableExists>
                <Proportion>0</Proportion>
                <TimeToPopulate>0</TimeToPopulate>
            </PopulationDetails>
            <Name>Table1</Name>
            <Schema>dbo</Schema>
        </value>
        <value>
            <TableType>Generated</TableType>
            <PopulationDetails version="2" type="PopulationDetails">
                <PopulationType>RowCount</PopulationType>
                <RowCount>10000</RowCount>
                <ProportionTableExists>False</ProportionTableExists>
                <Proportion>0</Proportion>
                <TimeToPopulate>0</TimeToPopulate>
            </PopulationDetails>
            <InvalidRowBehaviour>SkipRow</InvalidRowBehaviour>
            <Included>False</Included>
            <Append>False</Append>
            <Name>Table2</Name>
            <Schema>dbo</Schema>
        </value>
    </Tables>
</Project>');
-- DDL and sample data population,end

UPDATE @SQLGenXML
SET GenXML.modify('replace value of 
(/Project/Tables/value[Name="Table2"]/PopulationDetails/RowCount/text())[1] with ("60000")');

-- test
SELECT * FROM @SQLGenXML;
sql-serverxml
本文链接：https://www.f2er.com/3156259.html

大家都在问