Update SQL variable of Type xml with value in varchar variable

Question

I have one xml as

declare @EventBodyXml xml
set @EventBodyXml ='
                <LocationCopied xmlns="http://schemas.datacontract.org/2004/07/someName" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <Location xmlns:a="http://schemas.datacontract.org/2004/07/someAnotherName">
                <a:Name>SomeLocationName</a:Name></Location>
                </LocationCopied>'

I am retrieving location name in @uName variable using below query
declare @uName nvarchar(100) set @[email protected]( 'declare default element namespace "http://schemas.datacontract.org/2004/07/someName"; declare namespace a="http://schemas.datacontract.org/2004/07/someAnotherName"; /LocationCopied/Location/a:Name').value('.', 'nvarchar(100)') ;

Then doing some manipulation with @uName and now want to update @EventBodyXml with new @uName value.

@uName='SomeNewUpdatedValue'

I have tried something like this below

    set @EventBodyXml.modify('replace value of (
                declare default element namespace "http://schemas.datacontract.org/2004/07/SCS.Domain.BusinessObjectManagement.Contract.EventModel";
                declare namespace a="http://schemas.datacontract.org/2004/07/SCS.Domain.BusinessObjectManagement.Contract.ViewModel";
                /LocationCopied/Location/a:Name/text() ) with  sql:variable("@uName") ') ;

But I am getting error like

Msg 2205, Level 16, State 1, Line 27 XQuery [modify()]: ")" was expected.

wiretext · Accepted Answer · 2015-09-19 14:39:09Z

you just miss location of namespace before REPLACE

declare @EventBodyXml xml
set @EventBodyXml ='
                <LocationCopied xmlns="http://schemas.datacontract.org/2004/07/someName" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <Location xmlns:a="http://schemas.datacontract.org/2004/07/someAnotherName">
                <a:Name>SomeLocationName</a:Name></Location>
                </LocationCopied>'


declare @uName nvarchar(100)
set @[email protected](
'declare default element namespace  "http://schemas.datacontract.org/2004/07/someName";
declare namespace a="http://schemas.datacontract.org/2004/07/someAnotherName";
/LocationCopied/Location/a:Name').value('.', 'nvarchar(100)') ;

select @uName

set @EventBodyXml.modify('
declare default element namespace "http://schemas.datacontract.org/2004/07/someName";
declare namespace a="http://schemas.datacontract.org/2004/07/someAnotherName";
replace value of (/LocationCopied/Location/a:Name/text())[1]
with sql:variable("@uName")
');

print cast(@EventBodyXml as nvarchar(max))

i have found one interesting thing during this problem you have to define default namespace which you are using in your XML variable

declare @EventBodyXml xml
set @EventBodyXml ='
                <LocationCopied  xmlns="http://schemas.datacontract.org/2004/07/someName" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <Location xmlns:a="http://schemas.datacontract.org/2004/07/someAnotherName">
                <a:Name>XXXXXXXXXXXX</a:Name></Location>
                </LocationCopied>'


declare @uName nvarchar(100)
set @uName='yyyyyyyyyyyyy'


set @EventBodyXml.modify('
  declare default element namespace "http://schemas.datacontract.org/2004/07/someName";
  declare namespace a="http://schemas.datacontract.org/2004/07/someAnotherName";
  replace value of (/LocationCopied/Location/a:Name/text())[1]
  with sql:variable("@uName")
');

print cast(@EventBodyXml as nvarchar(max))

Collectives™ on Stack Overflow

Update SQL variable of Type xml with value in varchar variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related