SQL for XML

news/2024/11/28 0:17:26/
xmlns="http://www.w3.org/2000/svg" style="display: none;">

关系数据模型与SQL

SQL for XML

模式名功能
RAW返回的行作为元素,列值作为元素的属性
AUTO返回表名对应节点名称的元素,每列的属性作为元素的属性输出输出,可形成简单嵌套结构
EXPLICIT通过SELECT语法定义输出XML结构
PATH列名或列别名作为XPATH表达式来处理

SQL Server中的FOR XML子句允许开发者将查询结果转换成XML格式。这一特性非常实用,特别是在需要将关系型数据以XML格式交换或存储时。FOR XML子句支持四种模式:AUTORAWEXPLICITPATH。每种模式都有其特点和适用场景。下面我们将详细介绍这四种模式的基本语法和用法,结合例子进行说明。
我们先设计一个简单的关系表,并基于这个表创建四种 SQL for XML 模式的语句示例: RAW, AUTO, EXPLICIT, 和 PATH

示例关系表:Employees

EmployeeIDNamePositionDepartment
1AliceEngineerIT
2BobManagerHR
3CharlieAnalystFinance

以下是 SQL for XML 的四种模式的例子:

1. RAW 模式

直接将每一行封装为一个 <row> 元素:

sql">SELECT EmployeeID, Name, Position, Department
FROM Employees
FOR XML RAW;

输出示例

xml"><row EmployeeID="1" Name="Alice" Position="Engineer" Department="IT" />
<row EmployeeID="2" Name="Bob" Position="Manager" Department="HR" />
<row EmployeeID="3" Name="Charlie" Position="Analyst" Department="Finance" />

2. AUTO 模式

根据查询中指定的列和表的关系,自动生成嵌套结构:

sql">SELECT EmployeeID, Name, Position, Department
FROM Employees
FOR XML AUTO;

输出示例

xml"><Employees EmployeeID="1" Name="Alice" Position="Engineer" Department="IT" />
<Employees EmployeeID="2" Name="Bob" Position="Manager" Department="HR" />
<Employees EmployeeID="3" Name="Charlie" Position="Analyst" Department="Finance" />

如果包含表的层级关系,例如部门和员工:

sql">SELECT Department, EmployeeID, Name
FROM Employees
FOR XML AUTO, ELEMENTS;

输出示例(嵌套结构):

xml"><Employees><Department>IT</Department><EmployeeID>1</EmployeeID><Name>Alice</Name>
</Employees>
<Employees><Department>HR</Department><EmployeeID>2</EmployeeID><Name>Bob</Name>
</Employees>

3. EXPLICIT 模式

需要通过特定的列结构明确指定 XML 层级。规则:元数据列是SELECT查询必须先生成的满足规定要求的前两列,是Tag列和Parent列,作用是为结果提供层次信息:

  • 第1列,列名固定为Tag,值是一个对应当前元素的标记号(整数类型)。 查询必须为从行集构造的每个元素提供标记号。
  • 第2列,列名固定为Parent,值是父元素的标记号。Parent 列值为0或NULL表明相应的元素没有父级,该元素将作为顶级元素添加到 XML。

其他数据列名的指定方式ElementName!TagNumber!AttributeName!Directive,其中Directive 是可选的,提供有关 XML 构造的其他信息
注意:这种模式要求一个复杂的 UNION ALL 查询来构建 XML 的层级。

示例
sql">SELECT 1 AS Tag,NULL AS Parent,EmployeeID AS [Employees!1!EmployeeID],Name AS [Employees!1!Name],NULL AS [Department!2!Department]
FROM Employees
UNION ALL
SELECT 2 AS Tag,1 AS Parent,NULL AS [Employees!1!EmployeeID],NULL AS [Employees!1!Name],Department AS [Department!2!Department]
FROM Employees
ORDER BY [Employees!1!EmployeeID]
FOR XML EXPLICIT;

输出示例

xml"><Employees EmployeeID="1" Name="Alice"><Department>IT</Department>
</Employees>
<Employees EmployeeID="2" Name="Bob"><Department>HR</Department>
</Employees>
<Employees EmployeeID="3" Name="Charlie"><Department>Finance</Department>
</Employees>

4. PATH 模式

允许自定义 XML 的元素和属性的路径,并且可以更灵活地指定输出。

示例 1:直接生成简单路径
sql">SELECT EmployeeID AS "Employee/@ID",Name AS "Employee/Name",Position AS "Employee/Position"
FROM Employees
FOR XML PATH;

输出示例

xml"><Employee ID="1"><Name>Alice</Name><Position>Engineer</Position>
</Employee>
<Employee ID="2"><Name>Bob</Name><Position>Manager</Position>
</Employee>
<Employee ID="3"><Name>Charlie</Name><Position>Analyst</Position>
</Employee>
示例 2:PATH()传入层级关系
sql">SELECT ProductModelID,Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('ProductModel');

输出示例

xml"><ProductModel><ProductModelID>122</ProductModelID><Name>All-Purpose Bike Stand</Name>
</ProductModel>
<ProductModel><ProductModelID>119</ProductModelID><Name>Bike Wash</Name>
</ProductModel>
sql">SELECT ProductModelID,Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('');

输出示例

xml"><ProductModelID>122</ProductModelID>
<Name>All-Purpose Bike Stand</Name>
<ProductModelID>119</ProductModelID>
<Name>Bike Wash</Name>
示例 3:嵌套路径
sql">SELECT Department AS "Department",(SELECT EmployeeID AS "ID", Name AS "Name"FROM Employees E2WHERE E1.Department = E2.DepartmentFOR XML PATH('Employee'), TYPE) AS "Employees"
FROM Employees E1
GROUP BY Department
FOR XML PATH('Department');

输出示例

xml"><Department><Department>IT</Department><Employees><Employee><ID>1</ID><Name>Alice</Name></Employee></Employees>
</Department>
<Department><Department>HR</Department><Employees><Employee><ID>2</ID><Name>Bob</Name></Employee></Employees>
</Department>

让我们通过一个具体的例子来说明 RAWAUTO 模式的区别,尤其是 AUTO 模式如何形成层次关系

假设有两张表:DepartmentsEmployees

Departments
DepartmentIDDepartmentName
1IT
2HR
Employees
EmployeeIDNamePositionDepartmentID
1AliceEngineer1
2BobManager2
3EveAnalyst1

1. 使用 RAW 模式

RAW 模式会将每一行数据封装为 <row>,不形成嵌套关系:

SQL 查询
sql">SELECT D.DepartmentName, E.EmployeeID, E.Name, E.Position
FROM Departments D
JOIN Employees E ON D.DepartmentID = E.DepartmentID
FOR XML RAW;
输出 XML
xml"><row DepartmentName="IT" EmployeeID="1" Name="Alice" Position="Engineer" />
<row DepartmentName="HR" EmployeeID="2" Name="Bob" Position="Manager" />
<row DepartmentName="IT" EmployeeID="3" Name="Eve" Position="Analyst" />
  • 每一行数据以 <row> 包裹。
  • 没有层次结构DepartmentsEmployees 的信息在同一级别。

2. 使用 AUTO 模式

AUTO 模式根据表之间的关系自动生成层次结构,从左到右的表按嵌套顺序排列:

SQL 查询
sql">SELECT D.DepartmentName, E.EmployeeID, E.Name, E.Position
FROM Departments D
JOIN Employees E ON D.DepartmentID = E.DepartmentID
FOR XML AUTO;
输出 XML
xml"><Departments DepartmentName="IT"><Employees EmployeeID="1" Name="Alice" Position="Engineer" /><Employees EmployeeID="3" Name="Eve" Position="Analyst" />
</Departments>
<Departments DepartmentName="HR"><Employees EmployeeID="2" Name="Bob" Position="Manager" />
</Departments>
  • 层次结构Departments 成为父节点,Employees 成为子节点。
  • 嵌套关系由表的连接顺序(从左到右)决定:Departments → Employees

