Anyone know anything about querying XML from SQL Server?

[Memorex]

Just a little off topic. I know there are some smart ones on this board. If so...

I'm taking a SQL course and have an exam later - hoping this doesn't trip me up.

The example in the book shows this:

DECLARE @Orders XML;
SET @Orders = N'
<Orders>
<Order Product="Bike" Quantity="1" />
<Order Product="Bike" Quantity="2" />
<Order Product="Car" Quantity="4" />
</Orders>';

SELECT
tab.col.value('@Product', 'NVARCHAR(50)') AS Product
,tab.col.value('@Quantity', 'INT') AS Quantity
,tab.col.value('count(../Order)', 'INT') AS TotNumOfOrders
FROM @Orders.nodes('/Orders/Order[xs:integer(@Quantity) > 1]') AS tab(col);

The results are this:

Product Quantity TotNumOfOrders
Bike 2 3
Car 4 3


I see that the [xs:integer(@Quantity) > 1] filter causes this to return the two records with Quantities greater than one, but I would think that the TotNumOfOrders would then return '2'. So it seems like it is getting the total records before it does the filter. With a normal SQL statement and table, I believe it would filter out the records and then sum them.

The book must be correct because SQL Server returns the same result. I guess I just want to make sure that is the expected behavior. And if it is the opposite of normal SQL, you'd think the book would at least acknowledge that.

Thanks.

PS - Our resident DBA (true DBA, not just acting like a DBA) and programmer didn't know.

[AlteredDNA]

Just a little off topic. I know there are some smart ones on this board. If so...

I'm taking a SQL course and have an exam later - hoping this doesn't trip me up.

The example in the book shows this:

DECLARE @Orders XML;
SET @Orders = N'
<Orders>
<Order Product="Bike" Quantity="1" />
<Order Product="Bike" Quantity="2" />
<Order Product="Car" Quantity="4" />
</Orders>';

SELECT
tab.col.value('@Product', 'NVARCHAR(50)') AS Product
,tab.col.value('@Quantity', 'INT') AS Quantity
,tab.col.value('count(../Order)', 'INT') AS TotNumOfOrders
FROM @Orders.nodes('/Orders/Order[xs:integer(@Quantity) > 1]') AS tab(col);

The results are this:

Product Quantity TotNumOfOrders
Bike 2 3
Car 4 3


I see that the [xs:integer(@Quantity) > 1] filter causes this to return the two records with Quantities greater than one, but I would think that the TotNumOfOrders would then return '2'. So it seems like it is getting the total records before it does the filter. With a normal SQL statement and table, I believe it would filter out the records and then sum them.

The book must be correct because SQL Server returns the same result. I guess I just want to make sure that is the expected behavior. And if it is the opposite of normal SQL, you'd think the book would at least acknowledge that.

Thanks.

PS - Our resident DBA (true DBA, not just acting like a DBA) and programmer didn't know.

I'll get back to you in a bit on this - this is what I do as well...

[Don]

I get that it is confusing you but it is correct.

[AlteredDNA]

Ok - think of it like this - the TotNumOfOrders Column is not part of your XML data set, it's merely a derived column, no different than if you defined it as literal text (or a number). The count function therefore runs independent from the filter statement, and just adds the result as a column to each row that the query returns.

It can be confusing, and in "normal" SQL statements, "Select count(*) as total where (some condition)" will return the number of records that meet the filter criteria.

[Don]

Yeah, what he said.

[AlteredDNA]

Yeah, what he said.

haha - yeah, it's hard to reply without knowing the language level of the person, and why tech support is so much "fun"

[Don]

Yeah, what he said.

haha - yeah, it's hard to reply without knowing the language level of the person, and why tech support is so much "fun"

I know a little just because our tech support has been cut back quite a bit. I'm a book on the lap kind of troubleshooter when my shit breaks.

[Memorex]

Ok - think of it like this - the TotNumOfOrders Column is not part of your XML data set, it's merely a derived column, no different than if you defined it as literal text (or a number). The count function therefore runs independent from the filter statement, and just adds the result as a column to each row that the query returns.

It can be confusing, and in "normal" SQL statements, "Select count(*) as total where (some condition)" will return the number of records that meet the filter criteria.

Makes sense. I'm fluent in SQL, new to XML. Thanks.

By the way - when I say it makes sense, I still think it's a little odd. So I see what it is doing, I guess I just want it to act differently.