Xml parsing issue/question

  • 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.

  • 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?

  • Apparently I can't read. Can you please tell me what I have misspelled?

  • 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?

  • 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