树形数据查询出树形结构,JSON或XML

表结构

create table BA_Menu(
	ID int identity(1,1) primary key,
	Name nvarchar(200) null,
	Url nvarchar(500) null,
	Parent int not null default(0),
	ExGuid varchar(100) null default(newid()),
	Remark nvarchar(500) null
)

无极树,填充数据

select * from ba_menu
insert into ba_menu
select '1','http://',0,null,null union all
select '11','http://',1,null,null union all
select '12','http://',2,null,null union all
select '13','http://',3,null,null union all
select '14','http://',4,null,null union all
select '15','http://',5,null,null

查询出树形结构

with menu as (
	select *,id px,cast(id as nvarchar(max)) Menu from ba_menu where Parent = 0
	union all
	select b.*,t.px,t.menu + '-' + LTRIM(b.ID) from ba_menu b,menu t where b.parent = t.id 
)
select ID,Name,Url,Parent,ExGuid,Remark,Menu from menu

查询完的结果即是JSON格式或XML格式,总之想一步完成,不想再在代码里写了,
如果根本无法实现请告知原因

10 个解决方案

#1

帮顶，学习XML

#2

等高手来讲解2005的XML的精妙用法

#3

/*
SQLServer2005 XML在T－SQL查询中的典型应用

整理:fcuandy
时间:2008.11.7

前言:
    此文只讲xml数据类型及相应的一些操作方法在解决日常T－SQL编程中的一些应用，而避开xml modify,
xml schema,xml索引,命名空间等这些语法性或者生硬的一些问题(这些语法您可以查联机丛书),即此文主要
讲以xml的一些操作特性及xquery去解决编程问题.

Tags:
    xquery ,FLWOR迭带 ,sql:column ,sql:variable ,nodes ,value ,query ,xpath ,xquery function, if, 聚合函数, xs:function等

典型应用举例:
*/


--(1)
--====================================================================
--拆分
DECLARE @s VARCHAR(100)
SET @s='a,b,c,dd,ee,f,aa,a,aa,f'

--常规做法(sql2000常用),以一split函数拆分串为表类型结构,如
--SELECT * FROM dbo.split(@s,',') a
--当然，也可能是循环去拆分,或者以一输助表的identity列利用charindex等函数拿identity列值与','的位置匹配实现拆分
--这些做法，roy_88及本人以前都整理过，不再累赘，可见推荐贴。即便 是xml法，也贴过多次，下面一笔带过

