views:

393

answers:

2
<game xmlns="http://my.name.space" ></game>

This is my root element. I've written a stored procedure to insert elements into it. To sum up the stored procedure, here's the SQL

UPDATE ChessGame SET GameHistory.modify('insert <move><player>black</player><piece>pawn</piece><start>E7</start><end>E6</end></move> as last into (/game)[0]') WHERE Id = @GameId;

Now when MSSQL does the insert an empty namespace is also inserted so the result is this

<move xmlns="">
  <player>black</player>
  <piece>king</piece>
  <start>E7</start>
  <end>E6</end>
</move>

Now I've tried using both

WITH XMLNAMESPACES(DEFAULT 'http://my.name.space')

and

GameHistory.modify('declare default element namespace "http://my.name.space"; insert ...')

But I end up with prefixes everywhere and a namespace declaration for each element.

Problems arise in code libraries that lack logic to handle the prefixes MSSQL puts in. In the end I just want to insert a new element into my xml root and to leave the namespace blank (use the root default?). I'm all very new to this but as I understand it, if I have a namespace in my root element, shouldn't all childnodes have a default namespace of my root?

+1  A: 
declare @x xml;
select @x='<game xmlns="http://my.name.space" ></game>';
set @x.modify('declare default element namespace "http://my.name.space"; 
    insert <move><player>black</player><piece>pawn</piece>
     <start>E7</start><end>E6</end></move> as last into (/game)[1]');
select @x;

This produces:

<game xmlns="http://my.name.space"&gt;
  <move>
    <player>black</player>
    <piece>pawn</piece>
    <start>E7</start>
    <end>E6</end>
  </move>
</game>

on both SQL 2005 SP2 and SQL 2008 SP1.

Also this table update works fine:

declare @t table (x xml);
insert into @t (x) values ('<game xmlns="http://my.name.space" ></game>');
update @t
set x.modify('declare default element namespace "http://my.name.space"; 
    insert <move><player>black</player><piece>pawn</piece>
       <start>E7</start><end>E6</end></move> as last into (/game)[1]');
select * from @t;
Remus Rusanu
+1  A: 

OK, this works for me:

DECLARE @x XML;
SET @x = '<game xmlns="http://my.name.space" ></game>';

select @x

SET @x.modify(
    ' declare default element namespace "http://my.name.space";
    insert <move><player>black</player><piece>pawn</piece><start>E7</start><end>E6</end></move> as last into (/*:game)[1]'
    )

select @x
RBarryYoung
Both of your posts work 100%I found the issue that is breaking mine though.as last into ( /game)[0] should be as last into ( /game)[1]Doesn't the [0] represent the first item occurrence? Much like a zero-based array?
Matt
@RBarry: why do you prefer `/*:node`? Does that mean 'game node with *any* namespace'?
Remus Rusanu
Matt: No, is ordinal: [1] is the first instance.
RBarryYoung
Remus: yes /*:foo means "foo with any namespace". Wreaks heck with the SQL parsers and scanners because they frequently mistake it for a begin-comment token. :-).
RBarryYoung