How to handle leading white space between xml tag in sql

Question

My SQL table column having xml data like below

I am trying to find element based on specific tag value ( age ). But, my xml values having leading spaces. so, Its failing on the condition. Kindly guide me handle this.

Example:

declare @age2 varchar(40)='23'
declare @ageNew2 varchar(40)='43'
declare @xmldata xml='<Root>
         <Identification Name="John"  Family="Brown"><Age>40</Age><name>xxx</name></Identification>
         <Identification Name="Smith" Family="Johnson"><Age>35</Age><name>xxx</name></Identification>
         <Identification Name="Jessy" Family="Albert"><Age>60</Age><name>xxx</name></Identification>
         <Identification Name="Mike"  Family="Brown"><Age>23</Age><name>xxx</name></Identification>
         <Identification Name="Sarah" Family="Johnson"><Age>40</Age><name>xxx</name></Identification>
         <Identification Name="Jessy" Family="Albert"><Age>40</Age><name>xxx</name></Identification>
         <Identification Name="Mike"  Family="Brown"><Age>23 </Age><name>xxx</name></Identification>
         <Identification Name="Sarah" Family="Johnson"><Age>45</Age><name>xxx</name></Identification>         
         </Root>'

declare @dynamicData varchar(max)


set @dynamicData=' declare @xml varchar(max) ='''+Convert(varchar(max),@xmldata)+''' '
set @dynamicData +=' declare @xmld xml=convert(xml,@xml) '

set @dynamicData +=' declare @age varchar(55) ='''+@age2+''''
set @dynamicData +=' declare @ageNew varchar(55) ='''+@ageNew2+''''


DECLARE @nodeCount int
DECLARE @i int

SET @i = 0


SELECT @nodeCount = @xmldata.value('count(/Root/Identification/Age)','int')

WHILE (@i < @nodeCount)
BEGIN

set @dynamicData+=' set @xmld.modify(''replace value of (/Root/Identification[Age=sql:variable("@age")]/name/text())['+convert(varchar(10),@i)+'] with "'+@ageNew2+'"'') '

SET @i = @i + 1
END 
set @dynamicData+=' select convert(xml,@xmld) '

exec(@dynamicData)

here, i am trying to update age 23 to 43. So, as per my expectation. it should replace two places. But, its replacing first one alone. Because, second one having space. so, Please guide me. Thanks

Please [edit] your question to include sample data and desired results. — Zohar Peled, Dec 22 '19 at 10:25
take a look at https://stackoverflow.com/questions/12488213/how-to-use-like-in-xpath, espacially the workaround for endswith could help — Turo, Dec 22 '19 at 10:54

score 2 · Accepted Answer · answered Dec 22 '19 at 14:40

2

you could try casting Age/text() to token to remove all whitespace (if this is what you really need)

replace value of (/Root/Identification[xs:token(Age[1])=sql:variable("@age")]/name/text())

answered Dec 22 '19 at 14:40

lptr

1
2
6
16

Good answer, +1 from my side! Smart use of datatype casting to simulate missing in SQL Server `normalized-space()` function – Yitzhak Khabinsky Dec 22 '19 at 15:01

LukStorms · Answer 2 · 2019-12-22T17:11:04.493

If the search variable is integer, then it works. Since it then gets the benefit of implicit casting.

So first cast it to an INT variable, and use that.

Example snippet:

DECLARE @xmldata XML = N'<Root>
         <Identification Name="John"  Family="Brown"><Age>40</Age><name>xxx</name></Identification>
         <Identification Name="Smith" Family="Johnson"><Age>35</Age><name>xxx</name></Identification>
         <Identification Name="Jessy" Family="Albert"><Age>60</Age><name>xxx</name></Identification>
         <Identification Name="Mike"  Family="Brown"><Age>23</Age><name>xxx</name></Identification>
         <Identification Name="Sarah" Family="Johnson"><Age>40</Age><name>xxx</name></Identification>
         <Identification Name="Jessy" Family="Albert"><Age>40</Age><name>xxx</name></Identification>
         <Identification Name="Mike"  Family="Brown"><Age>23 </Age><name>xxx</name></Identification>
         <Identification Name="Sarah" Family="Johnson"><Age>45</Age><name>xxx</name></Identification>         
         </Root>';

DECLARE @age2 VARCHAR(40)='23';
DECLARE @ageNew2 VARCHAR(40)='43';

--
-- let us use an int to search
--
DECLARE @age2int INT = cast(@age2 as int);

--
-- how many to modify
--
DECLARE @cnt int = @xmldata.value('count(/Root/Identification/Age[.=sql:variable("@age2int")])','int');

--
-- loop over the elements to modify
--
WHILE @cnt > 0
BEGIN
  SET @xmldata.modify('replace value of (/Root/Identification/Age[.=sql:variable("@age2int")]/text())[1] with sql:variable("@ageNew2")');
  SET @cnt = @cnt - 1;
END;

A test on rextester here

@NatarajanVeerasekaran Could you add an example of that in your question? Text is fine. T-sql code is also fine. — LukStorms, Dec 22 '19 at 14:06
Ok, but I thought the data would have id's? But it seems you try to change everyone with age 23. Correct? — LukStorms, Dec 22 '19 at 14:24
@NatarajanVeerasekaran A bit late, but updated. I do like the trick of the other answer. — LukStorms, Dec 22 '19 at 17:14

How to handle leading white space between xml tag in sql

2 Answers2