SQL Server 2005 Xquery namespaces

I'm trying to get some values out of an Xml Datatype. The data looks like:

<Individual xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <FirstName xmlns="http://nswcc.org.au/BusinessEntities.Crm"&开发者_JAVA百科gt;Lirria</FirstName>
    <LastName xmlns="http://nswcc.org.au/BusinessEntities.Crm">Latimore</LastName>
</Indvidual>

Note the presence of the xmlns in the elements FirstName and LastName - this is added when we create the xml by serializing a c# business object. Anyway it seems that the presence of this namespace in the elements is causing XQuery expressions to fail, such as:

SELECT MyTable.value('(//Individual/LastName)[1]','nvarchar(100)') AS FirstName

This returns null. But when I strip out the namespace from the elements in the xml (e.g. using a Replace T-SQL statement), the above returns a value. However there must be a better way - is there a way of making this query work i.e. without updating the xml first?

Thanks

John Davies

---

You need to properly name the element you want to select. See Adding Namespaces Using WITH XMLNAMESPACES. Here is an example using your XML:

declare @x xml;
set @x = N'<Individual 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <FirstName xmlns="http://nswcc.org.au/BusinessEntities.Crm">Lirria</FirstName>
        <LastName xmlns="http://nswcc.org.au/BusinessEntities.Crm">Latimore</LastName>
    </Individual>';

with xmlnamespaces (N'http://nswcc.org.au/BusinessEntities.Crm' as crm)
select @x.value(N'(//Individual/crm:LastName)[1]',N'nvarchar(100)') AS FirstName

---

The * wildcard will also allow you to select the element without enforcing the explicit namespace. Remus' answer is the way to go, but this may assist others having namespace issues:

select @x.value(N'(//Individual/*:LastName)[1]',N'nvarchar(100)')