T-SQL XML get a value from a node problem?

Question

I have an 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'm trying to get the CPProjectId as a 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 receive is NULL =/ I've tried many combinations on @myDoc.value but no results =/

How can i retrieve the value from my XML ?

Thanks!

--EDIT: Something that i noted, when i remove the namespace declaration from the XML it works fine! The problem is that i need this namespaces! =/

See: http://stackoverflow.com/questions/4081906/sql-server-xml-namespace-querying-problem trick is "WITH NAMESPACES" and then using the "ns:" form. — , Aug 05 '11 at 20:46

score 4 · Accepted Answer · answered Aug 05 '11 at 20:50

You're right the namespace is the issue. You're query is looking for a node ExportProjectDetailsMessage but such a node doesn't exist in your document, because there is a namespace declared as a default in your document. Since you can't remove that (nor should you) you should include it in your XPATH query like so:

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

You may also want to consider not using varchar(max) but perhaps uniqueidentifier

Man.. Many Thanks! ;) PS: I was using varchar just for tests with other fields =] — renanleandrof, Aug 05 '11 at 21:05

score 1 · Answer 2 · answered Mar 01 '12 at 22:02

A better way to do this is to simply declare the namespace before each of your queries:

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

It's like a temporary default. When you run the next query in the batch you'll get nulls again if you don't specify this before each of your selects.

So instead of using "SET", you can use "SELECT" to set the value like so:

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

Same results, just more readable and maintainable. I found the solution here: http://www.sqlservercentral.com/Forums/Topic967100-145-1.aspx#bm967325

I see that the first answer was accepted before this response. But for what it's worth, I found this to be a much better technique (assuming you can rely on the namespace being consistent). — cBlaine, Jun 18 '13 at 18:23

T-SQL XML get a value from a node problem?

2 Answers2