I have a table meant to track changes.
我有一个用来跟踪变化的表格。
CREATE TABLE ChangeTracker
(
ChangeId BIGINT NOT NULL identity(1, 1) PRIMARY KEY,
ChangeDate DATETIME NOT NULL DEFAULT getdate(),
Changes VARCHAR(max) NOT NULL DEFAULT ''
)
The data in Changes
is in the following format:
更改的数据格式如下:
<span class="fieldname">AssignedTo</span>
<span class="oldvalue">user1</span>
<span class="newvalue">user2</span>
<br />
<span class="fieldname">Attachments</span>
<br />
<span class="fieldname">Status</span>
<span class="oldvalue">new</span>
<span class="newvalue">open</span>
<br />
<span class="fieldname">Priority</span>
<span class="oldvalue">low</span>
<span class="newvalue">high</span>
<br />
...
Note that some changes have fieldname, oldvalue, newvalue pair, while some have only fieldname. Also all changes are separated by a <br />
tag.
注意,有些更改有fieldname、oldvalue、newvalue对,而有些更改只有fieldname。所有更改都由
标记分隔。
So when I want to get changes on a particular FieldName (say Status), I can use the following query to do so:
因此,当我想要对某个字段名(比如状态)进行更改时,我可以使用以下查询:
SELECT * FROM
(
SELECT ChangeId,
ChangeDate,
TransDesc.value('(/root/span[@class="fieldname"]/text())[1]', 'varchar(255)') as FieldName,
TransDesc.value('(/root/span[@class="oldvalue"]/text())[1]', 'varchar(255)') AS OldValue,
TransDesc.value('(/root/span[@class="newvalue"]/text())[1]', 'varchar(255)') AS NewValue
FROM (
SELECT *, TransDesc = CAST('<root>' + SUBSTRING(ChangeA, 0, CHARINDEX('<br />', ChangeA)) + '</root>' AS XML)
FROM
(
SELECT *, ChangeA = SUBSTRING(Changes, CHARINDEX('<span class="fieldname">Status</span>', Changes), 4000)
FROM ChangeTracker
WHERE CHARINDEX('<span class="fieldname">Status</span>', Changes) > 0
) TTX
) TT
) x
This gives me the following result:
这给了我以下的结果:
ChangeId | ChangeDate | FieldName | OldValue | NewValue
————————————————————————————————————————————————————————————————————
1 | 2016-06-28 18:37:24.403 | Status | new | open
Now I want a query (create a view) that gets all the changes. So the output would look like this:
现在我想要一个查询(创建一个视图)来获取所有的更改。输出是这样的
ChangeId | ChangeDate | FieldName | OldValue | NewValue
————————————————————————————————————————————————————————————————————
1 | 2016-06-28 18:37:24.403 | AssignedTo | user1 | user2
1 | 2016-06-28 18:37:24.403 | Attachments | NULL | NULL
1 | 2016-06-28 18:37:24.403 | Status | new | open
1 | 2016-06-28 18:37:24.403 | Priority | low | high
2 个解决方案
#1
2
It is very good that you have <br />
as separator. This way you can get what you want.
有
作为分隔符非常好。这样你就能得到你想要的。
;with tbl as (
select ChangeId,ChangeDate,
--build xml
cast('<root><rec>'+replace(Changes,'<br />','</rec><rec>')+'</rec></root>' as xml) x
from ChangeTracker
)
select ChangeId, ChangeDate,
t.v.value('span[@class="fieldname"][1]','varchar(50)') fieldname,
t.v.value('span[@class="oldvalue"][1]','varchar(50)') oldvalue,
t.v.value('span[@class="newvalue"][1]','varchar(50)') newvalue
from tbl cross apply tbl.x.nodes('root/rec') t(v) --convert to tabular form
where t.v.value('span[@class="fieldname"][1]','varchar(50)') is not null
#2
2
We can do by comma split approach. Here we need just need to split string by "<br />".
我们可以用逗号分隔法。这里我们只需要用“
”来拆分字符串。
For Example:-
例如:-
SELECT CAST(item AS XML)
FROM dbo.SplitString(@str, '<br />') AS [Changes]
WHERE LEN(item) > 0
Result:- Result
结果:结果
So, the final query will be:-
因此,最终的查询将是:-
INSERT INTO ChangeTracker
SELECT GETDATE()
,tb.xmldata.value('(/span[@class="fieldname"]/text())[1]', 'varchar(255)') AS fieldName
,tb.xmldata.value('(/span[@class="oldvalue"]/text())[1]', 'varchar(255)') AS oldValue
,tb.xmldata.value('(/span[@class="newvalue"]/text())[1]', 'varchar(255)') AS newValue
FROM(
SELECT CAST(Item AS XML) AS xmldata
FROM dbo.SplitString(@str, '<br />') AS [Changes]
WHERE LEN(item) > 0
) AS tb
Final Result:- Final Result
最终结果:最终结果
Note:- You need to create split function, please find below.BEGIN
注意:-您需要创建拆分函数,请在下面找到
CREATE FUNCTION SplitString
(
@Input NVARCHAR(MAX),
@Character NVARCHAR(10)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + LEN(@Character), LEN(@Input))
END
RETURN
END
#1
2
It is very good that you have <br />
as separator. This way you can get what you want.
有
作为分隔符非常好。这样你就能得到你想要的。
;with tbl as (
select ChangeId,ChangeDate,
--build xml
cast('<root><rec>'+replace(Changes,'<br />','</rec><rec>')+'</rec></root>' as xml) x
from ChangeTracker
)
select ChangeId, ChangeDate,
t.v.value('span[@class="fieldname"][1]','varchar(50)') fieldname,
t.v.value('span[@class="oldvalue"][1]','varchar(50)') oldvalue,
t.v.value('span[@class="newvalue"][1]','varchar(50)') newvalue
from tbl cross apply tbl.x.nodes('root/rec') t(v) --convert to tabular form
where t.v.value('span[@class="fieldname"][1]','varchar(50)') is not null
#2
2
We can do by comma split approach. Here we need just need to split string by "<br />".
我们可以用逗号分隔法。这里我们只需要用“
”来拆分字符串。
For Example:-
例如:-
SELECT CAST(item AS XML)
FROM dbo.SplitString(@str, '<br />') AS [Changes]
WHERE LEN(item) > 0
Result:- Result
结果:结果
So, the final query will be:-
因此,最终的查询将是:-
INSERT INTO ChangeTracker
SELECT GETDATE()
,tb.xmldata.value('(/span[@class="fieldname"]/text())[1]', 'varchar(255)') AS fieldName
,tb.xmldata.value('(/span[@class="oldvalue"]/text())[1]', 'varchar(255)') AS oldValue
,tb.xmldata.value('(/span[@class="newvalue"]/text())[1]', 'varchar(255)') AS newValue
FROM(
SELECT CAST(Item AS XML) AS xmldata
FROM dbo.SplitString(@str, '<br />') AS [Changes]
WHERE LEN(item) > 0
) AS tb
Final Result:- Final Result
最终结果:最终结果
Note:- You need to create split function, please find below.BEGIN
注意:-您需要创建拆分函数,请在下面找到
CREATE FUNCTION SplitString
(
@Input NVARCHAR(MAX),
@Character NVARCHAR(10)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + LEN(@Character), LEN(@Input))
END
RETURN
END