SQLXML in SQL server

Question

I'm having an small issue with SQLXML in SQL Server 2008. Here's my testing SQL query

DECLARE @XMLTable TABLE(GameHistory XML)
INSERT INTO @XMLTable VALUES( '<game xmlns="http://my.name.space"&gt;
      <move>
       <player>white</player>
       <piece>pawn</piece>
       <start>A2</start>
       <end>A3</end>
      </move>
      <move>
       <player>black</player>
       <piece>pawn</piece>
       <start>D7</start>
       <end>D6</end>      
      </move>
     </game>')

SELECT GameHistory.query('/game/move[1]') FROM @XMLTable

Now if I take out the namespace (xmlns="http://my.name.space") part my query works fine. Why does removing the namespace fix the issue?

Answer 1

Your SELECT asks for the element /game/move (where both game and move have no namespace), and you do not have such element in your xml. You need to ask for the proper element, that is the /game/move in the namespace http://my.name.space. Use WITH XMLNAMESPACES:

;WITH XMLNAMESPACES(DEFAULT 'http://my.name.space')
SELECT GameHistory.query('/game/move[1]') FROM @XMLTable

Answer 2

The problem is that your XPath doesn't specify a namespace so the "game" elements don't match because their namespaces are different. You need to specify the namespace in the XPath so that they match:

SELECT @x.query('declare namespace x="http://my.name.space"; /x:game/x:move[1]')