Clever Geek Handbook

SQL Server 2005 Xquery Namespaces

I am trying to get some values ​​from Xat Datatype. The data looks like this:

<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>
</Indvidual>

Note the presence of xmlns in the FirstName and LastName elements - this is added when the xml is created by serializing the C # business object. In any case, it seems that the presence of this namespace in the elements is causing XQuery errors, e.g .:

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

This returns null. But when I allocate the namespace from the elements in the xml (for example using the T-SQL Replace statement) the above returns a value. However, there must be a better way - is there a way to make this query work without updating the xml?

thank

John Davis

+2


source to share


2 answers


You need to correctly name the item you want to select. See Adding Namespaces Using XMLNAMESPACES . Here's 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
+7


source


The * wildcard will also allow you to select an element without using an explicit namespace. Remus's answer is the way to go, but it might help others with namespace issues:



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


source






More articles:


All Articles