April 11, 2008 at 10:12 am
I just cannot figure this XPath/Xquery stuff out in SQL Server. For instance I have the Following simple XML:
Declare @xml xml
Set @xml = '<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">
<Code>-8408</Code>
<Description>Target service 'ProdWebOracle' does not support contract 'DevWebOracle_Contract'.</Description>
</Error>
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:
Select @xml.value('(/Error/Description/text())[1]', 'nvarchar(255)' )
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 11, 2008 at 10:40 am
This 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....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 11, 2008 at 10:50 am
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 11, 2008 at 10:54 am
Matt Miller (4/11/2008)
Edit: the Namespace declaration must be wrong, since trying to retrieve that pulls a 404 error for me....
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?).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 11, 2008 at 11:08 am
rbarryyoung (4/11/2008)
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.
Barry - just came across this interesting little ditty:
select cast(@xml.query(N'declare namespace
brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";
(/brokerns:Error/brokerns:Description/text())[1]') as nvarchar(255))
It came out of this document. Again - I hate the documentation on XMl handling in 2005. Just plain SUCKS....
http://msdn2.microsoft.com/en-us/library/ms166041.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 11, 2008 at 11:12 am
rbarryyoung (4/11/2008)
Matt Miller (4/11/2008)
Edit: the Namespace declaration must be wrong, since trying to retrieve that pulls a 404 error for me....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?).
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....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 11, 2008 at 12:26 pm
OK, 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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 11, 2008 at 12:48 pm
OK, thanks to Matt's link & insights, I was able to get this to work without having to resort to MS's three line preamble:
Select @xml.value('(/*:Error/*:_Description/text())[1]', 'nvarchar(255)' )
The underscore is just to deal with this forum's Smiley Face stuff that should be disabled in Code Blocks anyway.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply