Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Xml parsing issue/question Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 5:00 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:05 AM
Points: 1,069, Visits: 421
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 follows
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


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.
Post #1434079
Posted Thursday, March 21, 2013 8:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:44 PM
Points: 7,179, Visits: 15,764
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?
Post #1434114
Posted Friday, March 22, 2013 9:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:05 AM
Points: 1,069, Visits: 421
Apparently I can't read. Can you please tell me what I have misspelled?
Post #1434323
Posted Friday, March 22, 2013 11:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:44 PM
Points: 7,179, Visits: 15,764
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?
Post #1434435
Posted Friday, March 22, 2013 11:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:05 AM
Points: 1,069, Visits: 421
Thank you so much. that was a good catch.
Post #1434441
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse