CODEWITHSUNDEEP
sqlsql-serverxml
Natarajan Veerasekaran
Natarajan Veerasekaran

Reputation: 83

How to handle leading white space between xml tag in sql

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

Upvotes: 0

Views: 803

Answers (2)

LukStorms
LukStorms

Reputation: 29647

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

Upvotes: 0

lptr
lptr

Reputation: 6788

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

Upvotes: 2

Related Questions