使用 SQL 压缩 xml 文本列的最佳方法？

Question

使用 Microsoft SQL Server 2019。 我有两列，一列文本代表一些 xml，另一列 varbinary(max) 代表已经压缩的 xml，我需要对其进行压缩。 请假设我无法更改源数据，但可以根据需要在代码中进行转换。

我想压缩文本列，最初它工作正常，但如果我尝试将其保存到临时表中以便在该过程中进一步使用，我会得到奇怪的字符，例如 < 或 tŠÌK'À3û€Í‚;jw 。同样，我制作的第一个临时表存储得很好，我可以选择初始表并且它显示正确压缩。但是，如果我需要将其拉入辅助临时表或变量中，它就会变得一团糟。

我尝试过转换为几种不同的格式，在过程中稍后进行转换，并在最后阶段引入该列的源数据，但我的最终目标是填充一个将转换为 JSON 的变量，并且它总是那里也很奇怪。我只需要在查看我制作的 json 变量时正确显示列的压缩版本。 关于如何解决这个问题有什么建议吗？

Answer 1

排序问题？

这有点排序问题的味道。 tempdb 实际上是它自己的数据库，具有自己的默认排序规则和其他设置。

1. 在一个具有默认 Collat​​ionA 的数据库中，您调用 COMPRESS(NvarcharData) 并生成一些 VARBINARY。
2. 在其他具有默认 Collat​​ionB 的数据库 (tempdb) 中，您可以调用 CONVERT(NVARCHAR(MAX), DECOMPRESS(CompressedData))。现在，幕后发生的事情是：
   • CompressedData 被解压缩为 VARBINARY，表示 Collat​​ionA 中的 NvarcharData
   • 将 VARBINARY 转换为 NVARCHAR 假设二进制数据表示 Collat​​ionB< 中的 NVARCHAR 数据/code>，这不是真的！

   


   


尝试在 XML、VARBINARY 和 (N)VARCHAR 之间进行更明确的转换（排序规则、数据类型）。

双重压缩？

我还注意到“代表已经压缩的xml，我需要压缩”。如果您正在双重压缩，也许您忘记了双重解压？

示例？

遗憾的是，您缺少一个示例，但我已经制作了适合我的在 XML 和压缩数据之间进行转换的最小示例。

BEGIN TRANSACTION
GO

CREATE TABLE dbo.XmlData_Base (
  PrimaryKey INTEGER NOT NULL IDENTITY(1, 1),
  XmlCompressed VARBINARY(MAX) NULL
);
GO

CREATE OR ALTER VIEW dbo.XmlData
WITH SCHEMABINDING
AS
  SELECT
    BASE.PrimaryKey,
    CONVERT(XML, DECOMPRESS(BASE.XmlCompressed)) AS XmlData
  FROM
    dbo.XmlData_Base AS BASE;
GO

CREATE OR ALTER TRIGGER dbo.TR_XmlData_instead_I
  ON dbo.XmlData
  INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO dbo.XmlData_Base
    (XmlCompressed)
  SELECT
    COMPRESS(CONVERT(VARBINARY(MAX), I.XmlData))
  FROM
    Inserted AS I;
END;
GO

CREATE OR ALTER TRIGGER dbo.TR_XmlData_instead_U
  ON dbo.XmlData
  INSTEAD OF UPDATE
AS
BEGIN
  UPDATE BASE
  SET
    BASE.XmlCompressed = COMPRESS(CONVERT(VARBINARY(MAX), I.XmlData))
  FROM
    dbo.XmlData_Base AS BASE
    JOIN Inserted AS I ON I.PrimaryKey = BASE.PrimaryKey;
END;
GO

INSERT INTO dbo.XmlData
  (XmlData)
VALUES
  (CONVERT(XML, N'<this><I>I call upon thee!</I></this>'));

SELECT
  *
FROM
  dbo.XmlData;

SELECT
  PrimaryKey,
  XmlCompressed,
  CONVERT(XML, DECOMPRESS(XmlCompressed))
FROM
  dbo.XmlData_Base;

UPDATE dbo.XmlData
SET
  XmlData = CONVERT(XML, N'<that><I>I call upon thee!</I></that>');

SELECT
  *
FROM
  dbo.XmlData;

SELECT
  PrimaryKey,
  XmlCompressed,
  CONVERT(XML, DECOMPRESS(XmlCompressed))
FROM
  dbo.XmlData_Base;
GO

ROLLBACK TRANSACTION;