Rob Titerle (8/4/2011)

---

This is a fundamentally simple problem, but I don't see what is likely the obvious solution. Any help pointing out the obvious to me would be greatly...

I assume that you actually want where the name attribute is "FirstAttributeName" since there is nothing named "FirstAttribute" in your sample.

This should give you what you want.

SELECT per.value('(Data/item[@name="FirstAttributeName"])[1]', 'varchar(50)') AS...

DateDiff will always produce an integer result. You need to move your addition outside of the DateDiff function.

CASE Type WHEN 'HALF' THEN DATEDIFF (d, StartDate, EndDate) + 0.5

You're thinking...

In its most basic form, think of it as AGGREGATE FACT BY DIMENSION....

In most cases where there is no obvious aggregation, you'll want to use Min() or Max(). If there is only one value corresponding to each pivot value, they'll give...

I would amend this to say that it works on the...

mfink (7/26/2011)

---

A completed version of the problem and solution

SELECT

c.value('(//AnalyticsReport/Report/Title/ProfileName)[1]','varchar(100)') as WebPage,

c.value('(PrimaryKey)[1]', 'varchar(30)') AS Date

, c.value('(DrilldownId)[2]', 'varchar(25)') AS Region

, c.value('(Cell/Content/Value)[1]', 'int') AS Counts

FROM @x.nodes('/AnalyticsReport/Report/Table/Row')...

All of the valid formats require some kind of separator between the various components except for 112 which doesn't include the time components. Your conversion is failing, because your...

Since we don't have information on your base tables or your views, it's hard to say what the best approach might be. If your views are simple selects on...

If you're going to be working with XML, you need to learn XPath. There is a tutorial at W3Schools http://www.w3schools.com/xpath/. XPath is very easy.

Drew

What's the current node?

What's the relative path from the current node to ProfileName?

You're using...

See if you can figure it out for yourself. You just need to determine the relative path from your current...

You can't have any whitespace before the <?xml version="1.0" ?>.

Drew

My previous post contains all the information specific to XML. The rest is standard SQL stuff. You declare an XML variable and set the value of the variable...

I used an XML variable. If you use an XML column, you'll probably need to use CROSS APPLY in order to evaluate for each row.

Drew