Updating Node structure in SQL XML data

Question

I have some Data stored as XML in SQL Server that looks as follows:

<FormSearchFilter>
    .......
    <IDs>
        <int>1</int>
        <int>2</int>
    </IDs>
    .......
</FormSearchFilter>

This XML is mapped to a DTO and the data type for IDs is changing from a List to a string. As a result I now need to updae all existing XML: data to look as follows:

<FormSearchFilter>
    .......
    <IDs>1,2</IDs>
    .......
</FormSearchFilter>

Whats the best way to achieve this via an update query

Gottfried Lesigang · Accepted Answer · 2018-03-26 16:12:19Z

1

Besides the hint, that this is a very bad idea! you might try something like this:

DECLARE @t TABLE(
 Id INT NOT NULL IDENTITY(1,1),
 xml XML)

INSERT INTO @t(xml)
VALUES
 ('<FormSearchFilter><IDs><int>1</int><int>2</int></IDs></FormSearchFilter>'),
 ('<FormSearchFilter><IDs><int>1</int><int>2</int><int>3</int></IDs></FormSearchFilter>'),
 ('<FormSearchFilter><IDs><int>1</int><int>2</int><int>3</int><int>4</int></IDs></FormSearchFilter>');

 UPDATE @t
 SET [xml]= (SELECT REPLACE([xml].query('data(/FormSearchFilter/IDs/int)').value('.','nvarchar(max)'),' ',',') AS IDs 
             FOR XML PATH('FormSearchFilter'));

 SELECT * FROM @t

Explanation:

XQuery function data() will return alle text() nodes (in your case the int values) separated by a blank. This can be replaced with a comma to get the list needed.

UPDATE: Preserve other elements (be aware, that the order changes)

INSERT INTO @t(xml)
VALUES
 ('<FormSearchFilter><test>x</test><IDs><int>1</int><int>2</int></IDs></FormSearchFilter>'),
 ('<FormSearchFilter><IDs><int>1</int><int>2</int><int>3</int></IDs><test>x</test></FormSearchFilter>'),
 ('<FormSearchFilter><IDs><int>1</int><int>2</int><int>3</int><int>4</int></IDs></FormSearchFilter>');

 UPDATE @t
 SET [xml]= (SELECT  [xml].query('/FormSearchFilter/*[local-name()!="IDs"]') AS [*]
                    ,REPLACE([xml].query('data(/FormSearchFilter/IDs/int)').value('.','nvarchar(max)'),' ',',') AS IDs 
             FOR XML PATH('FormSearchFilter'));

 SELECT * FROM @t

edited Mar 26, 2018 at 16:12

answered Mar 26, 2018 at 15:44

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

5 Comments

Jeroen Mostert Over a year ago

This will not work if FormSearchFilter contains elements other than IDs which must be preserved, which the question seems to imply.

Gottfried Lesigang Over a year ago

@JeroenMostert, well one could use this to create the new <IDs> element and then replace the existing node...

Gottfried Lesigang Over a year ago

@JeroenMostert Depending on the rest, it might be enough to use .query in order to get all nodes below <FormSearchFilter> without the one with local-name()="IDs" and combine them in the FOR XML PATH

Jeroen Mostert Over a year ago

Not bad -- I don't think the reordering will be a problem in this scenario, since DTO mapping typically doesn't care about that). Attributes on IDs or blanks in int would also break this, but again, probably not an issue for this case. As usual, T-SQL's limitations make these things far more complicated than necessary (string-join, anyone?)

Stewart Alan Over a year ago

@Shnugo That works. Agreed that its not a great idea and have in fact mitigated the need to do this by affecting a more root cause fix, but been helpful to see how XML data can be updated in this way.

John Cappelletti · Accepted Answer · 2018-03-26 14:11:08Z

A bit of a hack, and if you're open to a helper Table-Valued Function.

Example

Declare @XML xml = '
<FormSearchFilter>
    <OtherContent>Some Content</OtherContent>
    <IDs>
        <int>1</int>
        <int>2</int>
    </IDs>
    <IDs>
        <int>11</int>
        <int>12</int>
        <int>13</int>
    </IDs>
    <IDs>
        <int>99</int>
    </IDs>
    <MoreContent>Some MORE Content</MoreContent>
</FormSearchFilter>
'


Select @XML = replace(cast(@XML as varchar(max)),RetVal,NewVal)
 From (
        Select *
              ,NewVal = stuff(replace(replace(RetVal,'<int>',','),'</int>',''),1,1,'')
         From [dbo].[tvf-Str-Extract](cast(@XML as varchar(max)),'<IDs>','</IDs>')
      ) A

Select @XML

Returns

<FormSearchFilter>
  <OtherContent>Some Content</OtherContent>
  <IDs>1,2</IDs>
  <IDs>11,12,13</IDs>
  <IDs>99</IDs>
  <MoreContent>Some MORE Content</MoreContent>
</FormSearchFilter>

The TVF was created because I tired of extracting content (left,right,charindex,patindex,reverse,...). It is a modifed parse/split function which accepts two non-like delimiters. Just to illustrate, if you were to run:

Select * From [dbo].[tvf-Str-Extract](cast(@XML as varchar(max)),'<IDs>','</IDs>')

The results would be

RetSeq  RetPos  RetVal
1       65      <int>1</int><int>2</int>
2       100     <int>11</int><int>12</int><int>13</int>
3       150     <int>99</int>

The TVF if Interested

CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table 
As
Return (  

with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
       cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
       cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
       cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)

Select RetSeq = Row_Number() over (Order By N)
      ,RetPos = N
      ,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1) 
 From  (
        Select *,RetVal = Substring(@String, N, L) 
         From  cte4
       ) A
 Where charindex(@Delimiter2,RetVal)>1 

)
/*
Max Length of String 1MM characters

Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[tvf-Str-Extract] (@String,'[[',']]')
*/

B.Porter · Accepted Answer · 2018-03-26 14:36:23Z

0

Not particularly elegant but does end up with the required output:

DECLARE @t TABLE(
 Id INT NOT NULL IDENTITY(1,1),
 xml XML)

INSERT INTO @t(xml)
VALUES
 ('<FormSearchFilter><IDs><int>1</int><int>2</int></IDs></FormSearchFilter>'),
 ('<FormSearchFilter><IDs><int>1</int><int>2</int><int>3</int></IDs></FormSearchFilter>'),
 ('<FormSearchFilter><IDs><int>1</int><int>2</int><int>3</int><int>4</int></IDs></FormSearchFilter>');

DECLARE @updates TABLE(
 Id INT,
 UpdatedValue XML
)

INSERT INTO @updates
SELECT 
 Id,
 (SELECT STUFF((
  SELECT 
   ',' + c.value('.', 'varchar')
  FROM @t t1
   CROSS APPLY t1.xml.nodes('//IDs/int') x(c)
  WHERE t1.Id = t.Id
  FOR XML PATH('')
 ), 1, 1, '') IDs
 FOR XML PATH(''))
FROM @t t

-- remove existing IDs node
UPDATE @t
 SET xml.modify('delete //IDs')

-- insert updated IDs node back in
UPDATE t
 SET xml.modify('insert sql:column("u.UpdatedValue") into (/FormSearchFilter)[1]')
FROM @t t
 JOIN @updates u ON t.Id = u.Id

answered Mar 26, 2018 at 14:36

B.Porter

3671 silver badge14 bronze badges

2 Comments

Jeroen Mostert Over a year ago

Don't use VARCHAR without specifying a length. In this case, it breaks your approach for any integer with more than one digit. Use varchar(max) as a simple fix.

Jeroen Mostert Over a year ago

It's worth noting that this does not preserve the order of other elements (since IDs is inserted at the end). This may or may not be a problem, depending on how the XML is processed.

Collectives™ on Stack Overflow

Updating Node structure in SQL XML data

3 Answers 3

Explanation:

UPDATE: Preserve other elements (be aware, that the order changes)

5 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Explanation:

UPDATE: Preserve other elements (be aware, that the order changes)

5 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related