Select XML Schema Collection type of an XML Column

I spent an afternoon looking for a way to get the XML Schema Collection type of a given column.

I wanted to copy the contents of a table (that has an XML column with a user defines Schema Collection) to another table but I get this error message: Implicit conversion between XML types constrained by different XML schema collections is not allowed. Use the CONVERT function to run this query. 

I understand that I have to CONVERT it to the same XML Schema Collection but how do I do so dynamically?

I ended up resorting to MSDN's SQL forums. The next morning, Peso, an MVP, gives me the answer:

SELECT object_name(ac.object_id) AS TableName,
ac.name as ColumnName,
  x.name AS CollectionName
FROM sys.all_columns AS ac
LEFT JOIN sys.xml_schema_collections AS x ON x.xml_collection_id = ac.xml_collection_id
WHERE object_name(ac.object_id) = 'MyTableNameHere'

Dynamic MIME Type

Storing and retrieving images from the database is now made pretty easy.

But what about displaying images in Reports? We are required to provide an image type if we are to choose Database as our Image source.

This can be easily done if you an additional column of ImageName in your database alongside your Picture column. The ImageName should contain the proper extension of the image that was loaded into the database. A proper ImageName input, for example, is "megan-fox-is-a-dude.jpg".

On your Dataset query statement, include the following code:

SELECT Table.Column1, Table.Column2, Table.Column3, Table.Picture,
ISNULL(
CASE SUBSTRING(Table.ImageName,(LEN(Table.ImageName)-2),3)
WHEN 'jpg'
THEN 'image/jpeg'
ELSE 'image/' + SUBSTRING(Table.ImageName,LEN(Table.ImageName)-2,3)
END,'image/jpeg') as 'ImageExtension',
FROM Table

The CASE statement will give you the proper Image Extension based on the name of the Picture. If the Image column is NULL then it will provide a default MIMEType of 'image/jpeg' to avoid raising Errors - this is an important.

1.) Drag and drop Image to the Report Body.
2.) On the Image properties:
- set Value: =Fields!EmployeePicture.Value
- set MIMEType: =Fields!ImageExtenion.Value

Missing Business Intelligence Types and Analysis Services Template

While following the tutorial on Analysis Services on MSDN:
http://msdn.microsoft.com/en-us/library/ms170208.aspx

I got stuck in item 3 in Creating Analysis Services Project
http://msdn.microsoft.com/en-us/library/ms166989.aspx

I have SQL Server 2008, Developers' Edition
I have installed Analysis Services (and Reporting Services for future use) so that's not the problem.
- If you're not sure if you have that. Go to Start -> Programs -> Microsoft SQL Server-> SQL Server Business Intelligence Development Studio

If you have that they you have Analysis Services and you might be encountering the same problem I did. When trying to create my first Analysis Services Project, the Business Intelligence Type was missing and so was the Analysis Services Templates.

Solution:

In MS Visual Studio (BIDS) click on Tools -> Import and Export Settings

Import Selected Environment Settings
Yes, Save my current settings
highlight "Business Intelligence Settings" and click on Finish.

This will add the Business Intelligence projects to BIDS.
Source: http://www.windows-tech.info/15/4e09444d2e59562b.php