SQL FO君越 XML PATH

   FO宝马X3 XML PATH
,其实它就是将查询结果集以XML方式表现,将多行的结果,体现在同风流洒脱行。

转载:https://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html

    上面我们来写二个事例:

 FOEnclave XML PATH
有的人恐怕清楚某一个人大概不明了,其实它就是将查询结果集以XML情势呈现,有了它大家得以简化大家的查询语句实现部分在先只怕需要借助函数活存储进度来完成的办事。那么以多个实例为主.

        纵然大家有个办事流程表:

        意气风发.FO昂科威 XML PATH 简介

    图片 1图片 2

             那就是说依旧率先来介绍一下FOEscort XML PATH
,假诺现在有一张兴趣爱好表(hobby)用来存放在兴趣爱好,表结构如下:图片 3

CREATE TABLE [dbo].[Workflow_Action](
    [WorkflowSchema] [nvarchar](128) NULL,
    [ActionSchema] [nvarchar](128) NULL,
    [ActionName] [nvarchar](64) NULL
)

INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('material-price','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('material-price','reject','审核驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('material-price','executing','执行价格')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('material-price','non-executing','不执行价格')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-meeting-apply','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-meeting-apply','reject','审核驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-officialSeal-apply','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-officialSeal-apply','reject','审核驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-officialSeal-apply','returned','归还公章')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','commit','提交审核')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','reject','采购驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','order','采购下单')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','recommit','重新提交审核')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','part-consignment','部分收货')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','consignment','完成收货')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase-request','commit','提交审核')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase-request','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase-request','reject','申请驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase-request','recommit','重新提交审核')

       接下来大家来看使用FO卡宴 XML PATH的查询结果语句如下:

数据表和数目

SELECT * FROM @hobby FOR XML PATH

    图片 4

       结果:

 

图片 5

    生龙活虎、简介

<row>
  <hobbyID>1</hobbyID>
  <hName>爬山</hName>
</row>
<row>
  <hobbyID>2</hobbyID>
  <hName>游泳</hName>
</row>
<row>
  <hobbyID>3</hobbyID>
  <hName>美食</hName>
</row>

      接下去,大家用这么些格局查询这一个表的数目。

图片 6

    select * from [dbo].[Workflow_Action] for xml path
      它可以将查询结果转换为一段XML格式的代码

      显而易见FOLX570 XML PATH 能够将查询结果依靠行输出成XML每一项!

    图片 7

      那么,怎样转移XML行节点的称谓呢?代码如下:     

   --for xml path 后面可以写东西,for xml path(Schema),这样写的话,可以将节点<row>变成<Schema>
    select WorkflowSchema as WS,ActionSchema as SC,ActionName as AN from [dbo].[Workflow_Action] for xml path('Schema')

SELECT * FROM @hobby FOR XML PATH(‘MyHobby’)

     
 图片 8

 

    --我们还可以单独输出某一个字段的值
    SELECT '[ '+ActionName+' ]' FROM [dbo].[Workflow_Action] FOR XML PATH('')

      结果一定也同理可得了吗?对的原本的行节点<row>
产生了作者们在PATH前边括号()中,自定义的名称<MyHobby>,结果如下:

    图片 9

图片 10

 

<MyHobby>
  <hobbyID>1</hobbyID>
  <hName>爬山</hName>
</MyHobby>
<MyHobby>
  <hobbyID>2</hobbyID>
  <hName>游泳</hName>
</MyHobby>
<MyHobby>
  <hobbyID>3</hobbyID>
  <hName>美食</hName>
</MyHobby>

    二、实际选用  

图片 11

    --我们看看一个操作对应的而多格流程
    --一共是两层,里面一层查出单独的ActionName,拼成一行,然后使用where条件连接外层
    SELECT WorkflowSchema,
      (SELECT ActionName+',' FROM [dbo].[Workflow_Action] 
      WHERE WorkflowSchema=A.WorkflowSchema    --必须加的条件
      FOR XML PATH('')) AS ActionList
    FROM [dbo].[Workflow_Action] A 
    GROUP BY WorkflowSchema
    

    --where 连接条件必须要,如果去掉,就会查出所有的ActionName,如同上面示例一样
    

    --现在我们优化一下格式,会发现最后多了一个‘,’符号,用LEFT函数去掉他,继续在外面接一层查询
    select B.WorkflowSchema,
        LEFT(B.ActionList,LEN(B.ActionList)-1) as ActionList 
           from (
                 SELECT WorkflowSchema,
                   (SELECT ActionName+',' FROM [dbo].[Workflow_Action] 
                      WHERE WorkflowSchema=A.WorkflowSchema 
                     FOR XML PATH('')) AS ActionList
                FROM [dbo].[Workflow_Action] A 
    GROUP BY WorkflowSchema) as B

     
那个时候留意的对象一定又会问那么列节点怎么着改换吧?还记的给列起别称的基本点字AS吗?对了就是用它!代码如下:

     图片 12

SELECT hobbyID as ‘MyCode’,hName as ‘MyName’ FROM @hobby FOR XML PATH(‘MyHobby’)

 

 

   接下来,大家再讲二个别的的函数,完毕均等的法力,STUFF函数。

      那么那时候大家列的节点名称也会编制程序大家自定义的名号
<MyCode>与<MyName>结果如下:

    sql
