SQL Server 2008 查找和替换 XML 列中的元素

Question

在下表中:

CREATE TABLE [dbo].[GDB_ITEMS](
    [ObjectID] [int] NOT NULL,
    [UUID] [uniqueidentifier] NOT NULL,
    [Type] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](226) NULL,
    [PhysicalName] [nvarchar](226) NULL,
    [Path] [nvarchar](512) NULL,
    [Url] [nvarchar](255) NULL,
    [Properties] [int] NULL,
    [Defaults] [varbinary](max) NULL,
    [DatasetSubtype1] [int] NULL,
    [DatasetSubtype2] [int] NULL,
    [DatasetInfo1] [nvarchar](255) NULL,
    [DatasetInfo2] [nvarchar](255) NULL,
    [Definition] [xml] NULL,
    [Documentation] [xml] NULL,
    [ItemInfo] [xml] NULL,
    [Shape] [geometry] NULL,
 CONSTRAINT [R2_pk] PRIMARY KEY CLUSTERED 
(
    [ObjectID] ASC
)

ALTER TABLE [dbo].[GDB_ITEMS]  WITH CHECK ADD  CONSTRAINT [g1_ck] CHECK  (([Shape].[STSrid]=(4326)))
GO

[Documentation] 列包含数百个 xml 项目和元素。我正在尝试使用 T-SQL 来替换一系列元素:

  <NPS_Info>
    <MetaPurp>NPS</MetaPurp>
    <NPS_Unit>
      <UnitCode>MANDATORY for Data Store: NPS Alpha Unit Code (ACAD)</UnitCode>
      <UnitType>MANDATORY for Data Store: NPS Unit Type (National Park, National Monument, etc)</UnitType>
    </NPS_Unit>
  </NPS_Info>

有了这个:

<NPS_Info>
  <MetaPurp>NPS</MetaPurp>
  <MetaPurp>CSDGM</MetaPurp>
  <MetaPurp>OnlineData</MetaPurp>
  <NPS_Unit>
    <UnitCode>ABCD</UnitCode>
    <UnitType>Park</UnitType>
  </NPS_Unit>
  <DatStore>
    <Category>Landscape</Category>
    <Category>Monitoring</Category>
    <Category>Resource Management</Category>
    <DSteward>
      <cntinfo>
        <cntperp>
           <cntper>Something</cntper>
        </cntperp>
        <cntaddr>
           <addrtype>mailing and physical</addrtype>
           <address>1 Smith Lane</address>
           <address></address>
           <city>Anywhere</city>
           <state>ST</state>
           <postal>12345</postal>
        </cntaddr>
        <cntemail>email@email.com</cntemail>
      </cntinfo>
    </DSteward>
  </DatStore>
</NPS_Info>

请原谅我笨拙的剪切和粘贴。此表中有几千行，但是，并非所有行都具有第一个代码块中描述的 xml 元素(这是一个全局表，包含数据库中所有表的描述，一些 [Documentation] 记录将包含非 -此操作不感兴趣的相关 xml)。

Answer 1

您可以使用 XML Data Modification Language (XML DML)

此代码会将名为 NPS_Info 的first 节点的内容更改为变量 @XML 中的内容。

-- Delete everything in node NPS_Info
update YourTable
set XMLCol.modify('delete //NPS_Info[1]/*')

-- Insert @XML to node NPS_Info
update YourTable
set XMLCol.modify('insert sql:variable("@XML") into (//NPS_Info)[1]')

SE Data 上的工作示例