I get sulky when...

[chrisvenus]

I'm in training at the moment on SQL server. I'm sulky at the moment too. Why? The trainer asked a question with what I thought was an obvious answer. I answered and was told I was wrong. Lots of other people answered and they were wrong too. When she told us the answer I discovered that I was actually right, she just hadn't quite understood what I said. This has made me sulky. On the other hand though I can be amused because I think I might understand this part of the course better than the trainer. Certainly soem of the explanations have been a little unclear in my mind.

Ah, well. Back to the training....

Edit:


The question was as follows (this is an abdridged version but keeps the core facts). For those that care I'm doing MS course 2779A.

I have the following XML:

<?xml version="1.0" ?>
<SalesInvoice InvoiceID="1000" CustomerID="123" OrderDate="2004-03-07">
   <Items>
      <Item ProductCode="12" Quantity="2" UnitPrice="12.99">aaa<ProductName>Bike</ProductName></Item>
      <Item ProductCode="41" Quantity="1" UnitPrice="17.45">bbb<ProductName>Helmet</ProductName></Item>
      <Item ProductCode="2" Quantity="1" UnitPrice="2.99">ccc<ProductName>Water Bottle</ProductName></Item>
   </Items>
</SalesInvoice>

I then do the following SQL:

SELECT * FROM 
OPENXML(@docHandle, '/SalesInvoice/Items/Item', 1)
WITH
	(InvoiceID int '../../@InvoiceID',
	ProductID int '@ProductCode',
	Quantity int 
)

Why do I not need to specify where Quantity gets its value from as I did for InvoiceID and ProductID?

Second edit: Explain in the above SQL what the '/SalesInvoice/Items/Item' means. The trainer did so *very* badly in my view and I want to see if those who know (probably Dave mainly) can explain better. :) Those who don't know are welcome to guess if they want. They are allowed to ask what the recordset returned looks like too if they want though.

Comments

[danfossydan.livejournal.com]

But what was the question?

I wanna know if I know the anwser too.

[davedevil.livejournal.com]

So do I!

[danfossydan.livejournal.com]

You wanna know if I know the anwser.... well I have my SQL book infront of me somewhere... so I can look it up! AHAHAH! And you will never know!

[davedevil.livejournal.com]

No I wanna know cos I teach SQL Server myself, what you know bothers me not :P

(ps I got Nine Worlds and it seems to be like a mage campaign)

[chrisvenus]

The main post now has it. And no answer so you can't just say "Oh yeah, I knew that." :)

[chrisvenus]

Post edited to add it for the geeks. :)

[wimble.livejournal.com]

In the past, we've had students wandering into our office, who are doing the database module.

The lecturer has asked a question, they've provided an answer, and they've been told they are wrong.

We've carefully looked at their answer and declared

Well, it's not wrong. It'll get you the right set of results. It's not optimal, but that wasn't required by the question.

So we've sent the student back to slap the lecturer around with a wet herring.

[karohemd.livejournal.com]

This reminds me of the lecturer in technology(!) who claimed that chrome wasn't a metal...
OK, he was a robotics engineer with Siemens, but still...

[evath.livejournal.com]

An arts student told me yesterday to eat cheese to avoid the saturated fats at the beer festival. I objected to this for countless reasons.

[karohemd.livejournal.com]

*_O

That reminds me, I should hop down to the beer festival tomorrow evening, really.

[wimble.livejournal.com]

Oh, I don't know anything about OpenXML :)

The obvious answer is that Quantity is an attribute of the Item element, but ProductID isn't (renamed from ProductCode) and InvoiceID isn't (it's extract from the grand parent).

'/SalesInvoice/Items/Item' is clearly the access path to the elements you're interested in, in standard "tree-structure" (I suspect it's actually XPath). So "/" means the document root, and SalesInvoice must therefore be the containing element for the entire document. <hand waves> You know what I mean, and I don't have to explain it :)

[davedevil.livejournal.com]

yup it is xpath as an structure.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5c89.asp

If you feel geeky :)

recordset

[wimble.livejournal.com]

I'm assuming that only those values mentioned in the WITH clause get generated into the recordset. Thus:

InvoiceID	ProductID	Quantity
1000	12	2
1000	41	1
1000	2	1

But it's not impossible that the other attributes and the content will get automagically included too.

Re: recordset

[chrisvenus]

That is the correct recordset. Have a bonus point.

[chrisvenus]

Yup. My actual answer to the first question of "Why don't you need to be explicit?" was "Well, it will just assume that its an attribute." Her answer was along the lines of "Because they have the same name". I just assumed that it was implicit in my statement that it assumed it was an attribute *with the same name*". I can't see what else I could have meant, certainly.