stuff函数用于删除钦赐长度的字符,并得以在制定的源点处插入另风度翩翩组字符。sql
stuff函数中只要起首位置或长度值是负数,只怕只要以前地方大于第3个字符串的长度,将重回空字符串。假使要刨除的尺寸超越第多个字符串的长短,将去除到第叁个字符串中的第二个字符。

图片 13

    一、作用

<MyHobby>
  <MyCode>1</MyCode>
  <MyName>爬山</MyName>
</MyHobby>
<MyHobby>
  <MyCode>2</MyCode>
  <MyName>游泳</MyName>
</MyHobby>
<MyHobby>
  <MyCode>3</MyCode>
  <MyName>美食</MyName>
</MyHobby>

      删除钦定长度的字符,并在钦定的起源处插入另生机勃勃组字符。

图片 14

    二、语法

    噢!
既然行的节点与列的节点我们都能够自定义,大家是还是不是足以创设我们心爱的出口格局吧?照旧看代码: 

      STUFF ( character_expression , start , length
,character_expression )

SELECT ‘[ ‘+hName+’ ]’ FROM @hobby FOR XML PATH(”)

        参数

   
没有错大家还是能透过标志+号,来对字符串类型字段的出口格式举办定义。结果如下:

        character_expression  贰个字符数听大人表达式。character_expression
能够是常量、变量,也得以是字符列或二进制数据列。

[ 爬山 ][ 游泳 ][ 美食 ]

        start   二个大背头值,内定删除和插入的上马地方。要是start 或 length 为负,则赶回空字符串。假使 start 比第一个character_expression 长,则赶回空字符串。start 能够是 bigint 类型。

    那么任何品种的列怎么自定义?
不妨,大家将它们转变来字符串类型就行呐!举个例子:

        length  叁个大背头,钦点要刨除的字符数。如若 length
比第二个 character_expression 长,则最多删除到最后四个character_expression 中的最终二个字符。length 能够是 bigint 类型。

SELECT ‘{‘+STR(hobbyID)+’}’,'[ ‘+hName+’ ]’ FROM @hobby FOR XML PATH(”)

        重返类型  

    好的 FOEvoque XML PATH就基本介绍到此处吧,越多关于FOKoleosXML的知识请查阅扶植文书档案!

        如果 character_expression
是受援救的字符数据类型,则赶回字符数据。倘诺 character_expression
是一个受援助的 binary 数据类型,则赶回二进制数据。

    接下去我们来看贰个FOHighlander XML PATH的采用场景呢!那么开头吧。。。。。。

    三、备注

        二.多个运用场景与FO瑞鹰 XML PATH应用

      1、假若开第三地点或长度值是负数,或许风流浪漫旦开头地点大于第四个字符串的长短,将赶回空字符串。尽管要去除的长度超越第一个字符串的尺寸,将去除到第八个字符串中的第三个字符。

        首先呢!大家在扩张一张学子表,列分别为(stuID,sName,hobby),stuID代表学生编号,sName代表学生姓名,hobby列存学子的赏识!那么以往表结构如下:

      2、假如结果值大于再次来到类型协理的最大值,则发出错误。

           图片 15

    四、sql stuff函数

       
此时,大家的必要是询问学子表,彰显全数学员的喜好的结果集,代码如下:

    --实例一
     select STUFF('abcdefg',1,0,'1234')       --结果为'1234abcdefg'
     select STUFF('abcdefg',1,1,'1234')       --结果为'1234bcdefg'
     select STUFF('abcdefg',2,1,'1234')       --结果为'a1234cdefg'
     select STUFF('abcdefg',2,2,'1234')       --结果为'a1234defg'

    --实例二、SQL 将列转成字符串并用逗号分隔
    --同样的,我们也用到了for xml path这个方法
    SELECT STUFF((SELECT ',' + ActionName FROM [dbo].[Workflow_Action] FOR XML PATH('')),1,1,'') AS WA

图片 16

     图片 17

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+’,’ FROM student 
  WHERE sName=A.sName 
  FOR XML PATH(”)) AS StuList
FROM student A 
GROUP BY sName
) B 

    --实例三、最后我们实现,上面for xml path的功能
    --先查出两个字段,然后对ActionName这个字段进行转化,where条件记得加上,不加就会显示出所有的ActionName
    select WorkflowSchema,
           ActionName=(STUFF((select ',' + ActionName 
                              from [dbo].[Workflow_Action] a 
                              where a.WorkflowSchema=b.WorkflowSchema for xml path('')),1,1,''))    --where条件必须加上 
    from [dbo].[Workflow_Action] b group by WorkflowSchema

图片 18

     图片 19

         结果如下:图片 20

 

 分析: 好的,那么大家来分析一下,首先看那句:

    比较以上三种做法,能够自行比较哪个种类方法更为简便易行。

SELECT hobby+’,’ FROM student 
  WHERE sName=A.sName 
  FOR XML PATH(”)

那句是由此FO奥迪Q3 XML PATH 将某生机勃勃姓名如张三的欢悦,彰显成格式为:“
爱好1,爱好2,爱好3,”的格式!

那么随着看:

图片 21

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+’,’ FROM student 
  WHERE sName=A.sName 
  FOR XML PATH(”)) AS StuList
FROM student A 
GROUP BY sName
) B  

图片 22

剩余的代码首先是将表分组,在进行FO昂科拉 XML PATH
格式化,此时当还还未有实行最外层的SELECT时查询出的构造为:

图片 23

能够看见StuList列里面包车型地铁数额都会多出四个逗号,这时候随外层的话语:SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby  正是来去掉逗号,并授予有含义的列明!

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图