SQL Server 2008 find and replace elements in XML column

Question

In the following table:

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

The [Documentation] column contains several hundred xml items and elements. I am trying to figure out to, with T-SQL, replace one series of elements:

  <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>

With this:

<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>

Please forgive my clumsy cut n' paste. There are several thousand rows in this table, however, not all of them have the xml element described in the first code block (this is a global table that holds descriptions of ALL tables in the DB, some [Documentation] records will contain non-pertinent xml not of interest to this operation).

score 3 · Accepted Answer · edited Jan 18 '21 at 12:30

3

You can use XML Data Modification Language (XML DML)

This code will change the content of the first node named NPS_Info with the content in variable @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]')

Working sample on SE Data

edited Jan 18 '21 at 12:30

Community

1
1

answered Jan 15 '12 at 19:34

Mikael Eriksson

136,425
22
210
281

Hey thanks! When I try that code block, starting with: use dbname declare @T dbo.gdb_items (Documentation xml) I get the following: Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'Documentation'. Msg 1087, Level 15, State 2, Line 7 Must declare the table variable "@T". Msg 1087, Level 15, State 2, Line 54 Must declare the table variable "@T". Msg 1087, Level 15, State 2, Line 57 Must declare the table variable "@T". Msg 1087, Level 15, State 2, Line 61 Must declare the table variable "@T" I must be missing something very obvious – tpcolson Jan 15 '12 at 20:02
1

never mind....was really obvious. I see what you did in SE and changed to work in a non-temp environment. Thanks a bunch! – tpcolson Jan 15 '12 at 20:08

SQL Server 2008 find and replace elements in XML column

1 Answers1