Jacob Sebastian is a SQL Server XML Guru!

Check out his collection for XQUery Labs. This is the first 12 of his series, and right now he has 43 and counting:

XQuery Sample Scripts

* XQuery Lab 1 – Transforming rows to columns
* XQuery Lab 2 – An example using OUTER APPLY
* XQuery Lab 3 – Filtering specific nodes
* XQuery Lab 4 – Joining XML Nodes with a Relational Table
* XQuery Lab 5 – Working with Namespaces
* XQuery Lab 6 – Processing Header-Detail information
* XQuery Lab 7 – Extracting a comma separated list of values
* XQuery Lab 8 – How to update the attribute value of an XML variable?
* XQuery Lab 9 – How to delete an attribute from an XML variable?
* XQuery Lab 10 – How to insert an attribute to an XML variable
* XQuery Lab 11 – How to insert an element to an XML variable
* XQuery Lab 12 – Different ways of reading values from an XML variable

Jacob Sebastian has also released a free ebook via RedGate – The Art of XSD – SQL Server XML Schema Collections

Check it out, all 483 pages!

If you want to list the definition for a stored procedure, User Defined Function, or Trigger, you can use one of the following ways:

Alternative 1: sp_helptext (T-SQL)

-- using sp_helptext

sp_helptext 'dbo.your_object_name'

Alternative 2: syscomments (T-SQL)

-- using syscomments

SELECT [text]
FROM sys.syscomments
WHERE OBJECT_NAME(id) = 'your_object_name'

Alternative 3: OBJECT_DEFINITION (T-SQL)

-- using built in function OBJECT_DEFINITION

SELECT OBJECT_DEFINITION(OBJECT_ID('your_object_name'))

There are several ways to look for a column in your SQL Server database.

Alternative 1: INFORMATION_SCHEMA.COLUMNS (T-SQL)

DECLARE @colName NVARCHAR(30)
SET @colName = 'name'

SELECT
	TABLE_CATALOG	AS 'Database',
	TABLE_SCHEMA	AS 'Schema',
	TABLE_NAME		AS 'Table',
	COLUMN_NAME		AS 'Column'
FROM
	INFORMATION_SCHEMA.COLUMNS
WHERE
	COLUMN_NAME LIKE '%' + @colName + '%'

Alternative 2: sys.columns (T-SQL)

SELECT
	sys.tables.name		AS 'Table',
	sys.columns.name	AS 'Column'
FROM
	sys.columns
	INNER JOIN sys.tables
	ON sys.tables.object_id = sys.columns.object_id
WHERE
	sys.columns.name LIKE '%' + @colName + '%'

Alternative 3: Object Search (SSMS)

In SQL Server 2000, there was an “Object Search” tool in Query Analyzer to search for objects.

To get to this tool in SQL Server 2000, either
- Go to Tools > Object Search, or
- Press F4

Unfortunately this was removed in SQL Server 2005.

But back again in SQL Server 2008:
- Go to View > Object Explorer Details
- Type object name in the Search bar
- Press Enter to Search

Yay!

Sometimes it is inevitable to encounter scenarios that will give division by zero errors

DECLARE @dividend INT
DECLARE @divisor INT

SET @dividend = 1
SET @divisor = 0

SELECT @dividend/@divisor

/*
Error:
Msg 8134, Level 16, State 1, Line 7
Divide by zero error encountered.
*/

What you can do is you can code around it, so your users and your app do not get this error.
Read the rest of this entry »

Assume this is your XML snippet

DECLARE @authorsXML XML

SET @authorsXML = '
<Author>
  <ID>172-32-1176</ID>
  <LastName>White</LastName>
  <FirstName>Johnson</FirstName>
  <Address>
    <Street>10932 Bigge Rd.</Street>
    <City>Menlo Park</City>
    <State>CA</State>
  </Address>
</Author>
'

Note that the examples below show how you can manipulate XML nodes – but most operations require singleton values. Ie, the changes must affect one and only one node. Thus in most the examples we specify the index of the node we want to target.

For example:

(/Author/LastName)[1]

which means we are only targetting the first instance of LastName under the Author node. If you need to do a mass update, you may need to use a cursor.

Read the rest of this entry »

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)