xml - SQL Server 2005 Xquery namespaces -

I am trying to exclude certain values ​​from the XML datatype. Data looks like this:

  & lt; Personalized xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance" xmlns: xsd = "http: // www. W3.org/2001/XMLSchema"> & Lt; Firstname xmlns = "http://nswcc.org.au/BusinessEntities.Crm" & gt; Liria & lt; / FirstName & gt; & Lt; LastName xmlns = "http://nswcc.org.au/BusinessEntities.Crm" & gt; Latimore & lt; / LastName & gt; & Lt; / Indvidual & gt;  

Note the presence of xmlns in elements first and last name - it is added when we make XML by serializing C # commercial object, it seems that elements in this The presence of the namespace is causing the XQuery expression to fail, such as:

  SELECT MyTable.value ('(// personal / last name) [1]', 'Newcomer (100) ') First name  

This return is taped. But when I remove namespace from elements in XML (for example use a T-SQL statement), then gives a description of the value given above. However, there should be a better way - is there any way to make this question, without updating XML first?

Thanks

John Davis

Must have to give the name exactly which you want to choose. See . Here is an example using your XML:

  declared @ x xml; Set @ x = n '& lt; Personalized xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance" xmlns: xsd = "http://www.w3.org/2001/XMLSchema" & gt; & Lt; Firstname xmlns = "http://nswcc.org.au/BusinessEntities.Crm" & gt; Liria & lt; / FirstName & gt; & Lt; LastName xmlns = "http://nswcc.org.au/BusinessEntities.Crm" & gt; Latimore & lt; / LastName & gt; & Lt; / Personal & gt; '; With Xmlnamespaces (N'http: //nswcc.org.au/BusinessEntities.Crm as CRM) select @x.value (n '(// personal / CRM: lastname) [1]', 'N'InWorker (100) ') AS First Name