数据库XML数据查询

一、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>


 

 

 

 

 



 

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值