InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 13:39:28 GMT20http://www.sqlservercentral.com/Forums/Topic483834-21-1.aspxOK, thanks to Matt's link & insights, I was able to get this to work without having to resort to MS's three line preamble:[code]Select @xml.value('(/*:Error/*:_Description/text())[1]', 'nvarchar(255)' )[/code]The underscore is just to deal with this forum's Smiley Face stuff that should be disabled in Code Blocks anyway.Fri, 11 Apr 2008 12:48:24 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic483834-21-1.aspxOK, I've got it now. And thanks for the link, it's not what I was hoping for, but it is exactly what I needed.Fri, 11 Apr 2008 12:26:55 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic483834-21-1.aspx[quote][b]rbarryyoung (4/11/2008)[/b][hr][quote][b]Matt Miller (4/11/2008)[/b][hr]Edit: the Namespace declaration must be wrong, since trying to retrieve that pulls a 404 error for me....[/quote]Yeah, but that shouldn't matter, Namespace declarations are just labels, they don't look them up or anything (gawd, how slow would that be?).[/quote]I agree with the SHOULDN'T part....but it does seem to make a difference. Apparently unless you specifically declare the namespace, trying to run queries without the namespace notation returns blanks of nulls depending on whether you use QUERY or VALUE....I should rephrase that - It shouldn't (and doesn't) seem to matter that it's an invalid URL. It shouldn't (and yet does) matter that the name space is declared on your queries either....Fri, 11 Apr 2008 11:12:41 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic483834-21-1.aspx[quote][b]rbarryyoung (4/11/2008)[/b][hr]Matt: unfortunately, I do not actually have any control over this XML, as it is a Microsoft error message returned by Service Broker. I just quickly mocked the SET = up for folks to test with and while I was trying to get the freakin' angle brackets to show up, I forgot about the embedded apostrophes.So I cannot modify it's creation in any way, I just have to deal with it after I receive it, whic is supposed to be extracting the Error Description and returning it in a RAISERROR message.*sigh* I really hate XML some days.[/quote]Barry - just came across this interesting little ditty:[code]select cast(@xml.query(N'declare namespace brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"; (/brokerns:Error/brokerns:Description/text())[1]') as nvarchar(255))[/code]It came out of this document. Again - I hate the documentation on XMl handling in 2005. Just plain SUCKS....[url=http://msdn2.microsoft.com/en-us/library/ms166041.aspx]http://msdn2.microsoft.com/en-us/library/ms166041.aspx[/url]Fri, 11 Apr 2008 11:08:47 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic483834-21-1.aspx[quote][b]Matt Miller (4/11/2008)[/b][hr]Edit: the Namespace declaration must be wrong, since trying to retrieve that pulls a 404 error for me....[/quote]Yeah, but that shouldn't matter, Namespace declarations are just labels, they don't look them up or anything (gawd, how slow would that be?).Fri, 11 Apr 2008 10:54:16 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic483834-21-1.aspxMatt: unfortunately, I do not actually have any control over this XML, as it is a Microsoft error message returned by Service Broker. I just quickly mocked the SET = up for folks to test with and while I was trying to get the freakin' angle brackets to show up, I forgot about the embedded apostrophes.So I cannot modify it's creation in any way, I just have to deal with it after I receive it, whic is supposed to be extracting the Error Description and returning it in a RAISERROR message.*sigh* I really hate XML some days.Fri, 11 Apr 2008 10:50:38 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic483834-21-1.aspxThis is not a complete answer to your question, but removing the xmlns notation allows your queries to work just fine (once you fix the issues with the single quotes INSIDE the xml declaration).Edit: the Namespace declaration must be wrong, since trying to retrieve that pulls a 404 error for me....Fri, 11 Apr 2008 10:40:50 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic483834-21-1.aspxI just cannot figure this XPath/Xquery stuff out in SQL Server. For instance I have the Following simple XML:[code]Declare @xml xmlSet @xml = '&ltError xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"&gt &ltCode>-8408&lt/Code&gt &ltDescription>Target service 'ProdWebOracle' does not support contract 'DevWebOracle_Contract'.&lt/Description&gt&lt/Error&gt[/code]All I want to do is to return the text contents of the {Description} tag, but I just cannot seem to get it right. And every single example that I can find of the Value method that I can find is returning an attribute value instead. Here is what I tried:[code]Select @xml.value('(/Error/Description/text())[1]', 'nvarchar(255)' )[/code]The result is NULL. I have tried a zillion variations, all NULL except: '(//text())[2]', but I would like to be a little bit more robust than that.Can some tell me how to do this and please tell me WHY my original syntax is not working.Fri, 11 Apr 2008 10:12:47 GMTRBarryYoung