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">
      <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?

That's actually not SQLXML. That's tne native XML datatype, new as of SQL Server 2005. — John Saunders
– John Saunders, Commented Sep 3, 2009 at 21:30

Remus Rusanu · Accepted Answer · 2009-09-03 21:37:59Z

2

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

answered Sep 3, 2009 at 21:37

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David · Accepted Answer · 2009-09-03 21:38:27Z

1

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]')

answered Sep 3, 2009 at 21:38

David

34.6k3 gold badges65 silver badges80 bronze badges

Collectives™ on Stack Overflow

SQLXML in SQL server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related