数据库XML数据查询

最新推荐文章于 2024-07-08 07:28:44 发布

ceclar123

最新推荐文章于 2024-07-08 07:28:44 发布

阅读量3.4k

点赞数

分类专栏： SQLServer 文章标签： xml 数据库测试 null sql server join

本文链接：https://blog.csdn.net/ceclar123/article/details/8000768

版权

SQLServer 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

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