如何将定义报表的XSD文件转换为可查询的SQL Server表？

方法一：SQL Server XQuery直接解析XSD #

1. 存储XSD内容 #

先创建表存储XSD文件内容，再导入XSD数据：

CREATE TABLE XSDContent (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    XSDData XML
);

-- 导入本地XSD文件（替换为你的文件路径）
INSERT INTO XSDContent (XSDData)
SELECT * FROM OPENROWSET(BULK 'C:\YourXSDFile.xsd', SINGLE_BLOB) AS XSD;

2. 解析并生成目标表数据 #

创建目标表，通过XQuery提取simpleType的字段信息，处理枚举值（每个枚举生成一行）：

-- 创建目标表结构
CREATE TABLE XSDToSQL (
    Field_Name NVARCHAR(255),
    [Type] NVARCHAR(255),
    [Length] INT,
    enumeration NVARCHAR(255),
    MaxLen INT,
    MinLen INT
);

-- 声明XSD命名空间（根据你的XSD实际命名空间调整）
WITH XMLNAMESPACES (
    DEFAULT 'http://www.w3.org/2001/XMLSchema',
    'http://your-custom-namespace' AS ns
)
INSERT INTO XSDToSQL
SELECT
    x.value('@name', 'NVARCHAR(255)') AS Field_Name,
    -- 移除命名空间前缀，保留类型名称
    REPLACE(x.value('(xs:restriction/@base)[1]', 'NVARCHAR(255)'), 'ns:', '') AS [Type],
    x.value('(xs:restriction/xs:length/@value)[1]', 'INT') AS [Length],
    enum.value('@value', 'NVARCHAR(255)') AS enumeration,
    x.value('(xs:restriction/xs:maxLength/@value)[1]', 'INT') AS MaxLen,
    x.value('(xs:restriction/xs:minLength/@value)[1]', 'INT') AS MinLen
FROM XSDContent
CROSS APPLY XSDData.nodes('//xs:simpleType') AS XT(x)
-- 处理枚举：有枚举则每行对应一个枚举值，无枚举则生成NULL行
OUTER APPLY x.nodes('xs:restriction/xs:enumeration') AS EnumNodes(enum);

3. 处理正则模式（xs:pattern） #

对于带xs:pattern的类型（如[0-9]{10}），可以提取模式并计算长度，扩展查询如下：

WITH XMLNAMESPACES (
    DEFAULT 'http://www.w3.org/2001/XMLSchema',
    'http://your-custom-namespace' AS ns
)
INSERT INTO XSDToSQL
SELECT
    x.value('@name', 'NVARCHAR(255)') AS Field_Name,
    REPLACE(x.value('(xs:restriction/@base)[1]', 'NVARCHAR(255)'), 'ns:', '') AS [Type],
    -- 从pattern中提取固定长度（如{10}）
    CASE 
        WHEN x.exist('xs:restriction/xs:pattern') = 1 THEN
            CAST(SUBSTRING(
                x.value('(xs:restriction/xs:pattern/@value)[1]', 'NVARCHAR(255)'),
                CHARINDEX('{', x.value('(xs:restriction/xs:pattern/@value)[1]', 'NVARCHAR(255)')) + 1,
                CHARINDEX('}', x.value('(xs:restriction/xs:pattern/@value)[1]', 'NVARCHAR(255)')) - CHARINDEX('{', x.value('(xs:restriction/xs:pattern/@value)[1]', 'NVARCHAR(255)')) - 1
            ) AS INT)
        ELSE x.value('(xs:restriction/xs:length/@value)[1]', 'INT') 
    END AS [Length],
    enum.value('@value', 'NVARCHAR(255)') AS enumeration,
    x.value('(xs:restriction/xs:maxLength/@value)[1]', 'INT') AS MaxLen,
    x.value('(xs:restriction/xs:minLength/@value)[1]', 'INT') AS MinLen
FROM XSDContent
CROSS APPLY XSDData.nodes('//xs:simpleType') AS XT(x)
OUTER APPLY x.nodes('xs:restriction/xs:enumeration') AS EnumNodes(enum);

方法二：PowerShell脚本解析并生成SQL #

如果XSD结构复杂，用PowerShell结合.NET类库解析更灵活：

$xsdPath = "C:\YourXSDFile.xsd"
$schema = New-Object System.Xml.Schema.XmlSchemaSet
$schema.Add($null, $xsdPath)
$schema.Compile()

# 生成SQL插入语句
$sqlHeader = "INSERT INTO XSDToSQL (Field_Name, [Type], [Length], enumeration, MaxLen, MinLen) VALUES`n"
$valuesList = @()

foreach ($type in $schema.GlobalTypes.Values | Where-Object { $_ -is [System.Xml.Schema.XmlSchemaSimpleType] }) {
    $restriction = $type.Content
    if ($restriction -is [System.Xml.Schema.XmlSchemaSimpleTypeRestriction]) {
        $fieldName = $type.Name
        $baseType = $restriction.BaseTypeName.Name
        $length = $restriction.Facets | Where-Object { $_ -is [System.Xml.Schema.XmlSchemaLengthFacet] } | Select-Object -ExpandProperty Value
        $maxLen = $restriction.Facets | Where-Object { $_ -is [System.Xml.Schema.XmlSchemaMaxLengthFacet] } | Select-Object -ExpandProperty Value
        $minLen = $restriction.Facets | Where-Object { $_ -is [System.Xml.Schema.XmlSchemaMinLengthFacet] } | Select-Object -ExpandProperty Value
        $enums = $restriction.Facets | Where-Object { $_ -is [System.Xml.Schema.XmlSchemaEnumerationFacet] } | Select-Object -ExpandProperty Value

        if ($enums.Count -gt 0) {
            foreach ($enum in $enums) {
                $valuesList += "('$fieldName', '$baseType', $length, '$enum', $maxLen, $minLen)"
            }
        } else {
            $valuesList += "('$fieldName', '$baseType', $length, NULL, $maxLen, $minLen)"
        }
    }
}

$sql = $sqlHeader + ($valuesList -join ",`n") + ";"
$sql | Out-File "C:\GeneratedXSDToSQL.sql"

执行生成的SQL脚本即可完成数据插入。

排查返回Null的问题 #

• 命名空间不匹配：确保XQuery中的WITH XMLNAMESPACES声明与XSD中的命名空间完全一致，包括前缀。
• XPath路径错误：检查XSD节点名称（如xs:simpleType）是否与XQuery中的路径匹配，避免嵌套节点遗漏。
• XSD合法性问题：先验证XSD文件是否可正常解析（用XML编辑器打开检查语法错误），避免无效XML导致解析失败。

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