SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple value method help


Simple value method help

Author
Message
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14618 Visits: 9518
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12143 Visits: 18567
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?
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14618 Visits: 9518
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14618 Visits: 9518
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?).

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12143 Visits: 18567
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/brokernsBigGrinescription/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?
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12143 Visits: 18567
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?
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14618 Visits: 9518
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14618 Visits: 9518
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search