Process Element XML and Attribute XML in single node

Process Element XML and Attribute XML in single node.


DECLARE @T VARCHAR(400)
SET @T = '<Log DeviceId="MainDevice001">
  <Info UId="100000" Date="05/05/2014">
    <Err>Error1</Err>
  </Info>
  <Info UId="100001" Date="05/05/2014">
    <Err>Error2</Err>
  </Info>
</Log>'
DECLARE @Errorlogsxml AS XML
SET @Errorlogsxml = Cast(@T AS XML)
SELECT TAB1.COL1.value('@UId', 'VARCHAR(100)') AS Uid,
       X.Deviceid AS Deviceid,
       TAB1.COL1.value('@Date', 'DATETIME') AS Date,
       TAB1.COL1.value('(Err)[1]', 'VARCHAR(100)') AS Error
FROM   (SELECT TAB.COL.value('@DeviceId', 'VARCHAR(100)') AS Deviceid
        FROM   @Errorlogsxml.nodes('/Log') AS Tab(COL)) X
       CROSS APPLY @Errorlogsxml.nodes('/Log/Info') AS Tab1(COL1)

XML Data in where clause

XML Data in where clause
XML Data in Document Types.
Step1 : Create Table XMLData.

	Use Adventureworks2012
	Go
	Create Table XMLData
	(
		ID Int Identity(1,1),
		XMLResult XML,
		CreateDate DateTime
	)

Step2 : Insert XML Document Types Data.

	Insert Into XMLData Values('<Databases> <Name>master</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2003-04-08T09:13:36.390</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>tempdb</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-27T16:13:43.170</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>model</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2003-04-08T09:13:36.390</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>msdb</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2012-02-10T21:02:17.770</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>ReportServer$SQL</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-16T20:33:13.300</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>ReportServer$SQLTempDB</Name> <recovery_model>3</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-16T20:33:13.583</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>temp</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-22T21:16:22.997</create_date> </Databases>',getdate())
	Insert Into XMLData Values('<Databases> <Name>Adventureworks2012</Name> <recovery_model>1</recovery_model> <state_desc>ONLINE</state_desc> <create_date>2013-05-27T22:12:48.527</create_date> </Databases>',getdate())

Step3 : Where clause for XML Data Type.

	Select * from XMLData
	 Where XMLResult.value('(/Databases/Name)[1]','varchar(20)') = 'master'

XML Data in Element Types.
Step1 : Truncate XML Data table and insert some element type XML data in the same table.

	Truncate Table XMLData
	Insert Into XMLData Values('<root><Databases name="master" recovery_model="3" state_desc="ONLINE" create_date="2003-04-08T09:13:36.390" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="tempdb" recovery_model="3" state_desc="ONLINE" create_date="2013-05-29T17:09:36.863" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="model" recovery_model="1" state_desc="ONLINE" create_date="2003-04-08T09:13:36.390" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="msdb" recovery_model="3" state_desc="ONLINE" create_date="2012-02-10T21:02:17.770" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="ReportServer$SQL" recovery_model="1" state_desc="ONLINE" create_date="2013-05-16T20:33:13.300" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="ReportServer$SQLTempDB" recovery_model="3" state_desc="ONLINE" create_date="2013-05-16T20:33:13.583" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="temp" recovery_model="1" state_desc="ONLINE" create_date="2013-05-22T21:16:22.997" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="Adventureworks2012" recovery_model="1" state_desc="ONLINE" create_date="2013-05-27T22:12:48.527" /></root>',getdate())
	Insert Into XMLData Values('<root><Databases name="SSISDB" recovery_model="1" state_desc="ONLINE" create_date="2013-05-29T17:07:05.090" /></root>',getdate())

Step2 : Where clause for XML Data Type.

	Select * from XMLData Where XMLResult.exist('(/root/Databases[@name="master"])') = 1
	Select * from XMLData Where XMLResult.value('(/root/Databases/@name)[1]','varchar(20)') = 'master'

