how to declare namespace when using sql variable to insert multiple xml nodes

Question

i'm trying to insert @newLinks into @links but am not sure how to declare the prefix "xsi" in the following sql:

declare @links xml
set @links = N'<Links/>';

declare @newLinks xml
set @newLinks = N'
<Link xsi:type="CustomLink">
  <Name>Foo</Name>
</Link>
<Link xsi:type="CustomLink">
  <Name>Bar</Name>
</Link>';

set @links.modify('
insert sql:variable("@newLinks")
into (/Links)[1]');

select @links;

executing the above sql results in the following error:

XML parsing: line 2, character 28, undeclared prefix

i tried the following with no luck:

set @links.modify('
declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
insert sql:variable("@newLinks")
into (/Links)[1]');

Answer 1

You can use an xmlns attribute in your xml fragment to define the xsi prefix. The declaration applies to all child nodes. You don't have a root node in your fragment, so you'll have to define it on both <link/> elements.

declare @newLinks xml
set @newLinks = N'
<Link 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:type="CustomLink">
  <Name>Foo</Name>
</Link>
<Link 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:type="CustomLink">
  <Name>Bar</Name>
</Link>';