SQL Server 2005 Xquery namespaces

Question

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"&gt;
    <FirstName xmlns="http://nswcc.org.au/BusinessEntities.Crm"&gt;Lirria&lt;/FirstName&gt;
    <LastName xmlns="http://nswcc.org.au/BusinessEntities.Crm"&gt;Latimore&lt;/LastName&gt;
</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

Answer 1

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"&gt;
        <FirstName xmlns="http://nswcc.org.au/BusinessEntities.Crm"&gt;Lirria&lt;/FirstName&gt;
        <LastName xmlns="http://nswcc.org.au/BusinessEntities.Crm"&gt;Latimore&lt;/LastName&gt;
    </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