And yes, you are broadly right. The thing about the '/SalesInvoice/Items/Item' is that you get one row for each of these it finds. This is the obvious way it would work (so you may have guessed this). THe thing is she described it as just being the base for where all your xpath worked from. So when I asked her what would happen if you based it on '/SalesInvoice' and referred to 'Items/Item/@ProductCode' she told me that it would produce multiple rows for that which is provenly not so.

Still, should get on with this lab really...

[wimble.livejournal.com]

Yes, I guessed :)

Out of curiousity, if you do base it on "/SalesInvoice", and ask for the ProductCode, what does happen (you mention "provenly", which suggests you've tried it).

Personally, I'd expect one of: the first ProductCode (12), the last ProducCode(2), the "aggregation" of the productCodes (so 12+41+2, which is either 55 or 12412, depending on whether they're treated as strings or numbers). Or, and the only one I'd think could be "correct" in a specification: an error.

[chrisvenus]

You get the first one. I believe this is what xpath does in this case as well though I am not familiar with the actual specification of either.

I suspect if being evaluated in a context where it expects a single value (rather than a set) then the xpath just looks through, finds one and then stops. This may well be as per spec since it would be a sensible way to spec it from a certain point of view. I would expect intelligent tools to provide warnings at that point though.

[wimble.livejournal.com]

Y'know.

There's a reason I don't go on training courses...

[onebyone.livejournal.com]

since it would be a sensible way to spec it from a certain point of view

Even from an SQL point of view. In some sense you're asking for one thing (a column in your results row, to be the value of an Items/Item/@ProductCode for the SalesInvoice) in a context where there might be more than one. So the equivalent of ROWCOUNT 1 isn't a stupid consequence. And hopefully the XML-reading stuff for SQL Server will ensure that things by default are ORDER BY their actual order in the document, since any other default would be a bit crazy, and "no default order" would be a pain in the ass to remember given that XML is intrinsically ordered...

I would expect intelligent tools to provide warnings at that point though.

That would be nice, since you can tell from the schema whether it's possible to have more than one Items/Item/@ProductCode (and, for that matter, whether it's possible to have less than one - does that give an empty result set (because there are 0 /SalesInvoices having the required great-grandchild), or an error (on grounds that the result row for the /SalesInvoice can't be generated?)). Nevertheless, I suspect I have a lower opinion of intelligent tools than you do.

[chrisvenus]

I should clarify that I don't think such intelligent tools to exist. Just that if I could magic one into existance it would have this feature. :) I don't have very high expectation sof real world intelligent tools. :)

[davedevil.livejournal.com]

Its the path to navigate through the XML DOM.

Root/Child/Child

The 1 is a byte argument as explained in the MSDN files:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5c89.asp

[twicedead.livejournal.com]

And does the '../../@InvoiceID' mean to pop up two levels to the SalesInvoice level?

[chrisvenus]

See my comments to wimble for the full answers I was expecting and why I was disappointed.

And did you know that? You've obviously gone and looked up the reference but did you do that before or after deciding on the answer?

And can you tell me what the value 8 does in practice? The course material only discusses 0,1,2,3...

[davedevil.livejournal.com]

Yup, I knew that. As I would always do though I hand back a refrence point so the questioner can read about the subject in more depth them self at a later point. I will admit I could nto remember the formating option the byte argument of 1 gave thoug.

*shamed*

[davedevil.livejournal.com]

Yup, I knew that. As I would always do though I hand back a refrence point so the questioner can read about the subject in more depth them self at a later point. I will admit I could nto remember the formating option the byte argument of 1 gave thoug.

*shamed*

[davedevil.livejournal.com]

Sorry made a mistake there.

'../../@InvoiceID' is an xpath statment

as is @Product ID. In the XPath notation form it is how you access an argument from the XML row.

[leathellin.livejournal.com]

Not Row, please don't call it a 'row'. It just isn't. <shudders /> :-)

[wimble.livejournal.com]

Whatever rows your boat :)

[onebyone.livejournal.com]

Except that in this case each Item element does represent something that can be thought of as a row in a database table[*], so in this particular context it might indeed be a "row".

[*] Indeed, it almost certainly is a database row which has been mangled into a slightly strange structure for the sake of generating an example which somewhat arbitrarily uses attributes, text nodes and child elements. In a relational database the table would probably have a foreign key containing the InvoiceID, three numeric columns and two text columns. Those would therefore both have names, instead of one of them being anonymous and hence, due to the "aaa"-style content, having no obvious semantics. Of course maybe it's essential that Items be able to specify multiple text nodes and nested child elements with extensibility through new elements and/or namespacing. But I seriously doubt it, given that it's a Mickey Mouse example from a textbook.

