sql - Unable to add xml node using .modify if Target xml contains default namespace -

here need insert source xml node target xml using .modify function in sql server. goes fine extent when have named namespaces. moment change 1 of namespace default stops inserting node.

following code problem

declare @sourcexml xml declare @targetxml xml declare @tempxml xml  set @targetxml = '<?xml version="1.0"?> <message> <mainbody> </mainbody> <part> <innerbody xmlns:ac="http://www.example.org/standards/1" xmlns:rlc="http://www.example.org/standards/standard" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance"> </innerbody> </part> </message>';   set @sourcexml = '<rlc:movement xmlns:rlc="http://www.example.org/standards/standard" type="outstanding">   <rlc:amt ccy="gbp" >500000.00</rlc:amt> </rlc:movement> <rlc:movement xmlns:rlc="http://www.example.org/standards/standard" type="previous">   <rlc:amt ccy="gbp" >0.00</rlc:amt> </rlc:movement> <rlc:movement xmlns:rlc="http://www.example.org/standards/standard" type="loss">   <rlc:amt ccy="gbp" >1000000.00</rlc:amt> </rlc:movement> <rlc:movement xmlns:rlc="http://www.example.org/standards/standard" type="current">   <rlc:amt ccy="gbp" >500000.00</rlc:amt> </rlc:movement> '   set @targetxml.modify(' declare namespace rlc= "http://www.example.org/standards/standard"; declare namespace ac="http://www.example.org/standards/1" ; declare namespace xsi="http://www.w3.org/2001/xmlschema-instance";  insert sql:variable("@sourcexml") first (/message/part/innerbody)[1]') select @targetxml go 

this gives result follows

<message>   <mainbody />   <part>     <innerbody xmlns:ac="http://www.example.org/standards/1" xmlns:rlc="http://www.example.org/standards/standard" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance">       <rlc:movement xmlns:rlc="http://www.example.org/standards/standard" type="outstanding">         <rlc:amt ccy="gbp">500000.00</rlc:amt>       </rlc:movement>       <rlc:movement xmlns:rlc="http://www.example.org/standards/standard" type="previous">         <rlc:amt ccy="gbp">0.00</rlc:amt>       </rlc:movement>       <rlc:movement xmlns:rlc="http://www.example.org/standards/standard" type="loss">         <rlc:amt ccy="gbp">1000000.00</rlc:amt>       </rlc:movement>       <rlc:movement xmlns:rlc="http://www.example.org/standards/standard" type="current">         <rlc:amt ccy="gbp">500000.00</rlc:amt>       </rlc:movement>     </innerbody>   </part> </message> 

if remove rlc prefix , change source xml

set @targetxml = '<?xml version="1.0"?> <message> <mainbody> </mainbody> <part> <innerbody xmlns:ac="http://www.example.org/standards/1" xmlns="http://www.example.org/standards/standard" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance"> </innerbody> </part> </message>'; 

i

<message>   <mainbody />   <part>     <innerbody xmlns="http://www.example.org/standards/standard" xmlns:ac="http://www.example.org/standards/1" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" />   </part> </message> 

can please have this?

previously, innerbody in empty namespace. change, you're declaring it's in http://www.abc.org/standards/standard namespace, , such, xpath /message/part/innerbody no longer matches it. however, /message/part/rlc:innerbody will:

set @targetxml.modify(' declare namespace rlc= "http://www.abc.org/standards/standard"; declare namespace ac="http://www.abc.org/standards/1" ; declare namespace xsi="http://www.w3.org/2001/xmlschema-instance";  insert sql:variable("@sourcexml") first (/message/part/rlc:innerbody)[1]') select @targetxml go 

you should note namespace prefixes can anything, query work:

set @targetxml.modify(' declare namespace a= "http://www.abc.org/standards/standard"; declare namespace b="http://www.abc.org/standards/1" ; declare namespace c="http://www.w3.org/2001/xmlschema-instance";  insert sql:variable("@sourcexml") first (/message/part/a:innerbody)[1]') select @targetxml go