<root>
  <ENCOUNTER.ENCOUNTERSTATUSDESCRIPTION>
    <OldValue>Draft</OldValue>
    <NewValue>Approved</NewValue>
    <DisplayName>Status</DisplayName>
  </ENCOUNTER.ENCOUNTERSTATUSDESCRIPTION>
  <ENCOUNTER.ADDONS>
    <OldValue>0</OldValue>
    <NewValue>2</NewValue>
    <DisplayName>Addn Ons</DisplayName>
  </ENCOUNTER.ADDONS>

DECLARE @rules TABLE (ID INT, XmlRule XML)

INSERT INTO @rules VALUES(1, '<root>
  <ENCOUNTER.ENCOUNTERSTATUSDESCRIPTION>
    <OldValue>Draft</OldValue>
    <NewValue>Approved</NewValue>
    <DisplayName>Status</DisplayName>
  </ENCOUNTER.ENCOUNTERSTATUSDESCRIPTION>
  <ENCOUNTER.ADDONS>
    <OldValue>0</OldValue>
    <NewValue>2</NewValue>
    <DisplayName>Addn Ons</DisplayName>
  </ENCOUNTER.ADDONS>
</root>')


SELECT
   
    T.Col.value('NewValue[1]','varchar(50)') AS 'Operation'
   
FROM @rules r
CROSS APPLY XmlRule.nodes('/root/ENCOUNTER.ENCOUNTERSTATUSDESCRIPTION') AS T(Col)

SELECT 
		--Al.CreatedDate,
		t.b.value('NewValue[1]','varchar(50)') StatusDescription
		
	FROM dbo.AuditLog AS AL WITH (NOLOCK)
	 cross apply Changeset.nodes('/root/ENCOUNTER.ENCOUTERSTATUSDESCRIPTION') AS t(b)

		
	where 
	 EntityTypeText ='Approved'
AND AuditActionTypeID =3
AND CreatedDate>='3/1/2013'
AND CustomerID=5862

USE [KareoAudit]
GO

/****** Object:  Table [dbo].[AuditLog]    Script Date: 3/21/2013 3:56:48 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[AuditLog](
	[AuditLogID] [bigint] IDENTITY(1,1) NOT NULL,
	[Application] [varchar](50) NOT NULL,
	[AuditActionTypeID] [int] NOT NULL,
	[AuditTypeID] [int] NOT NULL,
	[ChangeSet] [xml] NULL,
	[EntityTypeId] [int] NOT NULL,
	[EntityTypeText] [varchar](100) NULL,
	[SubEntityTypeId] [int] NULL,
	[SubEntityTypeText] [varchar](100) NULL,
	[CustomerId] [int] NOT NULL,
	[PracticeId] [int] NULL,
	[UserId] [int] NOT NULL,
	[UserName] [varchar](50) NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED 
(
	[AuditLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[AuditLog]  WITH CHECK ADD  CONSTRAINT [FK_AuditLog_AuditActionType] FOREIGN KEY([AuditActionTypeID])
REFERENCES [dbo].[AuditActionType] ([AuditActionTypeID])
GO

ALTER TABLE [dbo].[AuditLog] CHECK CONSTRAINT [FK_AuditLog_AuditActionType]
GO

ALTER TABLE [dbo].[AuditLog]  WITH CHECK ADD  CONSTRAINT [FK_AuditLog_AuditType] FOREIGN KEY([AuditTypeID])
REFERENCES [dbo].[AuditType] ([AuditTypeID])
GO

ALTER TABLE [dbo].[AuditLog] CHECK CONSTRAINT [FK_AuditLog_AuditType]
GO

cross apply Changeset.nodes('/root/ENCOUNTER.ENCOU___TERSTATUSDESCRIPTION')