March 21, 2013 at 5:00 pm
So I have the following xml that is stored in a table
<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>
If I run the following I get back the results I expect
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)
RESULTS:
StatusDescription
Approved
IF I run the following:
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
I get back nothing but there should be data coming back.
The table schema is as followsUSE [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
Can anyone see any reason why I wouldn't get any results for this? I get data back when I just do a select from the auditlog table without parsing the xml.
March 21, 2013 at 8:09 pm
The short answer is "spelling counts". Your query is misspelling the node name you want. (i.e. couNter)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 22, 2013 at 9:14 am
Apparently I can't read. Can you please tell me what I have misspelled?
March 22, 2013 at 11:27 am
Sorry - was running a bit short on time last night - update the spelling of ENCOUNTER in this line (the second one - where the underscores are):
cross apply Changeset.nodes('/root/ENCOUNTER.ENCOU___TERSTATUSDESCRIPTION')
You are missing the N.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 22, 2013 at 11:31 am
Thank you so much. that was a good catch.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply