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:
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.
The following example walks you through enabling xp_cmdshell, and using bcp from within SSMS to save your XML query to an external file.
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: -- ==========================================================================
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"?>
16: DECLARE @XMLTable TABLE
18: xmlcol XML
21: INSERT INTO @XMLTable(xmlcol)
26: SELECT *
27: FROM OPENROWSET(BULK 'c:invoice.xml',SINGLE_BLOB) AS Invoices
28: ) AS Invoices(InvoicesXML)
30: SELECT *
31: FROM @XMLTable
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
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
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">
19: <xsd:attribute name="Type" type="xsd:string" />
20: <xsd:attribute name="Value" type="xsd:string" use="optional" />
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:
There is an equivalent one for SQL Server 2005:
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.
2: CREATE TABLE t (id INT)
5: INSERT INTO t VALUES(1)
6: INSERT INTO t VALUES(2)
7: INSERT INTO t VALUES(3)
8: INSERT INTO t VALUES(4)
11: -- this displays what was deleted
12: DELETE t
13: OUTPUT deleted.id AS 'deleted';
15: -- this displays what is inserted
16: INSERT INTO t
17: OUTPUT inserted.id AS 'inserted'
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.
The following is an example of how to create an XML Schema in SQL Server.
1: -- DROP IF EXISTS
2: IF EXISTS (SELECT 1
3: FROM sys.xml_schema_collections
4: WHERE name='SampleSchema')
5: DROP XML SCHEMA COLLECTION SampleSchema
7: CREATE XML SCHEMA COLLECTION SampleSchema AS
8: '<?xml version="1.0" encoding="utf-8"?>
9: <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
10: <xsd:element name="Sample">
12: <xsd:attribute name="SampleID" type="xsd:integer" />
13: <xsd:attribute name="Name" type="xsd:string" />
14: <xsd:attribute name="Description" type="xsd:string" />
To check your XML Schema:
Method 1: Go to your database > Programmability > Types > XML Schema Collections
Method 2: You can use the xml_schema_namespace function to query the schema from within SSMS
Later on I will post additional sample schemas which use different SQL XML data types.