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