SQL Server and XML - Weekly Digest (24 August 2008)

Here is a summary of the blog posts I wrote last week.

1. TSQL Lab 3 - How to join the results of a Table Valued Function to a table using CROSS APPLY
• This post demonstrates an example of using CROSS APPLY with a TABLE Valued Function. The results of the TVF is joined with a table using CROSS APPLY.

2. Another XML Shaping Example - using FOR XML PATH and EXPLICIT

• I have demonstrated several XML shaping examples in the XML Workshop Series at www.sqlservercentral.com. FOR XML command, along with AUTO, RAW, PATH and EXPLICIT gives us a good amount of control over the structure of the XML result being generated. However, some times you might find it little tricky to generate a specific XML structure. This post shows one such case and demonstrates how to solve it using FOR XML PATH and EXPLICIT.

3. XQuery Lab 23 - Retrieving values and position of elements

• This post explains a tricky XQuery requirement. Position of an element is significant in XML. However, XQuery does not provide a way to read the position of an element. For example, you can read the 3rd or 12th element from an XML document. But you cannot retrieve the position of a specific element (or even all elements). This post shows a workaround.

4. FOR XML - Generating an element having NULL value

• When you generate XML output using FOR XML, SQL Server will ignore columns having NULL values. If the application on the other end, that consumes your XML output requires elements to be present, even if they are empty, you will be in trouble. This post shows how to use XSINIL directive to generate empty XML elements even for columns having NULL values.

5. Service Broker - Service Broker will be disabled when a backup is restored

• When you restore a database, Service Broker will be disabled in the restored database. This post explains the reason for this restriction and shows how to activate service broker.

6. Understanding SQL Server SET Options 1 - SET ANSI_WARNINGS ON | OFF

• This is the first of a series of posts I wanted to write on the various SET options of SQL Server. All these SET options are documented in BOL, but most of them are explained using a very high level language that makes is hard to understand.

 

TOP 5 Most Viewed Posts (Last Week)

1. XQuery Lab 19 - How to parse a delimited string?
2. How to generate a Delimited String using FOR XML PATH
3. XQuery Labs - A Collection of XQuery Sample Scripts
4. VARCHAR/NVARCHAR (N) vs (MAX)
5. ASP.NET - SQL Server Database Timeouts

 

TOP 5 Most Viewed Posts (Last 30 days)

1. VARCHAR/NVARCHAR (N) vs (MAX)
2. Having vs Where: Which is better? Having or Where?
3. XQuery Lab 19 - How to parse a delimited string?
4. ASP.NET - SQL Server Database Timeouts
5. When should I use large Value Types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX))

 

TOP 5 Most Viewed Posts (All Time)

1. VARCHAR/NVARCHAR (N) vs (MAX)
2. ASP.NET - SQL Server Database Timeouts
3. XML Workshop
4. Having vs Where: Which is better? Having or Where?
5. Passing a Data Table to a SQL Server 2005 Stored Procedure