Paul Nicklin (3/28/2008)

---

Hi

I'm not sure I agree exactly with your rule 1- or at least how you've used the idea of "unknown" to say what NULL is.

Consider when using foreign...

Hi csaba. See http://www.sqlservercentral.com/articles/Advanced+Querying/gotchasqlaggregatefunctionsandnull/1947/%5B/url%5D.

Jeff Moden (3/28/2008)

---

Good article... and I realize it's a couple years old...

I normally don't work NULLs against "TRUE/FALSE" values even in WHERE clauses... I normally work them against some form...

mohler (3/28/2008)

---

Excellent article that has stood the test of time.

My confusion comes in trying to apply the statement

Simply put, [when] we try to compare NULLs to data values[] or other...

Matt Miller (3/27/2008)

---

Christopher Ford (3/27/2008)

---

According to the link:

SUM(OrderQty) OVER(PARTITION BY SalesOrderID)

That works fine...

Why can't I remember what it was about that, that would be really nice to have...Several...

Matt Miller (3/27/2008)

---

Mike C (3/26/2008)

---

Yes, performance enhancements would be welcome, but OTOH I can just imagine the complexity involved in doing what CROSS APPLY does. Hopefully they'll begin finding opportunities...

Matt Miller (3/26/2008)

---

Mike C (3/26/2008)

---

Matt Miller (3/26/2008)

You're right, XQuery does have a slightly higher learning curve than many other newer features in SQL Server 🙂 The CROSS APPLY and...

Matt Miller (3/26/2008)

Right on...It does take a while for that light bulb to go off... You're essentially creating derived tables with each CROSS APPLY statement....

It's the same argument I...

Andrew Pfeiffer (3/26/2008)

---

I like this method. It is actually faster and uses...

Christopher Ford (3/26/2008)

---

The light finally went off...

for each "section" I need to cross apply it to do what I want.

Example below:

SELECT

x.value('UnparsedName[1]', 'VARCHAR(20)') as Name

,l.value('@BusinessType', 'varchar(20)') as BusinessType

,CAST(CONVERT(varchar(20),l.query('HighBalanceAmount/text()')) as money)...

For readability purposes I'd probably go with something like this:

SELECT x.value('Name[1]', 'VARCHAR(20)')

FROM @x.nodes('/Employees/Employee') e(x);

Or for this particular XML data you could simplify even further:

SELECT

Table1.Column1.value('.','VARCHAR(20)') AS Employee_Name

FROM

@XML_Employee_Name.nodes('//Name[1]') AS...

After the .nodes() method you must create a table and column alias. e is the table alias, c is the column alias.

You can retrieve all names without a CTE and CROSS APPLY as well:

DECLARE @x XML;

SET @x = '<Employees>

<Employee ID="101">

<Name>Jacob</Name>

<Department>IT</Department>

...

That's what I basically did (after about 10 edit attempts). I italicized everything after the ampersand. It appears that when the parser for this editor sees an ampersand...