SQL データを XML として公開する操作
SQL 式の中で XMLElement() 関数、XMLConcat() 関数、XMLAgg() 関数、および XMLAttributes() 関数を使用して、データベース照会の結果を XML に変換できます。
これらの XML 関数は、リレーショナル・データベースに保管されたデータを、他のアプリケーション (例えば、Web サービス) で利用可能な XML に変換することが目的であるため、しばしば公開関数 と呼ばれます。 この点において主に使用される関数は XMLElement() です。この関数は、作成する XML 要素の名前とその要素の内容の 2 つを引数としてとります。 次の select ステートメント (このステートメントは実際にはデータベースを照会しません) で、XMLElement() 関数の使用方法を説明します。
select XMLElement('Parent', 'Parent Text');
このステートメントは、次の XML を作成します。
<Parent>Parent Text</Parent>
XMLElement()関数の出力は XML 型の値で、これはNetezza Performance Serverでコンパイルされた XML 要素の表現です。 したがって、上記の select ステートメントを入力した場合、name 型の XML が返されます。
XMLELEMENT
-----------
XML
(1 row)
XMLElement() 呼び出しで作成される実際の XML 要素を表示するには、XMLElement() 呼び出しを XMLSerialize() 関数でラップする必要があります。 以下に例を示します。
select XMLSerialize(XMLElement('Parent', 'Parent Text'));
XMLSERIALIZE
------------------------------
<Parent>Parent Text</Parent>
(1 row)
XMLElement() 関数の真の価値は、関数呼び出しをネストして XML データに必要な階層構造を生成できる点です。 以下に例を示します。
select
XMLElement('Parent',
XMLElement('Child', 'Child text'));
この照会により、次の XML が生成されます。
<Parent>
<Child>Child text</Child>
</Parent>
公開関数は、必要に応じてネストすることができます。ネスト呼び出しの上限は 10,000 です。 以下に例を示します。
select
XMLElement('Parent',
XMLElement('Child',
XMLElement('GrandChild', 'Grandchild text')));
この照会により、次の XML が生成されます。
<Parent>
<Child>
<GrandChild>Grandchild text</GrandChild>
</Child>
</Parent>
より現実的な例として、DEPTNO、DEPTNAME、DEPTLOC という 3 つの列を持つ DEPARTMENTS 表があるとします。
DEPTNO DEPTNAME DEPTLOC
------ ---------- ---------
10 MARKETING BOSTON
20 HR BOSTON
30 SALES NEW YORK
40 ENGINEERING NEW YORK
以下の簡潔な SQL 照会を実行すると、すべての部門がリストされます。
select * from departments;
ただし、部門データの 4 行すべてを XML として返す必要があるとします。以下の XML 文書に示すように、部門ごとに 1 つの <Dept> ノードがあり、各 <Dept> ノードには、3 つの子ノード (<Number>、<Name>、および <Location>) があります。
<Departments>
<Dept>
<Number>10</Number>
<Name>MARKETING</Name>
<Location>BOSTON</Location>
</Dept>
<Dept>
<Number>20</Number>
<Name>HR</Name>
<Location>BOSTON</Location>
</Dept>
<Dept>
<Number>30</Number>
<Name>SALES</Name>
<Location>NEW YORK</Location>
</Dept>
<Dept>
<Number>40</Number>
<Name>ENGINEERING</Name>
<Location>NEW YORK</Location>
</Dept>
</Departments>
この XML 文書を作成するには、次のような select ステートメントを使用してモデル作成します。
select
XMLElement('Departments', XMLAGG(
XMLElement('Dept', XMLConcat(
XMLElement('Number', d.deptno),
XMLElement('Name', d.deptname),
XMLElement('Location', d.deptloc)))))
from departments d;
最初の 2 つの XMLElement() 呼び出しのそれぞれにおいて、要素の内容がネストした XML 関数の呼び出しによって作成されます。 親ノードと子ノードの階層的な構造化 XML 文書を作成するには、XMLElement() 呼び出しを SQL ステートメントの中でネストします。
クエリの最初の'XMLElement()関数は、トップレベルの<Departments>ノードを作成します：
XMLElement('Departments', XMLAgg (
2番目の引数には'XMLAgg()アグリゲートが使用され、トップレベルの<Departments>ノードのコンテンツがアグリゲートされたノード・グループであることを示しています。
2番目のXMLElement()呼び出しは、<Departments>親ノードの各子ノードの名前として<Dept>を確立し、次に埋め込まれた3つのXMLElement()呼び出しに各<Dept>子ノードの内容を依存する：
XMLElement('Dept', XMLConcat(
XMLElement('Number', d.deptno),
XMLElement('Name', d.deptname),
XMLElement('Location', d.deptloc)))))
これら3つの埋め込み'XMLElement()呼び出しは、DEPARTMENTSテーブルから返されるデータ行を包むのに必要な数の<Dept>子ノードを作成する。
重要なことは、XMLAgg() 集約関数の使用方法を理解することです。 この集約は、子ノードをそれらの親ノードの下に結合します。すなわち、上記の例では、1 つの親ノード <Departments> があり、そこに 4 つの <Dept> ノードすべてが入ります。 XMLAgg()集約呼び出しがないと、生成されるXMLには4つの<Departments>ノードが含まれ、各ノードには1つの<Dept>ノードが含まれる：
<Departments>
<Dept>
<Number>10</Number>
<Name>MARKETING</Name>
<Location>BOSTON</Location>
</Dept>
</Departments>
<Departments>
<Dept>
<Number>20</Number>
<Name>HR</Name>
<Location>BOSTON</Location>
</Dept>
</Departments>
<Departments>
<Dept>
<Number>30</Number>
<Name>SALES</Name>
<Location>NEW YORK</Location>
</Dept>
</Departments>
<Departments>
<Dept>
<Number>40</Number>
<Name>ENGINEERING</Name>
<Location>NEW YORK</Location>
</Dept>
</Departments>
これは、<Departments> 文書要素のインスタンスが 4 つあるため、有効な XML 構文ではありません。 この例により、関数ライブラリーで作成する XML が XML として解析できることを IsValidXML() 関数を使用して確認することがいかに重要であるかが分かります。 また、スキーマを使用している場合には、整形式の XML (スキーマで指定された構造に従った XML) を返す必要もあります。
別の例として、以下のようにタグ付けをして、部門 (Department) ごとの従業員 (Employee) の一覧を返すとします。
<EmployeesByDepartment>
<Dept DeptNo=“10“>
<Name>ACCOUNTING</Name>
<Location>NEW YORK</Location>
<Employees>
<Employee EmpNo=“7782“>
<Name>CLARK</Name>
<Job>MANAGER</Job>
<Manager>7839</Manager>
<Salary>2450</Salary>
</Employee>
<Employee EmpNo=“7839“>
<Name>KING</Name>
<Job>PRESIDENT</Job>
<Salary>5000</Salary>
</Employee>
...
</Employees>
</Dept>
...
<EmployeesByDepartment>
部門別に従業員を返すには、2 つの select ステートメントが必要です。 まず従業員のグループを作成した後、部門別に従業員をグループ化します。
CREATE temp table emp_grouping AS
SELECT deptno, XMLElement ('Employees', XMLAGG (
XMLElement ('Employee', XMLAttributes ('EmpNo', empno),
XMLConcat (
xmlelement ('name', name),
xmlelement ('job', job),
xmlelement ('manager', mgr),
xmlelement ('salary', sal),
xmlelement ('comm', comm)))))
AS xml FROM emp INNER JOIN dept
ON emp.deptno = dept.deptno
GROUP BY deptno;
SELECT XMLElement('EmployeesByDepartment', XMLAGG(
XMLElement('Dept', XMLAttributes('DeptNo', deptno), XMLConcat(
XMLElement('Name', D.DNAME),
XMLElement('Location', D.LOC),
emp_grouping.xml))))
FROM dept INNER JOIN emp_grouping
ON dept.deptno = emp_grouping.deptno;