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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy