Digital Increment

Very recently I had to get data from some XML data types from a SQL Server table.   At my employer, there are a number of SSRS reports that go out to a multitude of email addresses daily. As you know, this task is handled by the SQL Server Agent. The Agent pulls the information for each email from the Subscription and ReportSchedule tables. After a few acquisitions, my employer wishes to re-brand its website as well as its email addresses.   Very quickly, all those email addresses those reports go to needs to be re assigned from one domain - lets say "Group1.com" to "GlobalCo.com". Since we know where the emailed reports go to, we can do a batch update on the table. The challenge is that those email addresses are stored in a XML data type.   If you are used to using some quasi XML processor, you may understand how to traverse the XML DOM of a given XML document. When querying the XML data type, the field is put into memory and becomes a virtual table of ...

In my past lives, I’ve never had to pivot data in SQL Server.  I’ve always left that up to the front end and all other Business Intelligence (BI) People.  Being that I’m in the BI community now, pivoting seems to be common during basic querying since some of my users make requests on data, they want this data pivoted, but only want it once, not repeatedly.  This disqualifies SSRS reports, so I just decided that I’ll start pivoting data in SQL Server. I figured I’d do a post on pivoting data from the AdventureWorksDW2012 data warehouse so heeeeere goes!!!! For this example I decided that I wanted to get the amount of sales for each product in each territory.  Basically I want my resulset to be structured like this: SalesTerritoryRegion ModelName SalesAmount   The sales for this data warehouse are located in the “ FactInternetSales ” table, I can get the model information from “ DimProduct” dimension,  a...