一、sql Server for xml生成xml
for xml是一个行集聚和函数,返回nvarchar(max)的数据类型的单行单列结果集,type指令与for xml一起运用产生xml数据类型而非nvarchar(max)
这里举例的两张表是从Oracle示例数据库拿过来的
1、FOR XML AUTO
SELECT deptno deptNo,dname deptName,loc FROM dept
WHERE 1=1
FOR XML AUTO,ROOT('root');
结果为:
<root>
<dept deptNo="2" deptName="测试部2" loc="测试数据DDDDD" />
<dept deptNo="3" deptName="测试部3" loc="测试数据" />
<dept deptNo="4" deptName="测试部4" loc="Address4" />
<dept deptNo="10" deptName="ACCOUNTING" loc="测试地址" />
<dept deptNo="20" deptName="RESEARCH" loc="DALLAS" />
<dept deptNo="30" deptName="SALES" loc="CHICAGO" />
<dept deptNo="40" deptName="OPERATIONS" loc="BOSTON" />
</root>
如果将后面的ROOT去掉,那么生成的XML就没有根节点
如果关联多个表,有层级关系,XML也会有层级关系,这里以部门表与人员表为例:
SELECT dept.deptno deptNo,dept.dname deptName,dept.loc,emp.ENAME,emp.HIREDATE,emp.JOB
FROM dept
INNER JOIN emp ON emp.DEPTNO=dept.deptno
WHERE 1=1 and dept.deptno>10
ORDER BY dept.deptno
FOR XML AUTO,ROOT('root');
结果为:
<root>
<dept deptNo="20" deptName="RESEARCH" loc="DALLAS">
<emp ENAME="SMITH" HIREDATE="2012-10-12" JOB="CLERK"/>
<emp ENAME="JONES" HIREDATE="2012-10-12" JOB="MANAGER"/>
<emp ENAME="SCOTT" HIREDATE="2012-10-12" JOB="ANALYST"/>
<emp ENAME="ADAMS" HIREDATE="2012-10-12" JOB="CLERK"/>
<emp ENAME="FORD" HIREDATE="2012-10-12" JOB="ANALYST"/>
</dept>
<dept deptNo="30" deptName="SALES" loc="CHICAGO">
<emp ENAME="JAMES" HIREDATE="2012-10-12" JOB="CLERK"/>
<emp ENAME="TURNER" HIREDATE="2012-10-12" JOB="SALESMAN"/>
<emp ENAME="MARTIN" HIREDATE="2012-10-12" JOB="SALESMAN"/>
<emp ENAME="BLAKE" HIREDATE="2012-10-12" JOB="MANAGER"/>
<emp ENAME="ALLEN" HIREDATE="2012-10-12" JOB="SALESMAN"/>
<emp ENAME="WARD" HIREDATE="2012-10-12" JOB="SALESMAN"/>
</dept>
</root>
这个就生成了层级关系,这里的每天数据采用name=value的形式,还可以采用属性的形式:<name>value<name>,这里只需要用elements就可以了
SELECT dept.deptno deptNo,dept.dname deptName,dept.loc,users.ENAME,users.HIREDATE,users.JOB
FROM dept
INNER JOIN emp users ON users.DEPTNO=dept.deptno
WHERE 1=1 AND dept.deptno=20
ORDER BY dept.deptno
FOR XML AUTO,ROOT('root'),ELEMENTS;
结果为:
<root>
<dept>
<deptNo>20</deptNo>
<deptName>RESEARCH</deptName>
<loc>DALLAS</loc>
<users>
<ENAME>SMITH</ENAME>
<HIREDATE>2012-10-12</HIREDATE>
<JOB>CLERK</JOB>
</users>
<users>
<ENAME>JONES</ENAME>
<HIREDATE>2012-10-12</HIREDATE>
<JOB>MANAGER</JOB>
</users>
<users>
<ENAME>SCOTT</ENAME>
<HIREDATE>2012-10-12</HIREDATE>
<JOB>ANALYST</JOB>
</users>
<users>
<ENAME>ADAMS</ENAME>
<HIREDATE>2012-10-12</HIREDATE>
<JOB>CLERK</JOB>
</users>
<users>
<ENAME>FORD</ENAME>
<HIREDATE>2012-10-12</HIREDATE>
<JOB>ANALYST</JOB>
</users>
</dept>
</root>
2、FOR XML RAW
FOR XML RAW与FOR XML AUTO非常相似,唯一区别就是
(1)、前者可以更改元素名称,而后者只能表名、别名来生成
(2)、FOR XML AUTO为每个表都生成一个顶层元素,而FOR XML RAW为查询结果的每一行只生成一个顶层元素
SELECT dept.deptno deptNo,dept.dname deptName,dept.loc,users.ENAME,users.HIREDATE,users.JOB
FROM dept
INNER JOIN emp users ON users.DEPTNO=dept.deptno
WHERE 1=1 AND dept.deptno=20
ORDER BY dept.deptno
FOR XML RAW('order'),ROOT('root')
结果为:
<root>
<order deptNo="20" deptName="RESEARCH" loc="DALLAS" ENAME="SMITH" HIREDATE="2012-10-12" JOB="CLERK"/>
<order deptNo="20" deptName="RESEARCH" loc="DALLAS" ENAME="JONES" HIREDATE="2012-10-12" JOB="MANAGER"/>
<order deptNo="20" deptName="RESEARCH" loc="DALLAS" ENAME="SCOTT" HIREDATE="2012-10-12" JOB="ANALYST"/>
<order deptNo="20" deptName="RESEARCH" loc="DALLAS" ENAME="ADAMS" HIREDATE="2012-10-12" JOB="CLERK"/>
<order deptNo="20" deptName="RESEARCH" loc="DALLAS" ENAME="FORD" HIREDATE="2012-10-12" JOB="ANALYST"/>
</root>
这里也可以把Elements加上,以属性的形式展示
3、FOR XML EXPLICIT
传说是生成XML最强大的语句,但是也比较复杂,在查询的时候每一行应该有两个强制列tag和parent,用以建立层次结构
SELECT 1 AS tag,NULL AS parent, deptno 'dept!1!deptNo',dname 'dept!1!deptName',loc 'dept!1!deptLoc'
FROM dept
WHERE 1=1
FOR XML EXPLICIT,ROOT('root');
结果为:
<root>
<dept deptNo="2" deptName="测试部2" deptLoc="测试数据DDDDD"/>
<dept deptNo="3" deptName="测试部3" deptLoc="测试数据"/>
<dept deptNo="4" deptName="测试部4" deptLoc="Address4"/>
<dept deptNo="10" deptName="ACCOUNTING" deptLoc="测试地址"/>
<dept deptNo="20" deptName="RESEARCH" deptLoc="DALLAS"/>
<dept deptNo="30" deptName="SALES" deptLoc="CHICAGO"/>
<dept deptNo="40" deptName="OPERATIONS" deptLoc="BOSTON"/>
</root>
这里是针对一张表的数据进行处理,所以tag=1,parent=null
下面通过一个例子来处理两张表的层级关系,并且使用elements属性的形式来展示,不过这里是element而非elements
SELECT 1 AS tag,NULL AS parent, deptno 'dept!1!deptNo!ELEMENT' ,dname 'dept!1!deptName!ELEMENT',loc 'dept!1!deptLoc!ELEMENT',NULL 'users!2!userName',NULL 'users!2!hireDate',NULL 'users!2!job'
FROM dept
WHERE 1=1 AND deptno>10
UNION ALL
SELECT 2 AS tag,1 AS parent,NULL,NULL,NULL,users.ENAME,users.HIREDATE,users.JOB
FROM EMP users
WHERE users.DEPTNO=20
FOR XML EXPLICIT,ROOT('root');
结果为:
<root>
<dept>
<deptNo>20</deptNo>
<deptName>RESEARCH</deptName>
<deptLoc>DALLAS</deptLoc>
</dept>
<dept>
<deptNo>30</deptNo>
<deptName>SALES</deptName>
<deptLoc>CHICAGO</deptLoc>
</dept>
<dept>
<deptNo>40</deptNo>
<deptName>OPERATIONS</deptName>
<deptLoc>BOSTON</deptLoc>
<users userName="SMITH" hireDate="2012-10-12" job="CLERK"/>
<users userName="JONES" hireDate="2012-10-12" job="MANAGER"/>
<users userName="SCOTT" hireDate="2012-10-12" job="ANALYST"/>
<users userName="ADAMS" hireDate="2012-10-12" job="CLERK"/>
<users userName="FORD" hireDate="2012-10-12" job="ANALYST"/>
</dept>
</root>
此外还可以用hide指令,用上他,可以对此列隐藏,可以用他排序
SELECT 1 AS tag,NULL AS parent,deptno 'dept!1!deptNo!Hide' ,dname 'dept!1!deptName',loc 'dept!1!deptLoc',NULL 'users!2!userName',NULL 'users!2!hireDate',NULL 'users!2!job'
FROM dept
WHERE 1=1 AND deptno>=10
UNION ALL
SELECT 2 AS tag,1 AS parent,users.DEPTNO,NULL,NULL,users.ENAME,users.HIREDATE,users.JOB
FROM EMP users
WHERE users.DEPTNO>=10
ORDER BY 'dept!1!deptNo!Hide'
FOR XML EXPLICIT,ROOT('root')
结果为:
<root>
<dept deptName="ACCOUNTING" deptLoc="测试地址">
<users userName="CLARK" hireDate="2012-10-12" job="MANAGER"/>
<users userName="KING" hireDate="2012-10-12" job="PRESIDENT"/>
<users userName="MILLER" hireDate="2012-10-12" job="CLERK"/>
<users userName="FORD" hireDate="2012-10-12" job="ANALYST"/>
<users userName="ADAMS" hireDate="2012-10-12" job="CLERK"/>
<users userName="SCOTT" hireDate="2012-10-12" job="ANALYST"/>
</dept>
<dept deptName="RESEARCH" deptLoc="DALLAS">
<users userName="SMITH" hireDate="2012-10-12" job="CLERK"/>
<users userName="JONES" hireDate="2012-10-12" job="MANAGER"/>
<users userName="MARTIN" hireDate="2012-10-12" job="SALESMAN"/>
<users userName="BLAKE" hireDate="2012-10-12" job="MANAGER"/>
<users userName="ALLEN" hireDate="2012-10-12" job="SALESMAN"/>
<users userName="WARD" hireDate="2012-10-12" job="SALESMAN"/>
</dept>
<dept deptName="SALES" deptLoc="CHICAGO">
<users userName="TURNER" hireDate="2012-10-12" job="SALESMAN"/>
<users userName="JAMES" hireDate="2012-10-12" job="CLERK"/>
</dept>
<dept deptName="OPERATIONS" deptLoc="BOSTON"/>
</root>
是用hide来排序可以避免数据层次关系的时候由于顺序问题出错
4、FOR XML PATH
该功能在Sql Server 2005引入,没有FOR XML EXPLICIT复杂,但是功能一样强大
SELECT dept.deptno '@deptNo',dept.dname '@deptName',dept.loc '@deptLoc',
(
SELECT users.ENAME '@userName',users.HIREDATE '@hireDate',users.JOB '@job'
FROM emp users where users.DEPTNO=dept.deptno
FOR XML PATH('user'),ROOT('users'),TYPE
)
FROM dept
WHERE 1=1 AND dept.deptno>10
ORDER BY dept.deptno
FOR XML PATH('dept'),ROOT('root');
结果为:
<root>
<dept deptNo="20" deptName="RESEARCH" deptLoc="DALLAS">
<users>
<user userName="SMITH" hireDate="2012-10-12" job="CLERK" />
<user userName="JONES" hireDate="2012-10-12" job="MANAGER" />
<user userName="SCOTT" hireDate="2012-10-12" job="ANALYST" />
<user userName="ADAMS" hireDate="2012-10-12" job="CLERK" />
<user userName="FORD" hireDate="2012-10-12" job="ANALYST" />
</users>
</dept>
<dept deptNo="30" deptName="SALES" deptLoc="CHICAGO">
<users>
<user userName="ALLEN" hireDate="2012-10-12" job="SALESMAN" />
<user userName="WARD" hireDate="2012-10-12" job="SALESMAN" />
<user userName="MARTIN" hireDate="2012-10-12" job="SALESMAN" />
<user userName="BLAKE" hireDate="2012-10-12" job="MANAGER" />
<user userName="TURNER" hireDate="2012-10-12" job="SALESMAN" />
<user userName="JAMES" hireDate="2012-10-12" job="CLERK" />
</users>
</dept>
<dept deptNo="40" deptName="OPERATIONS" deptLoc="BOSTON" />
</root>
用子查询来处理层级关系,一条父级数据对多多条子数据,当然还可以一条父级对应一条子数据
SELECT dept.deptno '@deptNo',dept.dname '@deptName',dept.loc '@loc',users.ENAME 'users/@userName',users.HIREDATE 'users/@hiredate',users.JOB 'users/@job'
FROM dept
INNER JOIN emp users ON users.DEPTNO=dept.deptno
WHERE 1=1 AND dept.deptno>10
ORDER BY dept.deptno
FOR XML PATH('order'),ROOT('root');
结果为:
<root>
<order deptNo="20" deptName="RESEARCH" loc="DALLAS">
<users userName="SMITH" hiredate="2012-10-12" job="CLERK" />
</order>
<order deptNo="20" deptName="RESEARCH" loc="DALLAS">
<users userName="JONES" hiredate="2012-10-12" job="MANAGER" />
</order>
<order deptNo="20" deptName="RESEARCH" loc="DALLAS">
<users userName="SCOTT" hiredate="2012-10-12" job="ANALYST" />
</order>
<order deptNo="20" deptName="RESEARCH" loc="DALLAS">
<users userName="ADAMS" hiredate="2012-10-12" job="CLERK" />
</order>
<order deptNo="20" deptName="RESEARCH" loc="DALLAS">
<users userName="FORD" hiredate="2012-10-12" job="ANALYST" />
</order>
<order deptNo="30" deptName="SALES" loc="CHICAGO">
<users userName="JAMES" hiredate="2012-10-12" job="CLERK" />
</order>
<order deptNo="30" deptName="SALES" loc="CHICAGO">
<users userName="TURNER" hiredate="2012-10-12" job="SALESMAN" />
</order>
<order deptNo="30" deptName="SALES" loc="CHICAGO">
<users userName="MARTIN" hiredate="2012-10-12" job="SALESMAN" />
</order>
<order deptNo="30" deptName="SALES" loc="CHICAGO">
<users userName="BLAKE" hiredate="2012-10-12" job="MANAGER" />
</order>
<order deptNo="30" deptName="SALES" loc="CHICAGO">
<users userName="ALLEN" hiredate="2012-10-12" job="SALESMAN" />
</order>
<order deptNo="30" deptName="SALES" loc="CHICAGO">
<users userName="WARD" hiredate="2012-10-12" job="SALESMAN" />
</order>
</root>