SQL Server 2008 XML replace element values with text from other element values

Question

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

The xml column documentation contains this element group 1:

<spdom>
  <bounding>
    <westbc>-84.007769</westbc>
    <eastbc>-83.037582</eastbc>
    <northbc>35.790660</northbc>
    <southbc>35.418718</southbc>
  </bounding>
</spdom>

and this element group 2:

<GeoBndBox esriExtentType="search">
  <westBL Sync="TRUE">-84.024010</westBL>
  <eastBL Sync="TRUE">-82.992641</eastBL>
  <northBL Sync="TRUE">35.845552</northBL>
  <southBL Sync="TRUE">35.417139</southBL>
  <exTypeCode Sync="TRUE">1</exTypeCode>
</GeoBndBox>

What I'd like to do, programmatically, is replace the values in group1 with the values in group 2. E.g, westbc and westBL are the same definition, so I'd like to replace -84.007769 with -84.024010. Attempting to do this on several thousand records in gdb_items, each with different text values in that element group. Thanks!

(I'm assuming that documents should be Documentation) What do you mean by "element group"? Do you mean that Documentation contains a larger XML document, and spdom and GeoBndBox are two elements in that document? — Cheran Shunmugavel
– Cheran Shunmugavel, Commented Jan 15, 2012 at 22:23
yes, that was a typo. Spdom and Geobndbox are just two elements in the xml document which is stored in the [documentation] column. There are serveral hundred elements in this document, which is FGDC Metadata. — tpcolson
– tpcolson, Commented Jan 16, 2012 at 3:37

Community · Accepted Answer · 2021-01-18 12:30:34Z

1

You should use replace value of (XML DML).

The syntax used to replace the value of westbc with 10 looks like this.

update GDB_ITEMS
set Documentation.modify(
  'replace value of (//spdom/bounding/westbc/text())[1] with "10"')

And to get hold of the value you need you would use something like this:

select Documentation.value('(//GeoBndBox/westBL)[1]', 'varchar(20)')
from GDB_ITEMS

Putting those two together in one update statement.

update T
set Documentation.modify(
  'replace value of (//spdom/bounding/westbc/text())[1] 
   with sql:column("S.Value")')
from GDB_ITEMS as T
  cross apply (select T.Documentation.value('(//GeoBndBox/westBL)[1]', 
                                            'varchar(20)')) as S(Value)

It is not possible to replace the value in more than one node at a time so you have to repeat this for every node. You can do it quite nicely in a loop using a map table that has the source node name and the target node name.

declare @Map table
(
  ID int identity primary key,
  TargetNode varchar(7),  
  SourceNode varchar(7)
)

insert into @Map values
('westbc',  'westBL'),
('eastbc',  'eastBL'),
('northbc', 'northBL'),
('southbc', 'southBL')

declare @ID int = 1

while @ID <= 4
begin
  update T 
  set Documentation.modify('replace value of 
                              (//spdom/bounding/*[local-name(.)=sql:column("M.TargetNode")]/text())[1] 
                            with sql:column("S.Value")')
  from GDB_ITEMS as T
    cross apply (select TargetNode, SourceNode from @Map where ID = @ID) as M
    cross apply (select Documentation.value('(//GeoBndBox/*[local-name(.)=sql:column("M.SourceNode")])[1]', 'varchar(20)')) as S(Value)
  
  set @ID += 1
end

Try here: https://data.stackexchange.com/stackoverflow/query/59329/new

edited Jan 18, 2021 at 12:30

CommunityBot

11 silver badge

answered Jan 16, 2012 at 6:51

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tpcolson Over a year ago

as usual, your code logic is impeccable. I'm running into a block with the '<GeoBndBox esriExtentType="search">' element. When using your text, 'GeoBndBox', I get 'Mutator 'modify()' on 'Documentation' cannot be called on a null value.'. When I use 'GeoBndBox esriExtentType="search"' it returns 'Incorrect syntax near '='.'

Mikael Eriksson Over a year ago

@tpcolson The mutator error is because your column is null. Try to add a where clause where Documentation is not null.

Collectives™ on Stack Overflow

SQL Server 2008 XML replace element values with text from other element values

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related