データベースXMLデータ照会

16396 ワード
JOIN sql xml データベース server テスト null
一、sql Server for xml生成xml
for xmlは行集積と関数であり、nvarchar(max)のデータ型の単行単列結果セットを返し、type命令はfor xmlとともに運用してnvarchar(max)ではなくxmlデータ型を生成する
ここで例を挙げた2つのテーブルは、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の形式を採用し,valueの属性の形式を採用することもできるが,ここでは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を生成する最も強力な文だと言われていますが、複雑で、クエリーするときに各行に2つの強制列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>

ここでは1枚のテーブルのデータに対して処理するのでtag=1,parent=null
2つのテーブルの階層関係を一例で処理し、elementsプロパティの形式で示すが、ここではelementsではなくelementである
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>

サブクエリを使用して階層関係を処理します.1つの親データは複数の子データに対して、もちろん1つの親が1つの子データに対応することもできます.
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>

 
 
 
 
 
【MSsqlserver】ストアド・プロシージャ学習開始
NS 3ネットワークシミュレーション(3):NetAnim