SQL Server XML Data Type query issue.

Question

Please see below SQL Server 2005 script

Declare @xmlData XML
SET @xmlData = '<?xml version="1.0"?>
<bookstore xmlns="http://myBooks"&gt;
  <book genre="autobiography" publicationdate="1981" 
      ISBN="1-861003-11-0">
    <title>The Autobiography of Benjamin Franklin</title>
    <author>
      <first-name>Benjamin</first-name>
      <last-name>Franklin</last-name>
    </author>
    <price>8.99</price>
  </book>
  <book genre="novel" publicationdate="1967" 
      ISBN="0-201-63361-2">
    <title>The Confidence Man</title>
    <author>
      <first-name>Herman</first-name>
      <last-name>Melville</last-name>
    </author>
    <price>11.99</price>
  </book>
  <book genre="philosophy" publicationdate="1991" 
      ISBN="1-861001-57-6">
    <title>The Gorgias</title>
    <author>
      <first-name>Sidas</first-name>
      <last-name>Plato</last-name>
    </author>
    <price>9.99</price>
  </book>
</bookstore>'

Select  T.Item.query('.')
From @xmlData.nodes('/bookstore/book') AS T(Item)

This script should give me list of all books node. But it is not giving expected behaviour. If i remove XMLNS then it is working fine. Can anyone explain?

Below is working fine.

Declare @xmlData XML
SET @xmlData = '<?xml version="1.0"?>
<bookstore>
  <book genre="autobiography" publicationdate="1981" 
      ISBN="1-861003-11-0">
    <title>The Autobiography of Benjamin Franklin</title>
    <author>
      <first-name>Benjamin</first-name>
      <last-name>Franklin</last-name>
    </author>
    <price>8.99</price>
  </book>
  <book genre="novel" publicationdate="1967" 
      ISBN="0-201-63361-2">
    <title>The Confidence Man</title>
    <author>
      <first-name>Herman</first-name>
      <last-name>Melville</last-name>
    </author>
    <price>11.99</price>
  </book>
  <book genre="philosophy" publicationdate="1991" 
      ISBN="1-861001-57-6">
    <title>The Gorgias</title>
    <author>
      <first-name>Sidas</first-name>
      <last-name>Plato</last-name>
    </author>
    <price>9.99</price>
  </book>
</bookstore>'

Select  T.Item.query('.')
From @xmlData.nodes('/bookstore/book') AS T(Item)

Can anyone please explain how can I correct first scritp? I want to run script with xmlns.

Answer 1

As you say - it's because your original XML data is in a XML namespace, and if that's the case, you also need to make use of that XML namespace in your XQuery:

SELECT 
   T.Item.query('.')
FROM   
   @xmlData.nodes('declare namespace ns="http://myBooks";/ns:bookstore/ns:book') 
   AS T(Item)

You need to insert that declare namespace ns="http://myBooks"; part into your XQuery and then use the defined namespace prefix ns (you can use anything here, really) to reference the XML objects.

Marc

Answer 2

marc_s' answer is good. I'd vote for it but I'm a noob.