All geek questions in one place

SQL Server 2008 finds and replaces elements in an XML column

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 elements and elements. I am trying to figure out, using 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>

Wherein:

 <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 clipping. This table has several thousand rows, but not all of them have the xml element described in the first block of code (this is a global table that contains descriptions of ALL tables in the database, some records [Documentation] corresponding to xml, which are not of interest in this operation) .

+4
source share
1 answer

You can use XML Data Modification Language (XML DML)

This code will modify the contents of the first node named NPS_Info with the contents in the @XML variable.

 -- 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 for SE Data

+3
source

Source: https://habr.com/ru/post/1391130/

More articles:


All Articles