XML Query within SQL Server

Question

I just starting to query XML within a SQL Server database. I am having trouble with the most basic query. Here is a simplified example. How do I return description? The SELECT statement below is what I am using, but it returns nothing.

SELECT Incidents.IncidentXML.query
('data(/dsIncident/IncidentInformation/Description)') AS Description 
FROM Incidents

This is the snippet of the XML file that I am using:

<dsIncident xmlns="http://tempuri.org/dsIncident.xsd"&gt;
  <IncidentInformation>
    <Description>This is the description.</Description>
    <Country>Singapore</Country>
  </IncidentInformation>
</dsIncident>

Answer 1

Well, you're missing out on the XML namespace! :-)

Try this:

SELECT 
  Incidents.IncidentXML.query('declare namespace x="http://tempuri.org/dsIncident.xsd";
          (/x:dsIncident/x:IncidentInformation/x:Description)') AS Description 
FROM Incidents

The magic is the

declare namespace x="http://tempuri.org/dsIncident.xsd"

part here - it declares a namespace (with a prefix of your choice - can be anything - here 'x') for the period of the query on that XML data.

Hopefully, that'll return something! ;-)

Marc

Answer 2

This is very helpful. I'm just starting in querying an XML field. One question though. I tried this and now I get one field that contains all the description records. Like this:

"<"x:TestDesc xmlns:x="http://tempuri.org/Testing.xsd">Test Description 1"<"/x:TestDesc> "<"x:TestDesc xmlns:x="http://tempuri.org/Testing.xsd">Test Description 2"<"/x:TestDesc> "<"x:TestDesc xmlns:x="http://tempuri.org/Testing.xsd">Test Description 3"<"/x:TestDesc>

Is there a way to get each result in its own row and just get "Test Description 1" and not all the surrounding information? And, if I want multiple columns, how is the best way to handle that? Thanks!!!