One way to save your XML query results to the file system is by using bcp (bulk copy program).

Be aware of the following before deciding to use bcp for your regular export requirements:

• bcp is a program external to SSMS. If you need to use this from within your scripts, you will need to enable xp_cmdshell. xp_cmdshell is an extended stored procedure that allows external command line processes to be executed from within SQL Server. Enabling xp_cmdshell is considered to be a big no no in terms of security because this opens up avenues for malicious attacks through SQL Server.
• Depending on how much data you need to export, you may need to batch your export to overcome rowsize limitations of bcp.

If you intend to use bcp from within SSMS, you will need to enable xp_cmdshell first, otherwise you will get the following error:

The following example walks you through enabling xp_cmdshell, and using bcp from within SSMS to save your XML query to an external file.

Read the rest of this entry »

Using OPENXML you can import XML documents into an XML variable, or into a table that has XML columns. The following is an example:

   1: -- ==========================================================================

   2: -- Object       : OPENXML1.sql

   3: -- Object Type  : Script

   4: -- Description  : Various examples

   5: -- Developer    : Donabel Santos

   6: -- Origin       : 2008/08/17

   7: -- Last Modified: 2008/10/04

   8: -- Notes        : 

   9: -- ==========================================================================

  10:  

  11: -- bulk load

  12: -- For this example, XML file must be saved in C:

  13: -- XML file also should specify UTF-8 encoding, ie:

  14: -- <?xml version="1.0" encoding="UTF-8"?>

  15:  

  16: DECLARE @XMLTable TABLE

  17: (

  18:    xmlcol XML

  19: )

  20:  

  21: INSERT INTO @XMLTable(xmlcol)

  22: SELECT

  23:     InvoicesXML

  24: FROM

  25: (

  26:     SELECT * 

  27:     FROM OPENROWSET(BULK 'c:invoice.xml',SINGLE_BLOB) AS Invoices

  28: ) AS Invoices(InvoicesXML)

  29:  

  30: SELECT *

  31: FROM @XMLTable

  32:  

  33:  

Sometimes you may need to change schema definitions in existing columns.

The following shows an example of how you can do this:

   1: -- drop the XML schema from existing column definitions

   2: ALTER TABLE AD

   3:    ALTER COLUMN Title xml

   4:  

   5: -- DROP IF EXISTS

   6: IF    EXISTS (SELECT name 

   7:               FROM  sys.xml_schema_collections 

   8:               WHERE name='TitleSchema')

   9:    DROP  XML SCHEMA COLLECTION TitleSchema

  10: GO

  11:  

  12: -- new definition

  13: -- there can only be one Title element

  14: CREATE XML SCHEMA COLLECTION TitleSchema AS 

  15: '<?xml version="1.0" encoding="utf-8"?>

  16: <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  17:   <xsd:element name="Title">

  18:     <xsd:complexType>

  19:          <xsd:attribute name="Type" type="xsd:string" />

  20:          <xsd:attribute name="Value" type="xsd:string" use="optional" />

  21:     </xsd:complexType>

  22:   </xsd:element>

  23: </xsd:schema>'

  24: GO

  25:  

  26: -- IMPORTANT: before you add back the schema to the column 

  27: -- definition, make sure all values in your existing column 

  28: -- comply with the new schema definition

  29: ALTER TABLE AD

  30:    ALTER COLUMN Title xml(TitleSchema)

Excerpt from the Microsoft Site:

The Microsoft SQL Server 2008 System Views Map shows the key system views included in SQL Server 2008, and the relationships between them. The map is similar to the Microsoft SQL Server 2005 version and includes updates for the new and updated the Microsoft SQL Server 2008 features such as resource governor, extended events, full-text search, and others.

You can download the SQL Server 2008 version from:

http://download.microsoft.com/download/B/4/7/B47BB887-29ED-42F5-9289-90689CA1FB10/SQL_Server_2008_System_Views_Poster.pdf

There is an equivalent one for SQL Server 2005:

http://download.microsoft.com/download/0/d/f/0dfe488e-a335-4480-8a8a-b405e32f4368/SQL2005_Sys_Views.pdf

SQLTeach/DevTeach Vancouver 2009 – Stimulus Package

SQLTeach/DevTeach is offering a “stimulus package” – register for 2 attendees to the SQLTeach/DevTeach Vancouver conference on June 8-12, 2009

Excerpt from DevTeach site:

In difficult times the best thing you can do is to upgrade your skills. This is why DevTeach came up with the idea of an Education stimulus package. You can get a free registration when your company or group registers two attendees at our main event. So register 2 attendees and get one free! This promotion is applicable on the early bird price and will run until June 8th. You can take advantage of this deal even if you are using a rebate code. If you compare our price with any similar event in the USA you will find that our price is significantly lower. For you guys coming from the US it’s even a better deal because of the exchange rate

In case you haven’t come across this yet. The OUTPUT clause allows you to display or manipulate rows affected by INSERT/UPDATE/DELETE, similar to the inserted and deleted virtual tables in a DML trigger.

   1:  

   2: CREATE TABLE t (id INT)

   3: GO

   4:  

   5: INSERT INTO t VALUES(1)

   6: INSERT INTO t VALUES(2)

   7: INSERT INTO t VALUES(3)

   8: INSERT INTO t VALUES(4)

   9: GO

  10:  

  11: -- this displays what was deleted

  12: DELETE t

  13:        OUTPUT deleted.id AS 'deleted';

  14:  

  15: -- this displays what is inserted

  16: INSERT INTO t

  17:     OUTPUT inserted.id AS 'inserted'

  18: VALUES(1)

Brent Ozar posted in his blog: Real SQL Server in the Cloud is Coming Fast. Sounds exciting, and scary. Definitely feels like we are fast forwarding into the future.

I think it’s still going to be a slow process. Not everyone will switch over, and we have to get over a huge "trust issue". I still have clients who are working on SQL Server 2000 boxes, and using Windows XP for desktop use. Some clients refuse to upgrade from SQL Server 2005 to SQL Server 2008, unless they see a lot of "evidence" that SQL Server 2008 is reliable as a rock (or something like that).

The adoption process needs not just the technological backbone – robust architecture, reliable services, no downtime. For SQL Server in the Cloud to be accepted (just like anything else), the way we think about data and infrastructure needs to change too. At this point, many business still feel "safer" when their data sits on their local boxes or on boxes within their own networks. In addition, SQL in the cloud needs to be secure enough to meet legal trictions and regulatory compliance procedures.