[leathellin.livejournal.com]

Except that XPath is pointing to an attribute not to the element.
And the fact it's a text book simple example isn't going to make an ideal example of data which could do with being modelled in XML rather than in a relational format.

[onebyone.livejournal.com]

Well, if you must be pedantic, davedevil said "@ProductID" (by which he must mean @ProductCode) is how you access an "argument" (by which I assume he meant attribute) from the XML row (by which I therefore assumed he meant the Item element, not the thing indicated by the XPath). I suppose it's possible that by "argument" he meant something like "value", in which case "XML row" must have meant the actual attribute pointed to by the XPath, but I proceeded on the basis that he didn't mean that, and hence "row" adequately described the abstract entity in question. Saying either "ProductCode attribute" or "Item element" would of course have be clearer.

But my general point is that the general purpose of XML is to represent abstract data entities, and as such, you can call an element a "row", "column", "fish", or almost anything, provided that the thing you're calling it is what it actually does represent.

[chrisvenus]

ah, the anonymous "aaa" stuff was me doign a test to see if you could extract just that text with what we had to hand.

[sea-of-flame.livejournal.com]

I could...erm...guess that there was a path structure thingy happening there?

On the other hand, I know nothing of XML, so a lot of that syntax was fairly meaningless to me ;)

Well, very meaningless, really.

Question then, without understanding aaaaanything of most of this:
I can understand that:

ProductCode
Quantity
UnitPrice

are all attributes of 'Item' (that's the same principle as HTML - open an angular bracket, specify your initial *thing*, then specify further properties by using Attribute="Value" as syntax)

However - is there a reason why ProductName wasn't also listed as another attribute of the Item?

Or was that just the way the example was put together, and not actually of any significance?

[chrisvenus]

I wouldn't dwell too long on the structure of the XML if I were you. Its just example stuff. I don't recall exactly why it was structured like this but basically its a non-real world example. :)

[sea-of-flame.livejournal.com]

*nods*

[sea-of-flame.livejournal.com]

Actually, I'm going to look at it again, and ignore the fact I don't understand XML and remember instead the fact I do understand SQL, albeit through knowing Access. Oddly, I do understand invoicing ;)

Narrating this through then step by step in semi-layman's terms (mostly because I don't know the correct ones to use anything properly technical):

You are specifying the elements of an invoice, which is called SalesInvoice.
Each of these invoice elements is an Item, and has attributes ProductCode,
Quantity & UnitPrice (ignoring that ProductName bit for a moment). In terms of directory structure, these are located at /SalesInvoice/Items/Item (Query: That would seem to be effectively a table called 'Items', and entries within it each specified as a new Item?)

You are trying to get specific information from this set of info, via a SELECT query - which could be said to read as:

"Return everything from the invoice items, for the following fields: Invoice ID, ProductID, and Quantity."

Reading directly across any of those items and their attributes, the following is clear:

* You are not specifying the InvoiceID in each Item. There was no need to do so - they're all elements of the same invoice, and it would have been inefficient to repeat whxat you'd already stated when opening the specific invoice - so logicially, we need to return to the invoice itself to get this info:

InvoiceID int '../../@InvoiceID' - go up two levels of the directory structure. (I'll trust you on the int & the @, I don't understand the syntax to know that's required. Out of interest, would InvoiceID int '../../@.' have also worked, or does . have a different meaning here? )

* There's nothing called ProductID in the original set of attributes - there's been a change of name here, because we actually want ProductCode.

ProductID int '@ProductCode'

* Quantity is still Quantity, and is specified in each indidivual element. No need for further faffing:

Quantity int

[onebyone.livejournal.com]

is there a reason why ProductName wasn't also listed as another attribute of the Item

Sensible reasons for doing this in real life could include:

1) ProductName may in future need to contain further structure (unlikely in this case, although could be another way of doing (3) below).
2) The ordering of the contents of Item are important (unlikely in this case).
3) You might want to specify more than one ProductName for each Item (not necessarily unlikely in this case). <ProductName>Bike</ProductName> <ProductName lang="en">Bike</ProductName> <ProductName lang="fr">Vélo</ProductName> might make a certain amount of sense, with the lang-not-specified one being the default. Of course, listing a squillion languages for each item in an invoice might not be such a great idea. But ignoring that for a moment, it's reasonable to think that you might someday want to switch the language an invoice is displayed in after it has been generated, but that the item numbers, prices etc are fixed at that point (in a catalogue you'd want to list multiple prices, but once it's invoiced the currency has probably been fixed).

There are probably people working on the academic theory of contriving examples of stock/purchase control systems...