Querying XML data fails for element with a default namespace

Question

Why am I getting a blank result for the second SELECT query and the first query returns the result.

Declare @myxml xml;
set @myxml = '<items count="2">
  <item id="561" quantity="1" />
  <item id="167" quantity="1" />
</items>'

select @myxml.query('//items');
Go
Declare @myxml xml;
set @myxml = '<ContactLog xmlns="http://adventure-works/contact">
  <Contact Date="2008-09-02T10:12:00" Type="Email">
    <Employee>adventure-works\linda3</Employee>
    <Notes>Customer confirmed delivery of order 71935.</Notes>
  </Contact>
  <Contact Date="2008-06-02T15:07:00" Type="Phone">
    <Employee>adventure-works\david8</Employee>
    <Employee>adventure-works\linda3</Employee>
    <Notes>Customer requested status update on order 71935. Informed customer it is being prepared for delivery.</Notes>
  </Contact>
</ContactLog>';

select @myxml.query('//ContactLog');

default namespace is the keywords..

har07
– har07

2016-01-15 10:42:47 +00:00
Commented Jan 15, 2016 at 10:42 — har07
– har07, Commented Jan 15, 2016 at 10:42
har07 Sorry but did not get it, please explain.

TechTurtle
– TechTurtle

2016-01-15 11:14:02 +00:00
Commented Jan 15, 2016 at 11:14 — TechTurtle
– TechTurtle, Commented Jan 15, 2016 at 11:14

TT. · Accepted Answer · 2016-01-15 11:19:14Z

1

You should query in the proper namespace. Your second SELECT should read:

select @myxml.query('declare namespace pd="http://adventure-works/contact"; //pd:ContactLog') ;

An alternative way of writing this is the following:

WITH XMLNAMESPACES ('http://adventure-works/contact' AS pd)
SELECT  @myxml.query('//pd:ContactLog');

You can read more about how handling XML namespaces in SQL Server here

answered Jan 15, 2016 at 11:19

TT.

16.2k6 gold badges53 silver badges95 bronze badges

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

Collectives™ on Stack Overflow

Querying XML data fails for element with a default namespace

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related