3. FOR XML (SQL Server) : SELECT 查询将结果作为行集返回

天祈

2022-06-13 / 0 评论 / 217 阅读 / 正在检测是否收录...

06/13

温馨提示：

本文最后更新于2022年06月25日，已超过1020天没有更新，若内容或图片失效，请留言反馈。

环境要求

基本初识

测试数据库：

CREATE TABLE [dbo].[userTable](
    [id] [nvarchar](50) NULL,
    [encode] [nvarchar](50) NULL,
    [name] [nvarchar](50) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[userTable] ADD  CONSTRAINT [DF_user_id]  DEFAULT (newid()) FOR [id]
GO

INSERT [dbo].[userTable] ([id], [encode], [name]) VALUES (N'1A074FE5-BAB3-4B71-932D-393387E94B5F', N'001', N'张三')
GO
INSERT [dbo].[userTable] ([id], [encode], [name]) VALUES (N'F75F5FA1-D8A1-4076-B840-99941DB9038F', N'002', N'李四')
GO
INSERT [dbo].[userTable] ([id], [encode], [name]) VALUES (N'FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4', N'003', N'王五')
GO
INSERT [dbo].[userTable] ([id],[encode], [name]) VALUES (N'79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A', N'004',null)

id	encode	name
1A074FE5-BAB3-4B71-932D-393387E94B5F	001	张三
F75F5FA1-D8A1-4076-B840-99941DB9038F	002	李四
FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4	003	王五
79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A	004	NULL

使用示例：

RAW模式

示例一 : 基本使用

select * from userTable  for xml raw ;

<!--普通使用-->
<row id="1A074FE5-BAB3-4B71-932D-393387E94B5F" encode="001" name="张三" />
<row id="F75F5FA1-D8A1-4076-B840-99941DB9038F" encode="002" name="李四" />
<row id="FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4" encode="003" name="王五" />
<row id="79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A" encode="004" />

示例二 : ELEMENTS 指令

select * from userTable for xml raw,ELEMENTS  ;

<!--ELEMENTS指令：检索以元素为中心的 XML-->
<row>
  <id>1A074FE5-BAB3-4B71-932D-393387E94B5F</id>
  <encode>001</encode>
  <name>张三</name>
</row>
<row>
  <id>F75F5FA1-D8A1-4076-B840-99941DB9038F</id>
  <encode>002</encode>
  <name>李四</name>
</row>
<row>
  <id>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</id>
  <encode>003</encode>
  <name>王五</name>
</row>
<row>
  <id>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</id>
  <encode>004</encode>
</row>

示例三 : XSINIL 添加指令 ELEMENTS

select * from userTable for xml raw,ELEMENTS XSINIL ;

<!--XSINIL 添加指令 ELEMENTS：对为 Null 值的数据生成XML-->
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>1A074FE5-BAB3-4B71-932D-393387E94B5F</id>
  <encode>001</encode>
  <name>张三</name>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>F75F5FA1-D8A1-4076-B840-99941DB9038F</id>
  <encode>002</encode>
  <name>李四</name>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</id>
  <encode>003</encode>
  <name>王五</name>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</id>
  <encode>004</encode>
  <name xsi:nil="true" />
</row>

示例四 : XMLDATA 指令

select * from userTable for xml raw,XMLDATA  ;

<!-- 返回描述文档结构的 XML-DATA 架构 -->
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="empty" model="closed">
    <AttributeType name="id" dt:type="string" />
    <AttributeType name="encode" dt:type="string" />
    <AttributeType name="name" dt:type="string" />
    <attribute type="id" />
    <attribute type="encode" />
    <attribute type="name" />
  </ElementType>
</Schema>
<row xmlns="x-schema:#Schema1" id="1A074FE5-BAB3-4B71-932D-393387E94B5F" encode="001" name="张三" />
<row xmlns="x-schema:#Schema1" id="F75F5FA1-D8A1-4076-B840-99941DB9038F" encode="002" name="李四" />
<row xmlns="x-schema:#Schema1" id="FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4" encode="003" name="王五" />
<row xmlns="x-schema:#Schema1" id="79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A" encode="004" />

其他

AUTO 模式

示例一:

select * from userTable x for xml AUTO;

<!-- 可以为表设置别名 同时结合上述指令  -->
<userTable id="1A074FE5-BAB3-4B71-932D-393387E94B5F" encode="001" name="张三" />
<userTable id="F75F5FA1-D8A1-4076-B840-99941DB9038F" encode="002" name="李四" />
<userTable id="FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4" encode="003" name="王五" />
<userTable id="79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A" encode="004" />

示例二:

elect OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       OrderHeader.Status,
       Cust.CustomerID,
       Cust.CustomerType
from Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
where Cust.CustomerID = OrderHeader.CustomerID
for xml auto;

<!-- 可结合 ELEMENTS 等其他指令-->
<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5">
  <Cust CustomerID="1" CustomerType="S" />
</OrderHeader>
...

PATH 模式

示例一

select * from userTable   for xml PATH

<row>
  <id>1A074FE5-BAB3-4B71-932D-393387E94B5F</id>
  <encode>001</encode>
  <name>张三</name>
</row>
<row>
  <id>F75F5FA1-D8A1-4076-B840-99941DB9038F</id>
  <encode>002</encode>
  <name>李四</name>
</row>
<row>
  <id>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</id>
  <encode>003</encode>
  <name>王五</name>
</row>
<row>
  <id>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</id>
  <encode>004</encode>
</row>

示例二

select * from userTable  for xml PATH('') ;

<id>1A074FE5-BAB3-4B71-932D-393387E94B5F</id>
<encode>001</encode>
<name>张三</name>
<id>F75F5FA1-D8A1-4076-B840-99941DB9038F</id>
<encode>002</encode>
<name>李四</name>
<id>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</id>
<encode>003</encode>
<name>王五</name>
<id>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</id>
<encode>004</encode>

示例三

select id as '@part',encode as '@code',name  from userTable  for xml PATH

<row part="1A074FE5-BAB3-4B71-932D-393387E94B5F" code="001">
  <name>张三</name>
</row>
<row part="F75F5FA1-D8A1-4076-B840-99941DB9038F" code="002">
  <name>李四</name>
</row>
<row part="FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4" code="003">
  <name>王五</name>
</row>
<row part="79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A" code="004" />

示例四

select ID as 'ID1/ID2',ENCODE,NAME  from userTable  for xml PATH

<row>
  <ID1>
    <ID2>1A074FE5-BAB3-4B71-932D-393387E94B5F</ID2>
  </ID1>
  <ENCODE>001</ENCODE>
  <NAME>张三</NAME>
</row>
<row>
  <ID1>
    <ID2>F75F5FA1-D8A1-4076-B840-99941DB9038F</ID2>
  </ID1>
  <ENCODE>002</ENCODE>
  <NAME>李四</NAME>
</row>
<row>
  <ID1>
    <ID2>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</ID2>
  </ID1>
  <ENCODE>003</ENCODE>
  <NAME>王五</NAME>
</row>
<row>
  <ID1>
    <ID2>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</ID2>
  </ID1>
  <ENCODE>004</ENCODE>
</row>

示例五

select ID as '*',ENCODE,NAME  from userTable  for xml PATH

<row>1A074FE5-BAB3-4B71-932D-393387E94B5F<ENCODE>001</ENCODE><NAME>张三</NAME></row>
<row>F75F5FA1-D8A1-4076-B840-99941DB9038F<ENCODE>002</ENCODE><NAME>李四</NAME></row>
<row>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4<ENCODE>003</ENCODE><NAME>王五</NAME></row>
<row>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A<ENCODE>004</ENCODE></row>

示例六

列名	行为
text()	对于名为 text() 的列，该列中的字符串值将被添加为文本节点。
comment()	对于名为 comment() 的列，该列中的字符串值将被添加为 XML 注释。
node()	对于节点名称 () 的列，结果与列名称是通配符 () * 时的结果相同。
处理指令（名称）	如果列名为处理指令，该列中的字符串值将被添加为此处理指令目标名称的 PI 值。

select ID as 'text()',ENCODE as 'comment()',NAME as 'node()'  from userTable  for xml PATH

<row>1A074FE5-BAB3-4B71-932D-393387E94B5F<!--001-->张三</row>
<row>F75F5FA1-D8A1-4076-B840-99941DB9038F<!--002-->李四</row>
<row>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4<!--003-->王五</row>
<row>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A<!--004--></row>

示例七

select ','+ID   from userTable  for xml PATH('')

,1A074FE5-BAB3-4B71-932D-393387E94B5F,F75F5FA1-D8A1-4076-B840-99941DB9038F,FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4,79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A

3. FOR XML (SQL Server) : SELECT 查询将结果作为行集返回

环境要求

基本初识

RAW 模式：

AUTO 模式：

EXPLICIT模式：

PATH模式：

测试数据库：

使用示例：

RAW模式

示例一 : 基本使用

示例二 : ELEMENTS 指令

示例三 : XSINIL 添加指令 ELEMENTS

示例四 : XMLDATA 指令

其他

AUTO 模式

示例一:

示例二:

PATH 模式

示例一

示例二

示例三

示例四

示例五

示例六

示例七

EXPLICIT 模式

16. SQLServer : 函数

11. SQLServer : 运算符

10. SQLServer : 表达式

9. SQLServer : 语言元素-控制流

评论 (0)