Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Simple value method help Expand / Collapse
Author
Message
Posted Friday, April 11, 2008 10:12 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #483834
Posted Friday, April 11, 2008 10:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:44 PM
Points: 7,084, Visits: 14,684
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?
Post #483854
Posted Friday, April 11, 2008 10:50 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #483858
Posted Friday, April 11, 2008 10:54 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #483859
Posted Friday, April 11, 2008 11:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:44 PM
Points: 7,084, Visits: 14,684
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?
Post #483865
Posted Friday, April 11, 2008 11:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:44 PM
Points: 7,084, Visits: 14,684
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?
Post #483868
Posted Friday, April 11, 2008 12:26 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #483904
Posted Friday, April 11, 2008 12:48 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #483918
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse