Jacob's Blog

We have seen a few examples that demonstrated how to delete elements and attributes from XML documents. In this lab, let us see how to delete rows from a table, based on the data in an XML document.

DECLARE @t TABLE (id INT)
INSERT INTO @t(id) 
SELECT 1 UNION ALL 
SELECT 2 UNION ALL 
SELECT 3

SELECT * FROM @t 
/*
id
-----------
1
2
3
*/

declare @XmlData xml 
set  @XmlData = '
<PersonalInformationObject>
   <Skills>
    <SkillObject>
        <SkillId>1</SkillId>
    </SkillObject>
    <SkillObject>
        <SkillId>2</SkillId>
    </SkillObject>
    </Skills>
</PersonalInformationObject>'

DELETE t
FROM @t t
CROSS APPLY @XmlData.nodes('
    /PersonalInformationObject/Skills/SkillObject/SkillId
    [. = sql:column("id")]')
 a(x)

SELECT * FROM @t 
/*
id
-----------
3
*/

Note the usage of CROSS APPLY and the way the join is established between the table and the XML document using the sql:column() function within the XQuery expression.

Previous Lab: XQuery Lab 48 - Sorting Query files in SQL Server Management Studio (SSMS) Solution/Project

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

After waiting for much longer than I anticipated, I received my SQL Azure Invitation Code! One of the questions most frequently asked in the Azure forums is from anxious users like me who wanted to know when the invitations codes will be sent. Zach wrote a blog post that answers the questions of those of you who are still waiting for the invitation code. You can find his blog post here: http://english.zachskylesowens.net/2009/08/19/sql-azure-invitation-codes/

I was working on my presentation for 24-hour-pass event on 2nd September and came across the sorting issue with the solution explorer. I have a number of script demos and I wanted to organize them by name. No matter, what I did, the solution explorer continued to display the script files in its own order, and not in the way I wanted them.

My Frustration increased when I opened the project XML file and found that the XML element is set to be “sorted”, but the sorting flag has no effect in the way SSMS displayed the items.

<LogicalFolder Name="Queries" Type="0" Sorted="true">

CREATE PROCEDURE SortSSMSProjectFiles
(
    @ProjectFileName VARCHAR(512)
)
AS

DECLARE @x XML, @qry NVARCHAR(500), @param NVARCHAR(100)
SELECT @qry = N'
    SELECT 
        @x = CAST(bulkcolumn AS XML) 
    FROM OPENROWSET(BULK ''' + @ProjectFileName + ''', 
        SINGLE_BLOB) AS x'
PRINT @qry
SELECT @param = '@x XML OUTPUT'
EXECUTE sp_executesql @qry, @param, @x OUTPUT 

SELECT @x.query('
<SqlWorkbenchSqlProject 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    Name="{SqlWorkbenchSqlProject/@Name}">
  <Items>
    {
        for $lf in SqlWorkbenchSqlProject/Items/LogicalFolder
        return 
            if ($lf/@Name = "Queries") 
            then 
                <LogicalFolder Name="{$lf/@Name}" 
                    Type="{$lf/@Type}" Sorted="{$lf/@Sorted}">
                    <Items>
                        {
                            for $i in $lf/Items/*
                            order by $i/@Name
                            return $i
                        }
                    </Items>
                </LogicalFolder>
            else $lf
    }
  </Items>
</SqlWorkbenchSqlProject>    
')

This is how you can execute this stored procedure

EXECUTE SortSSMSProjectFiles
    @ProjectFileName = 'C:\temp\demo\demo\demo.ssmssqlproj'

The @ProjectFileName should point to the “.ssmssqlproj” file of your project. This stored procedure will produce an XML document. You can open it in SSMS and use “File->save as” menu to overwrite your existing project  file.

If you want to completely automate this, you can use osql.exe or sqlcmd.exe to execute this stored procedure and generate an output file in the desired location.

To demonstrate this problem, I created a demo project and added some files in random order. Here is how SSMS displays my files now.

I wanted to get them organized and I ran the project file through my stored procedure. The stored procedure updated the project file and here is how the files are displayed in SSMS after the change.

I tested it with my projects and it works. I would like to hear your comments and suggestions on this script.

Next Lab: XQuery Lab 49 – Deleting rows from a table based on the data in an XML document

Previous Lab: XQuery Lab 47 – Generating HTML table from XML Data

View all labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

While going over the ‘What is new’ part of SQL Server 2008 R2 Books Online, I found a reference to generating ATOM Data Feeds. It appeared that I could crate a report and then generate the data as an ATOM Data Feed.

I found it very interesting. I was very excited with the idea of generating and publishing ATOM feeds using reporting services. 

I have had a very close interaction with the ATOM format while working on the ATOM.NET project, a .NET library for generating ATOM feeds. After that I a couple of articles at SQLServerCentral that explained how to generate an ATOM Feed from TSQL.

1. XML Workshop XIX - Generating an ATOM 1.0 Feed
2. XML Workshop XXIII - A TSQL ATOM Library
3. XML Workshop XXI - Generating an ATOM 1.0 Feed with FOR XML EXPLICIT

I was very curious to know how the atom feed generation and publishing works. I had a tough time trying to configure a few SSRS 2008 installations to have reports accessible over internet. It was quite easy to configure SSRS 2005 reports to be accessible over internet. But is not anymore with SSRS 2008. So I was really curious how the ATOM feeds will be consumed by feed readers.

Reading further on Books online did not give me any clear information about this and finally I did some googling and binging and came across this article that indicates that the SSRS ATOM Data Feeds are not really meant for the poor feed readers, but it is designed to serve data to a number of higher level BI tools.

I could not figure it out from Books Online. May be I am missing something or it could be the the BOL documentation.

I have recently seen a number of questions on locking in the Database Engine Forums. Shankar Reddy and oj has shared links to a few interesting whitepapers and articles that helps to understand how locking works in SQL Server.

1. Troubleshooting Performance Problems in SQL Server 2008 
2. Lock Escalation in SQL2005
3. Locking in the Database Engine
4. Application Locks (or Mutexes) in SQL Server 2005
5. Introduction to Locking in SQL Server

If you have been waiting for SQL Azure (formerly known as SSDS) Database CTP, there is a good news. Registration for SQL Azure Database CTP is open: http://msdn.microsoft.com/en-us/sqlserver/dataservices/default.aspx

Key features with this CTP:

1. Relational Data model supporting TSQL, including stored procedures
2. Self-provisioning, auto-healing and disaster recovery, with high availability with no physical database administration.

See the SQL Azure Team Blog post: http://blogs.msdn.com/ssds/archive/2009/08/18/9874133.aspx

Welcome to XQuery Lab 47. In this lab, we will see how to generate an HTML table from an XML document, using XQuery.

Here is the source data:

<CUST COMPANY="Company1" CONTACT="Jacob" />
<CUST COMPANY="Company1" CONTACT="Michael" />
<CUST COMPANY="Company3" CONTACT="Steve" />

Here is the output required

<table>
  <tr>
    <td>Company</td>
    <td>Contact</td>
  </tr>
  <tr>
    <td>Company1</td>
    <td>Jacob</td>
  </tr>
  <tr>
    <td>Company1</td>
    <td>Michael</td>
  </tr>
  <tr>
    <td>Company3</td>
    <td>Steve</td>
  </tr>
</table>

Here is the TSQL code using XQuery to generate the required output.

DECLARE @x XML
SELECT @x = '
<CUST COMPANY="Company1" CONTACT="Jacob" />
<CUST COMPANY="Company1" CONTACT="Michael" />
<CUST COMPANY="Company3" CONTACT="Steve" />'

SELECT
    @x.query('
        <table>
            <tr>
                <td>Company</td>
                <td>Contact</td>
            </tr>
            {
                for $r in CUST
                return
                <tr>
                    <td>{data($r/@COMPANY)}</td>
                    <td>{data($r/@CONTACT)}</td>
                </tr>
            }
        </table>
')

/*
<table>
  <tr>
    <td>Company</td>
    <td>Contact</td>
  </tr>
  <tr>
    <td>Company1</td>
    <td>Jacob</td>
  </tr>
  <tr>
    <td>Company1</td>
    <td>Michael</td>
  </tr>
  <tr>
    <td>Company3</td>
    <td>Steve</td>
  </tr>
</table>
*/

Next Lab: XQuery Lab 48 - Sorting Query files in SQL Server Management Studio (SSMS) Solution/Project

Previous Lab: XQuery Lab 46 – Extracting Zip Code from an Address Value

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

SQL Server 2008 R2 is a minor release and the number of features and enhancements added to the database engine is very limited. The public CTP is available for download at http://technet.microsoft.com/en-us/evalcenter/ee315247.aspx

The following are the main Database Engine Features added in SQL Server 2008 R2 August CTP

1. SQL Server Utility – It allows creating a SQL Server Utility Control Point (UCP) which provides the ability for centralized management and consolidation of SQL Server instances and data-tier applications. SQL Server Management Studio has additional explorer windows ‘Utility Explorer’ and ‘Utility Explorer Content’ for working with Utility Control Point. More information can be found here: http://msdn.microsoft.com/en-us/library/ee210548(SQL.105).aspx
2. Support for more than 64 CPUs - The number of CPU cores that a server can use for database operations has been increased from 64 to 512
3. Unicode Compression - Unicode data that is stored in nvarchar(n) and nchar(n) columns is compressed by using an implementation of the Standard Compression Scheme for Unicode (SCSU) algorithm. A number of bloggers have blogged about this feature.
1. http://sqlblogcasts.com/blogs/simons/archive/2009/08/11/SQL2008-R2---Whats-New---Unicode-Compression.aspx
2. http://sqlblog.com/blogs/aaron_bertrand/archive/2009/08/11/sql-server-2008-r2-a-quick-experiment-in-unicode-compression.aspx
3. http://blogs.msdn.com/sqlserverstorageengine/archive/2009/08/17/unicode-compression-in-sql-server-2008r2.aspx

Did I miss something? Please feel free to add a comment.

When a VARBINARY(MAX) column is marked with the FILESTREAM attribute, the limitation of 2GB is not applicable on the value any more. You can store values larger than 2GB in such a column. The maximum size is limited only by the size of the disk.

A FILESTREAM column can be accessed from TSQL, just like you access any VARBINARY(MAX) column. To find the size (number of bytes occupied) of a FILESTREAM column, you can use the DATALENGTH() function.

SELECT 
    DATALENGTH(ColumnName) AS SizeOfData
FROM TableName 

I just came across a task to extract ZIP code values from the address column of a table. The address is a single string that contains information such as street, city, state, zip code, apartment number etc. The data comes from a legacy system where the address information is not stored in separate columns. The task is to extract the zip code from such an address value.

Here are some sample address values (The address values are not real)

CustomerID  CustomerAddress
----------- --------------------------------------------------
1           12 20 97TH STREET NEW GARDENS, NY  11415  APT 8P
2           20-10 93RD STREET #8A VICTORIA NY 11106 19TH FLR
3           290 BERKELEY STREET APT24D  NYC, NY  10038
4           351-250  345 STREET PANAMA BEACH 11414  APT4F

Looking at the pattern (in this specific case), I found that the following logic will help to identify the correct zip code values

1. break the address string into words using SPACE as the breaking point
2. Examine each 5 characters long words
3. If the word is a number, it could be the zip code

Note that this logic may not work for every case. You might find data that is quite different than what I have. For the specific set of data that I had, the above logic worked perfect.

Step #1 above can be achieved using XQuery, as explained in http://beyondrelational.com/blogs/jacob/archive/2008/08/14/xquery-lab-19-how-to-parse-a-delimited-string.aspx. LEN() and ISNUMERIC() functions can be applied on the result of #1 to perform the rest of the validations.

DECLARE @t TABLE (CustomerID INT, CustomerAddress VARCHAR(50))
INSERT INTO @t(CustomerID, CustomerAddress) 
    SELECT 1, '12 20 97TH STREET NEW GARDENS, NY  11415  APT 8P' UNION ALL
    SELECT 2, '20-10 93RD STREET #8A VICTORIA NY 11106 19TH FLR' UNION ALL
    SELECT 3, '290 BERKELEY STREET APT24D  NYC, NY  10038' UNION ALL
    SELECT 4, '351-250  345 STREET PANAMA BEACH 11414  APT4F'
    
;WITH cte AS (
    SELECT 
        CustomerID,
        CAST('<i>' + 
            REPLACE(CustomerAddress, ' ', '</i><i>') + '</i>' AS XML)
             AS CustomerAddress
    FROM @t
)
SELECT 
    CustomerID,
    x.i.value('.', 'VARCHAR(10)') AS ZipCode
FROM cte
CROSS APPLY CustomerAddress.nodes('//i[string-length(.)=5][number()>0]') 
    x(i)
    
/*
CustomerID  ZipCode
----------- ----------
1           11415
2           11106
3           10038
4           11414
*/

I created a small ASP.NET example to demonstrate this. For this demonstration, I will use the same database and table we created in the above article. We will see the code of an ASP.NET webpage that has a file upload control. We will see the VB.NET code that shows how to send the content of the file to the FILESTREAM data store using ADO.NET.

Note that I am presenting the TSQL approach here, just because that is the question asked. If I had a choice, I would have used Managed API to access the FILESTREAM data directly, rather than using TSQL to store the FILESTREAM data.

I always prefer Stored Procedures over parameterized queries. Let us create a stored procedure to perform the update for our demonstration.

CREATE PROCEDURE UpdateItemImage
(
    @ItemNumber VARCHAR(20),
    @ItemImage VARBINARY(MAX)
)         
AS

UPDATE Items SET
    ItemImage = @ItemImage
WHERE ItemNumber = @ItemNumber

Let us now create an ASP.NET application with a File Upload control. Let the user select a file of their choice and click on the ‘Upload’ button. Within the ‘click’ event of the ‘Upload’ button, add the following code.

If FileUpload1.HasFile Then
    Dim bytes(FileUpload1.PostedFile.ContentLength) As Byte
    FileUpload1.PostedFile.InputStream.Read(bytes, 0, 
                 FileUpload1.PostedFile.ContentLength)
    SaveWithTSQL(bytes)
End If

Public Sub SaveWithTSQL(ByVal data As Byte())
       'Create a connection to the database
       Dim ConStr As String
       ConStr = "Data Source=JACOB-XPS\katmai;Initial Catalog=NorthPole" & _
                ";Integrated Security=True"
       Dim con As New SqlConnection(ConStr)
       con.Open()

       'Create a command to execute
       Dim cmd As New SqlCommand()
       cmd.Connection = con
       cmd.CommandText = "UpdateItemImage"
       cmd.CommandType = System.Data.CommandType.StoredProcedure

       ' Add Parameter Values
       cmd.Parameters.AddWithValue("@ItemNumber", "MS1001")
       cmd.Parameters.Add("@ItemImage", System.Data.SqlDbType.VarBinary, -1)
       cmd.Parameters("@ItemImage").Value = data

       'Execute Command
       cmd.ExecuteNonQuery()

       'clean up
       con.Close()
   End Sub

I had been blogging a bit about the FILESTREAM feature of SQL Server 2008 recently. I have just published a rather long article on FILESTREAM, at simple-talk.com. You can read the article here: http://www.simple-talk.com/sql/sql-training/an-introduction-to-sql-server-filestream/

The article covers the following:

1. Problems with storing unstructured data
2. Introduction to FILESTREAM Feature
3. Installing and configuring FILESTREAM feature
   1. Enabling FILESTREAM as part of installation
   2. Enabling FILESTREAM during an unattended installation
   3. Enabling FILESTREAM after installation
4. Using FILESTREAM
   1. Creating a FILESTREAM enabled database
   2. Understanding FILESTREAM Data Container
   3. Reviewing the FILESTREAM Data Container
   4. Creating tables with FILESTREAM columns
   5. Reviewing FILESTREAM Data Container Changes
   6. Inserting FILESTREAM Data
   7. Accessing FILESTREAM Data using TSQL
   8. Accessing FILESTREAM Data using Managed API
   9. Updating FILESTREAM Data
   10. Deleting FILESTREAM Data
   11. FILESTREAM Garbage Collector
5. FILESTREAM Feature Summary
6. Restrictions on Existing Features
7. FILESTREAM Limitations
8. FILESTREAM Feature – Points to Remember
9. FILESTREAM Best Practices
10. Conclusions

FILESTREAM is an interesting feature and I would like to hear your comments on this feature. If you have a FILESTREAM question, feel free to contact me to discuss.

I see this error reported many times in the online forums. This usually happens if you try to create a FILESTREAM enabled table in a database that is not configured for FILESTREAM usage.

To be able to store FILESTREAM data in a database, the database should be created with a FILESTREAM file group. This file group creates and configures a special storage area in the NT file system called FILESTREAM Data Container.

If the FILESTREAM file group is configured, there will be a row in the system catalog view sys.database_files with type_desc column set to ‘FILESTREAM’. Here is a simple query that checks if the FILESTREAM file group is created for the current database.

IF EXISTS(
    SELECT * FROM sys.database_files 
    WHERE type_desc = 'FILESTREAM'
) PRINT 'FILESTREAM Configured'
ELSE
  PRINT 'FILESTREAM Not Configured'

The following sample code shows how to create a FILESTREAM enabled database.

CREATE DATABASE MyFSDatabase 
ON
PRIMARY ( 
    NAME = MyFSDatabaseDB, 
    FILENAME = 'C:\Temp\MyFSDatabaseDB.mdf'
), FILEGROUP MyFSDatabaseFS CONTAINS FILESTREAM( 
    NAME = MyFSDatabaseFS,
    FILENAME = 'C:\Temp\MyFSDatabaseFS')
LOG ON (     
    NAME = MyFSDatabaseLOG,
    FILENAME = 'C:\Temp\MyFSDatabaseLOG.ldf')
GO

“Well, when accessing from TSQL, FILESTREAM enabled columns are not different from other regular columns and the code is not different from the regular code that you write”. This used to be my usual answer to people who ask me this question. I also had a standard piece of sample code that looks like this:

INSERT INTO FileStreamTable( IdCol, FileStreamCol )
SELECT 1, CAST('Hi' AS VARBINARY(MAX))

However, one of my friends asked me; “Can you show me a REAL example that loads data to a FILESTREAM enabled column using TSQL?”. To answer this question, I wrote the following sample code.

-- Declare a variable to store the image data
DECLARE @img AS VARBINARY(MAX)

-- Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
    FROM OPENROWSET(
        BULK
        'C:\temp\zigzag.jpg',
        SINGLE_BLOB ) AS x
        
-- Insert the data to the table        
INSERT INTO Items (ItemID, ItemNumber, ItemDescription, ItemImage)
SELECT NEWID(), 'MS1001','Microsoft Mouse', @img

The above code reads the content of an image file from the local disk and inserts the content to a FILESTREAM enabled column. OPENROWSET() can be used to access disk files and is a great tool for a TSQL developer to work with disk files.