--XML做法:
SELECT b.v FROM
    (SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a   --将字串","换换为"</r><r>"并前后拼上<r>,</r>以用来构造xml串
CROSS APPLY
    (SELECT v=t.x.value('.','VARCHAR(10)') FROM a.x.nodes('//r') AS t(x) ) b  --使用 xml.nodes函数将xml串拆分为行
/*
a
b
c
dd
ee
f
aa
a
aa
f
*/


--(2)
--====================================================================
--去重,@s中出现的元素，重复的只要一个,希望结果为 'a,b,c,dd,ee,f'
--常规做法，循环或函数,或临时表拆后distinct
--XML做法:
--a.在(1)的基础上进行

;WITH fc AS   --定义cte命名,将@s转换为一个表结构
(
    SELECT DISTINCT b.v v
            FROM
                (SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a
            CROSS APPLY
                (SELECT v=t.x.value('.','VARCHAR(10)') FROM a.x.nodes('//r') AS t(x) ) b
)
--对这个表利用xml方法进行行值拼接
SELECT STUFF(b.v.value('/r[1]','varchar(100)'),1,1,'')
    FROM
    (SELECT v=(SELECT ',' + v FROM fc FOR XML PATH(''),ROOT('r'),TYPE)) b
/*
a,aa,b,c,dd,ee,f
*/

--b FLWOR语句 + T-SQL组合:
SELECT STUFF(v,1,1,'') FROM
    (SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a
CROSS APPLY
    (SELECT x=(SELECT t.x.value('.','varchar(10)') v,idx=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM a.x.nodes('//r') AS t(x) FOR XML PATH('r'),TYPE)) b --利用row_number得到唯一idx
CROSS APPLY
    (SELECT v=CAST(b.x.query('for $r in //r where count(//r[v=$r/v and idx<$r/idx])=0 return concat(",",xs:string($r/v[1]))') AS VARCHAR(MAX))) c  --类似count计数法，取得v相同的节点集idx值最小的节点,原型为:
--SELECT * FROM tb a WHERE 1>(SELECT COUNT(*) FROM tb WHERE v=a.v AND id<a.id)
/*
a ,b ,c ,dd ,ee ,aa ,f
*/


--c distinct-values
SELECT REPLACE(v,' ',',') FROM
    (SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a
CROSS APPLY
    (SELECT CAST(a.x.query('distinct-values(//r)') AS VARCHAR(MAX)) v) b  --直接调用distinct-values函数来操作
/*
a,b,c,dd,ee,f,aa
*/


-- 导入去重, last() , position()

DECLARE   @doc  xml
SET   @doc   ='<?xml version="1.0" encoding="gb2312" ?>
<employees>
    <employee>
        <empid>e0001</empid>
        <name>萧峰</name>
    </employee>
    <employee>
        <empid>e0002</empid>
        <name>段誉</name>
    </employee>
    <employee>
        <empid>e0003</empid>
        <name>王语嫣</name>
    </employee>
    <employee>
        <empid>e0003</empid>
        <name>张无忌</name>
    </employee>
</employees>
'
create table people2 
( 
    personid varchar(10)  primary key ,
    name varchar(20) 
)

INSERT people2
SELECT DISTINCT b.* FROM
    (SELECT x = @doc.query('for $e in //employee  return  //employee[empid = $e/empid][last()]')) a  --FLWOR时,用当前节点去//emploee节点集中找节点集中empid等于当前节点的empid, 在找到的集合中取最后一个利用last()函数
CROSS APPLY
    (SELECT id=t.x.value('empid[1]','varchar(100)'),name=t.x.value('name[1]','varchar(100)') FROM a.x.nodes('//employee') AS t(x)) b

SELECT * FROM people2
/*
e0001    萧峰
e0002    段誉
e0003    张无忌
*/
GO
drop table people2 
GO
--同组一选多，也可应用此方法，不过没有必要，就不再累赘了。


--(3)
--====================================================================
--列名,列值相关
--a,按行聚合
declare @t table(Sname nvarchar(5),  V1 float,    V2 float,    V3 float,      V4 float,    V5 float,      V6 float) 
insert @t select N'张三',    0.11 , 0.21 , 0.29,  0.32 ,   0.11,    0.08 
insert @t select N'李四',    0.01 , 0.61 , 0.21,  0.73 ,   0.21,    0.12 
insert @t select N'张五',    0.31 , 0.21 , 0.23,  0.33 ,   0.91,    0.65 
insert @t select N'张六',    0.59 , 0.11,  0.26,  0.13,    0.01,    0.15 

select b.* from
    (select x=cast((select * from @t for xml path('r')) as xml)) a
cross apply
    (
        select name=x.query('./Sname/text()'),v=x.query('max(./*[local-name(.)!="Sname"])') from a.x.nodes('//r') as t(x)  
        --r为二级节点(因为文档本身无根节点，即为每项的*节点)即为一个r节点表示一条记录. r下级节点，每个表示一个列，因为列名未知，所以用/*匹配所有节点，因为name为区别列，不参与聚合运算，故用local-name取得来过滤
    ) b

/*
张三    0.32
李四    0.73
张五    0.91
张六    0.59
*/

--b ,由值引到取列
if not object_id('T1') is null
    drop table T1
GO
Create table T1([tId] int,[tName] nvarchar(4))
Insert T1
select 1,N'zhao' union all
select 2,N'qian' union all
select 3,N'sun'
Go
--> --> 借且(Roy)生成測試數據

if not object_id('T2') is null
    drop table T2
Go
Create table T2([tId] int,[zhao] nvarchar(1),[qian] nvarchar(1),[sun] nvarchar(1))
Insert T2
select 1,N'a',N'b',N'c' union all
select 2,N'd',N'e',N'f' union all
select 3,N'g',N'h',N'i'
Go


SELECT c.tid,c.tName,v FROM t1 c
CROSS APPLY
    (SELECT x=(SELECT * FROM t2 WHERE tid=c.tid FOR XML PATH('r'),TYPE)) a
CROSS APPLY
    (SELECT v=t.x.query('./*[local-name(.)=xs:string(sql:column("c.tName")) ]/text()') 
        FROM a.x.nodes('//r') AS t(x)
    ) b

/*
1    zhao    a
2    qian    e
3    sun    i
*/


--c, 列名，列值，与系统表 

CREATE TABLE tb(f1 INT,f2 INT,x INT,z INT,d INT,ex INT,dd INT,vv INT) 
INSERT tb SELECT 1,2,3,5,11,3,2423,33 
GO 
SELECT * FROM tb 
GO 
SELECT name,v FROM
  ( SELECT name FROM sys.columns WHERE object_id=object_id('tb','u') ) a 
CROSS JOIN
  (SELECT x=(SELECT * FROM tb FOR XML PATH('r'),TYPE)) b 
CROSS APPLY
(SELECT v=t.x.query('./*[local-name(.)=xs:string(sql:column("a.name")) ]/text()') FROM b.x.nodes('//r') AS t(x) ) c 
/*
f1    1
f2    2
x    3
z    5
d    11
ex    3
dd    2423
vv    33
*/
GO 
DROP TABLE tb
GO

--(4)
--一些综合计算
--以下表 ta.a值 yyyymmdd-yyyymmdd表连续时间段，","表单个日期
If object_id('ta','u') is not null 
    Drop table ta
Go
Create table ta(a varchar(100))
Go
Insert into ta
select '1 ¦ ¦20080101-20080911' 
union all
select '2 ¦ ¦20080101，20080201，20080301，20080515，20080808'
union all
select '3 ¦ ¦20080101，20080201，20080301，20080515，20081108'
Go

declare @s varchar(8)
select @s= convert(varchar(8),getdate(),112)

select stuff(replace(replace(cast(x as varchar(1000)),'</item><item>',case when type='1' then '-' else '，' end),'</item>',''),1,6,type + ' ¦ ¦') a
    from
    (
        select left(a,1) type, 
            cast(
                    '<item>' 
                    + 
                    replace(
                        stuff(a,1,5,''),
                        case when left(a,1)=1 then '-' else '，' end,
                        '</item><item>'
                        )
                    + 
                    '</item>'
                AS XML
                ) x
        from ta
    ) base

    where x.value('
            if (sql:column("base.type")="1") then
                if(
                    (/item/text())[1]<sql:variable("@s")
                    and
                    (/item/text())[2]>sql:variable("@s")
                )
                then 1
                else 0
            else
                count(//item[text()>sql:variable("@s")])
            '
            ,
            'int'
            )>0
go

#4

#5

CREATE OR REPLACE FUNCTION TEST.get_query_xml_withrowtag ( 
   q_string   IN   VARCHAR2, 
   p_rowtag   IN   VARCHAR2 DEFAULT NULL 
) 
   RETURN CLOB 
IS 
   ctx       NUMBER; 
   xmldoc    CLOB; 
   page      NUMBER        := 0; 
   xmlpage   BOOLEAN       := TRUE; 
   refcur    sys_refcursor; 
BEGIN 
   OPEN refcur FOR q_string; 

   ctx := DBMS_XMLGEN.newcontext (refcur); 
   DBMS_LOB.createtemporary (xmldoc, TRUE); 

   IF NOT (p_rowtag IS NULL) 
   THEN 
      DBMS_XMLGEN.setrowtag (ctx, p_rowtag); 
   END IF; 

   xmldoc := DBMS_XMLGEN.getxml (ctx, DBMS_XMLGEN.NONE); 
   DBMS_XMLGEN.closecontext (ctx); 
   RETURN xmldoc; 
END; 
/ 
select get_query_xml('select * from my_tables');
CREATE OR REPLACE PROCEDURE TEST.put_query_xml ( 
   q_string   IN       VARCHAR2, 
   RESULT     OUT      CLOB 
) 
IS 
   qryctx   DBMS_XMLGEN.ctxhandle; 
BEGIN 
   qryctx := DBMS_XMLGEN.newcontext (q_string); 
   DBMS_XMLGEN.setrowtag (qryctx, 'report'); 
   RESULT := DBMS_XMLGEN.getxml (qryctx); 
   DBMS_XMLGEN.closecontext (qryctx); 
END; 
/ 

DECLARE
   q_string1   VARCHAR2 (2000);
   RESULT      CLOB;
BEGIN
   q_string1 := 'select * from my_tables';
   put_query_xml (q_string1, RESULT); 
   INSERT INTO temp_clob_tab
        VALUES (RESULT); 
   COMMIT;
END; 

SELECT *
  FROM temp_clob_tab;

#6

你要的结果是设么样子？

#7

六个回复,我怎么一个也看不见?

#8

类似下边的结构的结果

<root>
	<node1>
		<node2>
			<node3>
				<node4>
					<node5>
						...无极分类
					</node5>
				</node4>
			</node3>
		</node2>
	</node1>
	<node6>
		<node7>
		</node7>
	</node6>
	<node8 />
</root>

#9

　　FOR XML子句有四种最基本的模式，如上图所示：

　　1、AUTO模式：返回数据表为起表名的元素，每一列的值返回为属性；

　　2、RAW模式：返回数据行为元素，每一列的值作为元素的属性；

　　3、PATH模式：通过简单的XPath语法来允许用户自定义嵌套的XML结构、元素、属性值

　　4、EXPLICIT模式：通过SELECT语法定义输出XML的结构

　　具体实例如下：

　　1、AUTO模式

　　(1). SQL语句：

　　1: SELECT EmployeeID,FirstName,LastName FROM Employees FORXMLAUTO, XMLSCHEMA

　　(2). 所生成的XML文件：

　　返回XML文件的XML Schema

　　<xsd:schematargetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1"xmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"elementFormDefault="qualified"><xsd:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/><xsd:elementname="Employees"><xsd:complexType><xsd:attributename="EmployeeID"type="sqltypes:int" use="required"/><xsd:attributename="FirstName"use="required"><xsd:simpleType><xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"><xsd:maxLengthvalue="10"/>xsd:restriction>xsd:simpleType>xsd:attribute><xsd:attributename="LastName"use="required"><xsd:simpleType><xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52"><xsd:maxLengthvalue="20"/>xsd:restriction>xsd:simpleType>xsd:attribute>xsd:complexType>xsd:element>xsd:schema><Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy" LastName="Davolio"/><Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew" LastName="Fuller"/><Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet" LastName="Leverling"/><Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret" LastName="Peacock"/><Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven" LastName="Buchanan"/><Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael" LastName="Suyama"/><Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert" LastName="King"/><Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura" LastName="Callahan"/><Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne" LastName="Dodsworth" />

　　2、RAW模式

　　1: 将元素命名为自定义的名称Employee2: SELECTEmployeeID,FirstName,LastNameFROM Employees FOR XML RAW("Employee") (2). 所生成的XML文件： 1:<Employee EmployeeID="1"FirstName="Nancy" LastName="Davolio"/> 2: <EmployeeEmployeeID="2" FirstName="Andrew"LastName="Fuller" /> 3:<Employee EmployeeID="3"FirstName="Janet" LastName="Leverling"/> 4: <EmployeeEmployeeID="4" FirstName="Margaret"LastName="Peacock" /> 5:<Employee EmployeeID="5"FirstName="Steven" LastName="Buchanan"/> 6: <EmployeeEmployeeID="6" FirstName="Michael"LastName="Suyama" /> 7:<Employee EmployeeID="7"FirstName="Robert" LastName="King" />8: <EmployeeEmployeeID="8" FirstName="Laura" LastName="Callahan"/> 9:<Employee EmployeeID="9" FirstName="Anne"LastName="Dodsworth"/>

　　3、PATH模式：

　　(1). SQL语句：

　　SELECT EmployeeID "@ID",FirstName"Name/FirstName",LastName"Name/LastName"

　　FROM Employees FOR XML PATH ("Employee")

　　(2). 所生成的XML文件

　　<EmployeeID="1"><Name><FirstName>NancyFirstName><LastName>DavolioLastName>Name>Employee><EmployeeID="2"><Name><FirstName>AndrewFirstName><LastName>FullerLastName>Name>Employee><EmployeeID="3"><Name><FirstName>JanetFirstName><LastName>LeverlingLastName>Name>Employee><EmployeeID="4"><Name><FirstName>MargaretFirstName><LastName>PeacockLastName>Name>Employee><EmployeeID="5"><Name><FirstName>StevenFirstName><LastName>BuchananLastName>Name>Employee><EmployeeID="6"><Name><FirstName>MichaelFirstName><LastName>SuyamaLastName>Name>Employee><EmployeeID="7"><Name><FirstName>RobertFirstName><LastName>KingLastName>Name>Employee><EmployeeID="8"><Name><FirstName>LauraFirstName><LastName>CallahanLastName>Name>Employee>

　　4、EXPLICIT模式

　　问题：加入要生成如下的XML文档该如何操作？

　　Nancy Davolio EXPLICIT模式解决这个问题的应用分为两个主要步骤

　　1.定义要输出的XML文档结构；

　　2.传入实际的数据值；

　　(1). SQL语句：

　　--定义输出XML文档的数据结构SELECT 1 AS Tag, NULL AS Parent, EmployeeIDAS[Employee!1!EmpID], FirstName AS[Employee!1!FirstName!element],LastName AS[Employee!1!LastName!element]FROM Employees UNIONALL--传入实际的数据SELECT1, NULL, EmployeeID, FirstName, LastNameFROMEmployeesORDERBY[Employee!1!EmpID],[Employee!1!FirstName!element],[Employee!1!LastName!element]FORXML EXPLICIT

　　语句含义的解释：

　　先看看定义XML结构的语句输出结果：

　　Tag栏用来指定生成元素的嵌套水平；1表示嵌套水平为

　　Parent栏用来指定当前Tag的父级层次；Null值表示该元素为*元素；

　　EmployeeID AS [Employee!1!EmpID],

　　说明：当前元素或属性的父级元素的名称！元素的标签号！元素或属性的名称

　　FirstName AS [Employee!1!FirstName!element],

　　说明：当前元素或属性的父级元素的名称！元素的标签号！元素或属性的名称！指定值作为元素输出

　　5、为输出的XML文档添加根元素(Root element)

　　(1). SQL语句：

　　SELECT EmployeeID,FirstName,LastName FROM Employees FOR XMLAUTO,ROOT("MyRoot")

　　(2). 所生成的XML文件

　　<MyRoot><Employees EmployeeID="1"FirstName="Nancy"LastName="Davolio" /><EmployeesEmployeeID="2"FirstName="Andrew" LastName="Fuller"/><EmployeesEmployeeID="3" FirstName="Janet"LastName="Leverling"/><Employees EmployeeID="4"FirstName="Margaret"LastName="Peacock" /><EmployeesEmployeeID="5"FirstName="Steven" LastName="Buchanan"/><EmployeesEmployeeID="6" FirstName="Michael"LastName="Suyama"/><Employees EmployeeID="7"FirstName="Robert"LastName="King" /><Employees EmployeeID="8"FirstName="Laura"LastName="Callahan" /><EmployeesEmployeeID="9"FirstName="Anne" LastName="Dodsworth"/>MyRoot>

#10

学习吧。。。