如何在SQL Server图架构中存储XML Schema及数据并支持XML/JSON导出？

我需要在SQL Server数据库中存储XML Schema及其对应数据，且能将数据导出为XML或JSON文件。试过两种方案都有明显缺陷：

• 传统关系型方案：用自引用parentId表示XML层级，但数据量大时递归查询性能急剧下降；
• SQL Server图架构方案：将每个XML元素设为节点表，属性作为列，通过isParentOf边表关联层级，但每个元素单独做节点导致查询操作过于繁琐。

我知道XML Schema和数据库结构没有直接对应关系，也清楚这个问题的复杂性，想请教能不能通过SQL图数据库实现需求——它看起来最适合定义元素并创建各类关系。

示例XML数据：

<?xml version="1.0" encoding="utf-8"?>
<Document xmlns='http://mydocument.com/schema/1'>
  <BankStatement frequency='monthly'>
    <Customer>
      <AcctNo>012-3456789</AcctNo>
      <Name type="full">John Doe</Name>
      <Street>123 Street Road</Street>
      <City>London</City>
    </Customer>
    <BeginDate>18/10/2022</BeginDate>
    <EndDate>18/11/2022</EndDate>
  </BankStatement>
</Document>

优化SQL图数据库的实现方式 #

之前的图架构设计问题在于每个XML元素单独建节点表，导致查询繁琐。可以调整为通用化的图结构设计：

1. 统一节点表存储所有XML元素 #

创建一个通用的XMLElement节点表，收纳所有元素的共性字段：

CREATE TABLE XMLElement (
    ElementId INT IDENTITY(1,1) PRIMARY KEY,
    ElementName NVARCHAR(100) NOT NULL, -- 元素名，如Document、BankStatement
    ElementValue NVARCHAR(MAX), -- 元素的文本值，如John Doe、18/10/2022
    SchemaNamespace NVARCHAR(200) -- XML命名空间，如http://mydocument.com/schema/1
) AS NODE;

2. 单独节点表存储元素属性 #

用ElementAttribute节点表统一管理所有元素的属性：

CREATE TABLE ElementAttribute (
    AttributeId INT IDENTITY(1,1) PRIMARY KEY,
    AttributeName NVARCHAR(100) NOT NULL, -- 属性名，如frequency、type
    AttributeValue NVARCHAR(MAX) NOT NULL -- 属性值，如monthly、full
) AS NODE;

3. 用边表建立两类核心关系 #

• IsParentOf：连接父元素与子元素，体现XML层级结构
• HasAttribute：连接元素与其关联的属性

CREATE TABLE IsParentOf AS EDGE;
CREATE TABLE HasAttribute AS EDGE;

4. 插入示例数据的参考语句 #

以提供的XML为例，插入节点和关联边的示例：

-- 插入根元素Document
INSERT INTO XMLElement (ElementName, SchemaNamespace) VALUES ('Document', 'http://mydocument.com/schema/1');
DECLARE @DocId INT = SCOPE_IDENTITY();

-- 插入BankStatement元素
INSERT INTO XMLElement (ElementName) VALUES ('BankStatement');
DECLARE @StmtId INT = SCOPE_IDENTITY();
-- 建立父层级关系：Document -> BankStatement
INSERT INTO IsParentOf ($from_id, $to_id) VALUES ((SELECT $node_id FROM XMLElement WHERE ElementId = @DocId), (SELECT $node_id FROM XMLElement WHERE ElementId = @StmtId));
-- 插入BankStatement的frequency属性
INSERT INTO ElementAttribute (AttributeName, AttributeValue) VALUES ('frequency', 'monthly');
DECLARE @FreqAttrId INT = SCOPE_IDENTITY();
-- 建立属性关联：BankStatement -> frequency
INSERT INTO HasAttribute ($from_id, $to_id) VALUES ((SELECT $node_id FROM XMLElement WHERE ElementId = @StmtId), (SELECT $node_id FROM ElementAttribute WHERE AttributeId = @FreqAttrId));

-- Customer、AcctNo等元素与属性的插入逻辑可依此类推

5. 查询与导出实现 #

通过图查询获取层级结构，再拼接成XML或JSON：

• 导出XML示例：

WITH XMLHierarchy AS (
    SELECT 
        e.ElementName,
        e.ElementValue,
        (SELECT a.AttributeName, a.AttributeValue FROM ElementAttribute a
         JOIN HasAttribute ha ON ha.$to_id = a.$node_id
         WHERE ha.$from_id = e.$node_id
         FOR XML PATH('Attribute'), TYPE) AS Attributes,
        (SELECT * FROM XMLHierarchy child
         JOIN IsParentOf ip ON ip.$from_id = e.$node_id AND ip.$to_id = child.$node_id
         FOR XML PATH('Element'), TYPE) AS Children
    FROM XMLElement e
    WHERE e.ElementName = 'Document'
)
SELECT * FROM XMLHierarchy FOR XML PATH(''), ROOT('Document'), TYPE;

• 导出JSON示例：

WITH JSONHierarchy AS (
    SELECT 
        e.ElementName AS [name],
        e.ElementValue AS [value],
        (SELECT a.AttributeName AS [key], a.AttributeValue AS [value] FROM ElementAttribute a
         JOIN HasAttribute ha ON ha.$to_id = a.$node_id
         WHERE ha.$from_id = e.$node_id
         FOR JSON PATH) AS attributes,
        (SELECT * FROM JSONHierarchy child
         JOIN IsParentOf ip ON ip.$from_id = e.$node_id AND ip.$to_id = child.$node_id
         FOR JSON PATH) AS children
    FROM XMLElement e
    WHERE e.ElementName = 'Document'
)
SELECT * FROM JSONHierarchy FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

其他可选方案 #

如果图架构的维护成本仍偏高，可考虑以下替代方案：

1. SQL Server原生XML类型 #

直接用XML类型字段存储完整XML数据，同时单独存储XML Schema：

CREATE TABLE XMLDataStore (
    DataId INT IDENTITY(1,1) PRIMARY KEY,
    SchemaId INT REFERENCES XMLSchemas(SchemaId),
    XMLContent XML NOT NULL
);

CREATE TABLE XMLSchemas (
    SchemaId INT IDENTITY(1,1) PRIMARY KEY,
    SchemaNamespace NVARCHAR(200) NOT NULL UNIQUE,
    SchemaContent XML NOT NULL
);

这种方式存储和导出都很便捷，SQL Server原生支持XML的查询、Schema验证，还可创建XML索引优化大数量场景下的性能。

2. 混合关系型+XML模式 #

对XML中固定结构的部分用关系表存储，动态/可变结构部分用XML字段存储，兼顾查询性能与灵活性。

内容的提问来源于stack exchange，提问作者Cragly