SQL Compare report XML import

From RedGateWiki
Jump to: navigation, search

The XML database difference reports produced by SQL Compare can be inserted into XML fields of a SQL Server 2005 or 2008 table. To do this, the XML Schema Definition is created, then a table is built with a column linking to the schema definition, and finally, the report output can be bulk-inserted.

Contents

Step 1: insert the schema definition into an XML schema collection

USE [SQCReportDb]
GO
CREATE XML Schema Collection SQLCompareReport AS
N'<?xml version="1.0" encoding="utf-16"?>
<xsd:schema
   elementFormDefault="qualified"
   attributeFormDefault="unqualified"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
	<xsd:element name="comparison">
	<xsd:complexType>
			<xsd:sequence>
			<xsd:element name="datasources">
				<xsd:complexType>
					<xsd:sequence maxOccurs="2" minOccurs="2">
						<xsd:element name="datasource">
							<xsd:complexType>
							<xsd:sequence>
								<xsd:element name="server" type="xsd:string"/>
								<xsd:element name="database" type="xsd:string"/>
							</xsd:sequence>
								<xsd:attribute name="type" type="rgDataSourceTypes"/>
								<xsd:attribute name="id" type="xsd:positiveInteger"/>
							</xsd:complexType>				  
						</xsd:element>
					</xsd:sequence>
				</xsd:complexType>
			</xsd:element>
				<xsd:element name="differences">
					<xsd:complexType>
						<xsd:sequence>
						<xsd:element name="difference" minOccurs="0" maxOccurs="unbounded">
							<xsd:complexType>
								<xsd:sequence>
									<xsd:element name="object" minOccurs="2" maxOccurs="2">
										<xsd:complexType mixed="true">
											<xsd:attribute name="owner" type="xsd:string"/>
											<xsd:attribute name="id" type="xsd:positiveInteger"/>
										</xsd:complexType>							   
								    </xsd:element>
									<xsd:element name="comparisonstrings">
										<xsd:complexType>
											<xsd:sequence>
												<xsd:element name="line" minOccurs="0" maxOccurs="unbounded">
													<xsd:complexType>
														<xsd:sequence>
															<xsd:element name="left" type="xsd:string"/>
															<xsd:element name="right" type="xsd:string"/>
														</xsd:sequence>
														<xsd:attribute name="type" type="rgStatusTypes"/>
													</xsd:complexType>									   
												</xsd:element>
										    </xsd:sequence>								  
										</xsd:complexType>							   
								    </xsd:element>
								</xsd:sequence>
						<xsd:attribute name="objecttype" type="xsd:string"/>
						<xsd:attribute name="status" type="rgStatusTypes"/>
						<xsd:attribute name="fqn" type="xsd:string" />
							</xsd:complexType>
						</xsd:element>
						</xsd:sequence>
					</xsd:complexType>
					</xsd:element>
			</xsd:sequence>
		<xsd:attribute name="direction" type="rgSynchDirections"/>
			<xsd:attribute name="timestamp" type="xsd:string" />
		</xsd:complexType>
		</xsd:element>
	<xsd:simpleType name="rgStatusTypes">
		<xsd:restriction base="xsd:string">
			<xsd:enumeration value="onlyin1"/>
			<xsd:enumeration value="onlyin2"/>
			<xsd:enumeration value="different"/>
			<xsd:enumeration value="same"/>
			<xsd:enumeration value="equal"/>
		</xsd:restriction>
	</xsd:simpleType>
	<xsd:simpleType name="rgDataSourceTypes">
		<xsd:restriction base="xsd:string">
			<xsd:enumeration value="live"/>
			<xsd:enumeration value="snapshot"/>
			<xsd:enumeration value="folder"/>
		</xsd:restriction>
	</xsd:simpleType>
	<xsd:simpleType name="rgSynchDirections">
		<xsd:restriction base="xsd:string">
			<xsd:enumeration value="1to2"/>
			<xsd:enumeration value="2to1"/>
			  </xsd:restriction>
	   </xsd:simpleType>
</xsd:schema>'

Step 2: Create the table to hold the report data

USE [SQCReportDb]
GO
/****** Object:  Table [dbo].[SQLCompareReports]    Script Date: 06/02/2009 11:17:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SQLCompareReports](
	[id] [int] IDENTITY (1,1) NOT NULL,
	[filename] [nvarchar](255) NULL,
	[report] [xml](CONTENT [dbo].[SQLCompareReport]) NULL,
 CONSTRAINT [PK__SQLCompareReport__014935CB] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Step 3: bulk-insert the data from the SQL Compare XML report

USE [SQCReportDb]
GO
insert into dbo.SQLCompareReports(filename,report)
select	'SQLCompareReport.xml', * 
from	openrowset( bulk 'C:\Documents and Settings\My.Username\My Documents\SQL Compare\Reports\SQLCompareReport.xml' ,SINGLE_NCLOB)
MyTable
GO

Test that the data has been inserted

/* Shows all objects in all databases */
select	cast(ref.value('(//comparison/@timestamp)[1]', 'nvarchar(20)') as datetime) 'Timestamp',
ref.value('(//comparison/datasources/datasource/server)[1]','varchar(30)') 'Server1',
ref.value('(//comparison/datasources/datasource/server)[2]','varchar(30)') 'Server2',
ref.value('(//comparison/datasources/datasource/database)[1]','varchar(30)') 'Database1',
ref.value('(//comparison/datasources/datasource/database)[2]','varchar(30)') 'Database2',
		ref.value('@objecttype', 'nvarchar(20)') 'ObjectType',
		ref.value('@status', 'nvarchar(20)') 'Status',
		ref.value('@fqn', 'nvarchar(100)') 'fqn'
from	dbo.SQLCompareReports cross apply report.nodes('//comparison//differences/difference') R(ref)
Personal tools