T-SQL XML gets value from node problem?

Question

I have XML like:

<?xml version="1.0" encoding="utf-16"?> <ExportProjectDetailsMessage xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Project"> <CPProjectId>7665699f-6772-424c-8b7b-405b9220a8e7</CPProjectId> </ExportProjectDetailsMessage> 

I am trying to get CPProjectId as Uniqueidentifier using:

 DECLARE @myDoc xml DECLARE @ProdID varchar(max) SET @myDoc = '<ExportProjectDetailsMessage xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Project"><CPProjectId>7665699f-6772-424c-8b7b-405b9220a8e7</CPProjectId></ExportProjectDetailsMessage>' SET @ProdID = @myDoc.value('(ExportProjectDetailsMessage/CPProjectId)[1]', 'varchar(max)' ) SELECT @ProdID 

All I can get is NULL = / I tried many combinations on @ myDoc.value, but no results = /

How can I get the value from my XML?

Thanks!

- EDIT: Something I noticed when I remove a namespace declaration from XML, it works great! The problem is that I need these namespaces! = /

Answer 1

You are right, this is a namespace. You are requesting a node ExportProjectDetailsMessage file, but such a node does not exist in your document, since your document has a namespace declared as the default. Since you cannot remove this (and should not), you should include it in your XPATH request as follows:

 set @ProdId = @myDoc.value(' declare namespace PD="http://schemas.datacontract.org/2004/07/Project"; (PD:ExportProjectDetailsMessage/PD:CPProjectId)[1]', 'varchar(max)' ) 

You might also consider using varchar (max), but perhaps a uniqueidentifier

Answer 2

The best way to do this is to simply declare a namespace before each of your queries:

 ;WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/Project') 

This is like a temporary default value. When you run the following query in a package, you will get zeros again if you do not specify this before each of your choices.

Therefore, instead of using "SET", you can use "SELECT" to set the value as follows:

 ;WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/Project') SELECT @ProdID = @myDoc.value('(ExportProjectDetailsMessage/CPProjectId)[1]', 'VarChar(MAX)') SELECT @ProdID 

The same results, more readable and supported. I found a solution here: http://www.sqlservercentral.com/Forums/Topic967100-145-1.aspx#bm967325