Regards,
Nirav Gajjar

SSIS Package to get Table Schema Discrepancy using XML

SSIS Package to get Table Schema Discrepancy using XML.

I have created package which uses XML in Execute SQL Task and compares two table’s schema discrepancy.

You can use this script to compare table in different instances with different database or in same instance with difference database or in same instance with same database.

You can save your result in database table in any text file, here i have messaged the result.

I have done this stuff in different instances with different database.

Please flow the step to do so.

Step1 : Create one sample table with one column difference on two difference instnace.

Table script for source connection

CREATE TABLE [dbo].[DatabaseInfo]

(
    [ServerName] [varchar](20) NULL,
    [DatabaseName] [varchar](20) NULL,
    [LogicalName] [varchar](20) NULL,
    [PhysicalName] [varchar](200) NULL,
    [DBSize] [int] NULL)

Table script for destination connection


CREATE TABLE [dbo].[DatabaseInfo]

(
    [ServerName] [varchar](20) NULL,
    [DatabaseName] [varchar](20) NULL,
    [LogicalName] [varchar](20) NULL,
    [DBSize] [int] NULL
)

here, i have deleted column PhysicalName to get an idea.

Step2: Now drag two Execute SQL Task, One Script Task, two OLEDB Connection and two variables.

TableSchemaDifference 1

Variables

Obj_Result –> DataType Object

XMLData –> String

Step3 : Right click on Source Execute SQL Task –> Edit.

–> Select ResultSet –> XML

–>SQL Statement


SELECT
 CLMList.Table_Name,
 CLMList.COLUMN_NAME
FROM Information_Schema.Columns CLMList
Where CLMList.Table_Name = 'DatabaseInfo'
For XML Path('SyncTables'),root('Root')

TableSchemaDifference 2

Step4 : Click on ResultSet –> Edit.

–> Select XMLData variable.

TableSchemaDifference 3

Click OK

Step5 : Right click on Source Execute SQL Task –> Edit.

–> Select ResultSet –> Full Result Set

–>SQL Statement


Declare @TableName xml
set @TableName = ?

SELECT
    SyncTables.TableName,
    SyncTables.ColumnName
FROM Information_Schema.Columns CLMList
    right join (SELECT p.value('(./Table_Name)[1]', 'VARCHAR(8000)') as TableName,
    p.value('(./COLUMN_NAME)[1]', 'VARCHAR(8000)') as ColumnName
FROM @TableName.nodes('//Root/SyncTables') t(p)) as SyncTables
    on CLMList.Table_Name = SyncTables.TableName
    and CLMList.Column_Name = SyncTables.ColumnName
 Where CLMList.Column_Name is null

TableSchemaDifference 4

Step6 : Click on Parameter Mapping  of Execute SQL Task Editor.

Select User::XMLData as input variable.

TableSchemaDifference 5

Step7 : Click on Result Set of Execute SQL Task Editor.

Select User::Obj_Result in Variable Name.

TableSchemaDifference 6

Step8 : Now Right click on Script Task –> Edit.

Select User::Obj_Result as ReadOnlyVariables.

TableSchemaDifference 7

Click on Edit Script and past below code.

</pre>
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
#endregion

namespace ST_e81be99c144345b2bb202cbd5460cb01
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
  public void Main()
  {
   // TODO: Add your code here
            try
            {
                OleDbDataAdapter OA = new OleDbDataAdapter();
                DataTable DT = new DataTable();
                DataRow DR;

               
                OA.Fill(DT, Dts.Variables["User::Obj_Result"].Value);
                foreach (DataRow row in DT.Rows)
                {
                    DR = row;
                    string TableName, ColumnName;
                    TableName = DR["TableName"].ToString();
                    ColumnName = DR["ColumnName"].ToString();

                    MessageBox.Show("Table Name : " + TableName.ToString() + " Column Name : " + ColumnName.ToString());
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

Now run the package and it will show you column difference in popup message.

Regards,

Nirav Gajjar