总结 RAW 与 AUTO 的区别

特性RAW 模式AUTO 模式
输出结构每行数据一个 <row> 元素自动根据表关系生成层次结构
节点名称固定为 <row>节点名称根据表名自动生成
多表嵌套支持不支持,所有数据在同一层级支持,通过表连接顺序生成嵌套
使用场景简单的平面数据输出需要自动生成父子关系、层次结构的场景

http://www.ppmy.cn/news/1550469.html

相关文章

C++中虚继承为什么可以解决菱形继承的数据冗余问题

在C中菱形继承会有数据冗余的问题发生&#xff0c;我们可以使用虚继承来解决&#xff0c;那虚继承的原理是什么&#xff0c;为什么它可以解决这个问题。 菱形继承的数据冗余问题 class A { public:int data; };class B : public A {};class C : public A {};class D : public…

eduSRC挖洞思路

声明 学习视频来自 B 站UP主泷羽sec&#xff0c;如涉及侵权马上删除文章。 笔记的只是方便各位师傅学习知识&#xff0c;以下网站只涉及学习内容&#xff0c;其他的都与本人无关&#xff0c;切莫逾越法律红线&#xff0c;否则后果自负。 ✍&#x1f3fb;作者简介&#xff1a;致…

重写get方法导致json序列化之后值改变的问题

重写get方法导致json序列化之后值改变的问题 问题描述 在开发中&#xff0c;我发现有一个接口某一个字段返回的值和数据库中的实际值并不一样 数据库中值: id diameter 1 10接口返回值: {"id": 1,"diameter": 5 }问题排查 发现&#xff0c;在getDiame…

10 —— Webpack打包模式

开发模式&#xff1a;development &#xff1b;场景&#xff1a;本地开发 生产模式&#xff1a;production &#xff1b; 场景&#xff1a;打包上线 这两种模式如何设置给webpack&#xff1a; 方式1.webpack.config.js 配置文件设置mode选项 module.exports { mode:produc…

tsx中使用el-dialog设置visible.sync,解决弹窗右上角叉号失效问题

在 TSX 中使用 el-dialog 设置 visible.sync 在 Vue 2 中&#xff0c;.sync 修饰符用于在父子组件之间同步属性值。然而&#xff0c;在 TSX 中并没有类似的 .sync 修饰符。如果要在 TSX 中实现类似的功能&#xff0c;需要手动实现。具体来说&#xff0c;可以通过监听 update:v…

Jackson 对象与json数据互转工具类JacksonUtil

下面是一个基于 Jackson 的工具类 JacksonUtil&#xff0c;用于在 Java 项目中实现对象与 JSON 数据之间的互相转换。该工具类具有简洁、易用、通用的特点。 package com.fy.common.util;import com.fasterxml.jackson.core.JsonGenerator; import com.fasterxml.jackson.core…

SQL99版外连接

外连接 看这样的场景&#xff0c;在ta和tb两表中查询没有对应年龄数据的学生姓名和年龄 SELECT tb.name,ta.age FROM tb INNER JOIN ta ON tb.ta_idta.id WHERE ta.id IS NULL; 结果没有,所以前面的查询是解决不了这种问题&#xff01;&#xff01;&#xff01; 所以外连接…

Spring Boot 核心逻辑与工作原理详解

当然可以&#xff01;下面是一篇关于 Spring Boot 逻辑的文章&#xff0c;涵盖了其核心概念、主要特点和工作原理。 Spring Boot 的逻辑与工作原理 1. 引言 Spring Boot 是一个基于 Java 的框架&#xff0c;旨在简化新 Spring 应用的初始搭建和开发过程。它通过提供默认配置和…