Transforming "xml" field to pivot-like results
-
My apologies for bad formatting and perhaps not posting all that I need to - first-time poster, here.
I have a personnel database that produces an audit table, and the changes are in "xml-like" format. I need the results to be laid out in a more easy-to-understand, pivot-table like format. Here's what I know so far:
/****** Object: Table [dbo].[DATA_AUDIT] Script Date: 12/20/2018 1:36:57 PM ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[DATA_AUDIT](
[DAU_OID] [nchar](14) NOT NULL,
[DAU_TBL_OID] [nchar](14) NULL,
[DAU_OBJ_OID] [nchar](14) NULL,
[DAU_USR_OID] [nchar](14) NULL,
[DAU_CHANGE_TYPE] [numeric](1, 0) NULL,
[DAU_CHANGE_DEFINITION] [varchar](max) NULL,
[DAU_LAST_MODIFIED] [numeric](14, 0) NULL,
CONSTRAINT [DAU_PK] PRIMARY KEY CLUSTERED
(
[DAU_OID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO/****** Object: Table [dbo].[USER_INFO] Script Date: 12/20/2018 1:38:58 PM ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[USER_INFO](
[USR_OID] [nchar](14) NOT NULL,
[USR_NAME_VIEW] [varchar](60) NULL,
CONSTRAINT [USR_PK] PRIMARY KEY CLUSTERED
(
[USR_OID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOIf I run this (criteria added just to show you a sample of what I want):
DECLARE @DaysBack int = 10
DECLARE @DateToStamp datetime = dateadd("d",0-@DaysBack,cast(getdate() as date))
DECLARE @CompareToTimeStamp NUMERIC(14,0) = DATEDIFF ("s", '1/1/1970', @DateToStamp) + 18000
;with AUD_PSN AS
(
SELECT DAU_OID, USR_NAME_VIEW, DAU_OBJ_OID, DAU_TBL_OID , Changed=dbo.udf_AspenTimeStampConversion(DAU_LAST_MODIFIED), AuditData=CAST(DAU_CHANGE_DEFINITION AS XML)
FROM SISDB.x2data.dbo.USER_INFO
LEFT JOIN SISDB.x2data.dbo.DATA_AUDIT ON DAU_USR_OID=USR_OID
WHERE DAU_LAST_MODIFIED/1000>=@CompareToTimeStamp
AND DAU_TBL_OID in ('tblStaff','tblPerson')
AND USR_NAME_VIEW = 'McAlpine, Stacie'
AND ( DAU_CHANGE_DEFINITION like '%psnNameLast%' or DAU_CHANGE_DEFINITION like '%psnPhone02%')
)
select PersonIdNo=a.DAU_OBJ_OID
, DAU_TBL_OID
, Changed
, ChangedBy=USR_NAME_VIEW
, a.AuditData
, LastName_was=CASE WHEN m.c.value('@id','varchar(50)')='psnNameLast' and m.c.value('@type','varchar(25)')='previous' then m.c.value('.','varchar(100)') else null end
, LastNameName_is=CASE WHEN m.c.value('@id','varchar(50)')='psnNameLast' and m.c.value('@type','varchar(25)')='changed' then m.c.value('.','varchar(100)') else null end
, OtherPhone_was=CASE WHEN m.c.value('@id','varchar(50)')='psnPhone02' and m.c.value('@type','varchar(25)')='previous' then m.c.value('.','varchar(100)') else null end
, OtherPhone_is=CASE WHEN m.c.value('@id','varchar(50)')='psnPhone02' and m.c.value('@type','varchar(25)')='changed' then m.c.value('.','varchar(100)') else null end
from AUD_PSN a
CROSS APPLY a.AuditData.nodes('/audit/field') m(c)I can get data that looks like this:
Ideally, though (though this data is a bad example), I want to "flatten" it more, to look like this:
I need to grab this audit data, on occasion, and show by person (PersonIdNo) the before-and-after values of certain fields in certain tables in a more clear layout.
There are a lot more tables and fields that I want to report changes for -- I've just given a few, here, as examples.
Thanks for any pointers, leads, links, or questions.
-
as long as the data in the AuditDate column is xml, you can try this to see if it works.
drop table if exists #Auditcreate table #Audit
(AuditID int identity(1,1),
PersonNo int,
TableName varchar(20),
AuditData xml)insert into #Audit values
(1, 'tblPerson','<audit><field id="PanPhone02" type="previous">123-1234</field><field id="PanPhone02" type="changed" /></audit>'),
(1, 'tblPerson','<audit><field id="PanLastName" type="previous">Hubbell</field><field id="PanLastName" type="changed">NewLastName</field></audit>')
insert into #Audit values
(2, 'tblPerson','<audit><field id="PanPhone02" type="previous">123-1234</field><field id="PanPhone02" type="changed" /></audit>'),
(3, 'tblPerson','<audit><field id="PanLastName" type="previous">Hubbell</field><field id="PanLastName" type="changed">NewLastName</field></audit>'),
(4, 'tblPerson','<audit><field id="PanLastName" type="previous"/><field id="PanLastName" type="changed">NewLastName</field></audit>')select * from
(
select AuditID,
PersonNo,
TableName,
fields.value('@id[1]', 'Varchar(50)') FieldChanged,
fields.value('@type[1]', 'Varchar(50)') [type],
fields.value('.', 'Varchar(50)') OldValue,
lag (fields.value('.', 'Varchar(50)') ) over (order by AuditID, fields.value('@type[1]', 'Varchar(50)') desc) as NewValue
from #Audit
cross apply AuditData.nodes('/audit/*') as xmldata(fields)
) v
where Type = 'Previous'For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
That looks very promising. I'll try it tomorrow and update after.
Thank you for the quick response!
- Mike01 - Thursday, December 20, 2018 1:17 PM
There are a couple problems with this approach, the most obvious one being that you haven't specified the correct partition for your LAG expression, so you're mixing results from two different records.
The other issue is that it only uses the minimum XQuery necessary to get the information required, and, in doing so, makes it much harder to calculate the correct values.
Here is my solution:
SELECT
AuditID,
PersonNo,
TableName,
c.value('field[1]/@id', 'varchar(50)') AS FieldChanged,
c.value('(field[@type="previous"]/text())[1]', 'varchar(50)') AS OldValue,
c.value('(field[@type="changed"]/text())[1]', 'varchar(50)') AS NewValue
FROM #Audit
CROSS APPLY AuditData.nodes('/audit') m(c)NOTE: In order to accomplish the "pivot", I had to start higher in the document tree ('/audit' vs '/audit/field').
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply