将XML数据转换为行和列

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

It is very good that you have   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

We can do by comma split approach. Here we need just need to split string by " ".

我们可以用逗号分隔法。这里我们只需要用“
”来拆分字符串。

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