XML XQuery problem

Question

Post reply

XML XQuery problem

Marcin Zawadzki

Mr or Mrs. 500

Points: 510
More actions
March 11, 2009 at 11:23 pm

#206874

Comments posted to this topic are about the item XML XQuery problem

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Why 4 points for this question? I mean u can run this thing in SSMS and get the answer.

"Keep Trying"

Marcin Zawadzki Mr or Mrs. 500 Points: 510 More actions · Answer 2

Do not now - I'am only author of this question, others are responsible for give a score..

Campden BRI Hall of Fame Points: 3300 More actions · Answer 3

XML using T-SQL would be less of a pain if it did allow variables

SuperDBA-207096 SSCrazy Eights Points: 8176 More actions · Answer 4

I think it's a pretty good question, would like to see more xml questions!

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

If you want to run it in SSMS and get the answer, that's up to you. However that kind of defeats the purpose of the questions. You should make an attempt to answer the question without running it.

Think about it, if someone asked you this in an interview, would you answer it or say, "I can't do it without SSMS?"

StarNamer SSCrazy Eights Points: 8744 More actions · Answer 6

Did this actually appear as intended?

SET @xml =

'

I would have thought there should be something between the quotes.

Derek

kevin77 SSCarpal Tunnel Points: 4054 More actions · Answer 7

Could someone give me a code example of this that does work. I tried:

DECLARE @xml xml;

DECLARE @expression nvarchar(128);

SET @xml =

'<books>

<book price=''31'' name=''SQL Server Central'' Author=''Steve Jones''/>

</books>';

--SET @expression = '/books/book/@price[text()=31]';

IF @xml.exist('/books/book/@price[text()=31]') =1

SELECT

T.value('./@price[1]','int'),

T.value('./@name[1]','nvarchar(32)'),

T.value('./@Author[1]','nvarchar(32)')

FROM @xml.nodes('/books/book') AS T(T);

and I get the error:

XQuery [exist()]: Result of 'text()' expression is statically 'empty'

So I replace the XPath expression with:

/books/book/@price=31

and it works.

I don't use Xml and XPath every day, but I understand most of the basic concepts. What should the Xml look like in order to use the original XPath expression? The '@price' means an attribute and 'text()' all the text within a node. To me, the XPath expression is invalid or just doesn't make sense.

P.S.

Did the author just do the same thing I did and forget to escape out the '<' with '& lt;' in the Xml string when writing and posting the question?

P.P.S

I can't get an escaped ampersand followed by 'lt;', as in above to show up in these forums without manually putting that space between them.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Derek Dongray (3/12/2009)
Did this actually appear as intended?
SET @xml =
'
'
I would have thought there should be something between the quotes.

Actually, I think that was the toughest part of this question. I spent 5 minutes thinking about whether or not this was another case of the XML getting eaten by this site before I risked the 4 points by trying to answer it! (maybe that's why it 4 points? 🙂 )

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Steve Jones - Editor (3/12/2009)
If you want to run it in SSMS and get the answer, that's up to you. However that kind of defeats the purpose of the questions. You should make an attempt to answer the question without running it.
Think about it, if someone asked you this in an interview, would you answer it or say, "I can't do it without SSMS?"

FWIW, I do not think that questions of the form "What would you get if you ran this in SSMS" are the best choice, either here or in an interview.

In fact I would never ask a question like that in an interview unless I wanted them to say "Try it and see." I already have a resource that can tell me that both faster and more accurately: SSMS.

Now a much more useful question form, both in interviews and for SSC (IMHO), is "When I run this in SSMS, I get this. WHY?" You just have to make sure that there is a definitive and deterministic answer (which admittedly takes some work to get right).

[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]

ChiragNS One Orange Chip Points: 26137 More actions · Answer 10

Steve Jones - Editor (3/12/2009)
If you want to run it in SSMS and get the answer, that's up to you. However that kind of defeats the purpose of the questions. You should make an attempt to answer the question without running it.
Think about it, if someone asked you this in an interview, would you answer it or say, "I can't do it without SSMS?"

Agreed. I have nothing against such questions and most of them are quite informative. But they are easy to answer so i thought 4 points were a bit too much.

RBarryYoung has put the point across quite nicely.

"Keep Trying"

Ronald H SSCarpal Tunnel Points: 4023 More actions · Answer 11

Now a much more useful question form, both in interviews and for SSC (IMHO), is "When I run this in SSMS, I get this. WHY?" You just have to make sure that there is a definitive and deterministic answer (which admittedly takes some work to get right).

I completely agree on that. Or another question: What is needed to change to get the desired result? These question I have to face about daily, not a question about what the output would be of some code.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

TomThomson SSC Guru Points: 104773 More actions · Answer 12

ChiragNS (3/12/2009)
Why 4 points for this question? I mean u can run this thing in SSMS and get the answer.

Maybe I should have done that - I might have been four points better off. But I wouldn't have had the mental excercise of trying to recall how xml works in sql server.

Tom