【SQL】クエリの結果を階層有XMLで出力する（WITH区）

SQLのクエリの結果をXMLで出力させる際は、「FOR XML AUTO, ROOT(‘Document’), ELEMENTS」を最後につけることでXMLとして結果が表示される。
このような平たいテーブルを使用する。

SELECT *
FROM [ORDER]
FOR XML AUTO, ROOT('Document'), ELEMENTS;

ROOT(‘Document’)は、XMLのルートエレメントを設定（ROOTはOPTIONALでなくても良い）できる。
その下に、テーブル名、列名の要素のXMLが作成される。

<Document>
  <ORDER>
    <ORDERNUM>100</ORDERNUM>
    <SHIPADDRESS>CALIFORNIA</SHIPADDRESS>
    <ITEM>COFFEE</ITEM>
    <QTY>1</QTY>
    <PRICE>100</PRICE>
  </ORDER>
  <ORDER>
    <ORDERNUM>100</ORDERNUM>
    <SHIPADDRESS>CALIFORNIA</SHIPADDRESS>
    <ITEM>CUP</ITEM>
    <QTY>2</QTY>
    <PRICE>70</PRICE>
  </ORDER>
  <ORDER>
    <ORDERNUM>101</ORDERNUM>
    <SHIPADDRESS>ARIZONA</SHIPADDRESS>
    <ITEM>COFFEE</ITEM>
    <QTY>2</QTY>
    <PRICE>100</PRICE>
  </ORDER>
  <ORDER>
    <ORDERNUM>102</ORDERNUM>
    <SHIPADDRESS>OREGON</SHIPADDRESS>
    <ITEM>TEA</ITEM>
    <QTY>3</QTY>
    <PRICE>110</PRICE>
  </ORDER>
</Document>

上記例では、ORDERの中のヘッダデータ（例：ORDERNUM、SHIPADDRESS）、明細データ（ITEM、QTY、PRICE）は並列階層で出力されているが、これに階層分け（ヘッダど明細）をクエリ側で制御することができる。（例えば、下記のXMLのようヘッダ（ORDER_H）と明細(ORDER_D)の階層を作成する。）

<Document>
  <ORDER_H>
    <ORDERNUM>100</ORDERNUM>
    <SHIPADDRESS>CALIFORNIA</SHIPADDRESS>
    <TOTALPRICE>240</TOTALPRICE>
    <ORDER_D>
      <ITEM>COFFEE</ITEM>
      <QTY>1</QTY>
      <PRICE>100</PRICE>
    </ORDER_D>
    <ORDER_D>
      <ITEM>CUP</ITEM>
      <QTY>2</QTY>
      <PRICE>70</PRICE>
    </ORDER_D>
  </ORDER_H>
  <ORDER_H>
    <ORDERNUM>101</ORDERNUM>
    <SHIPADDRESS>ARIZONA</SHIPADDRESS>
    <TOTALPRICE>200</TOTALPRICE>
    <ORDER_D>
      <ITEM>COFFEE</ITEM>
      <QTY>2</QTY>
      <PRICE>100</PRICE>
    </ORDER_D>
  </ORDER_H>
  <ORDER_H>
    <ORDERNUM>102</ORDERNUM>
    <SHIPADDRESS>OREGON</SHIPADDRESS>
    <TOTALPRICE>330</TOTALPRICE>
    <ORDER_D>
      <ITEM>TEA</ITEM>
      <QTY>3</QTY>
      <PRICE>110</PRICE>
    </ORDER_D>
  </ORDER_H>
</Document>

WITH ORDER_H(ORDERNUM, SHIPADDRESS, TOTALPRICE)
AS(
SELECT	ORDERNUM,
	MAX(SHIPADDRESS) AS SHIPADDRESS,
	SUM(PRICE * QTY) AS TOTALPRICE
FROM [ORDER]
GROUP BY ORDERNUM
)
SELECT ORDER_H.ORDERNUM, ORDER_H.SHIPADDRESS, ORDER_H.TOTALPRICE, ORDER_D.ITEM, ORDER_D.QTY, ORDER_D.PRICE
FROM ORDER_H INNER JOIN [ORDER] AS ORDER_D ON ORDER_H.ORDERNUM = ORDER_D.ORDERNUM
FOR XML AUTO, ROOT('Document'), ELEMENTS

WITH区で仮のヘッダテーブルを定義し、そのテーブルと[ORDER]テーブルを結合することで、クエリで階層を持たせることで、XMLにも階層付きで出力を行う。

Share this: