FOR XML & OPENXML

今回は、 XML文書を行セットへと変換するOPENXMLと行セットをXML文書にするFOR XML について書こうと思います。
今回もScoreXMLテーブルを使うので、以下にデータを示します。

ScoreXMLテーブル
20070001	<Score TestId="20071001">89</Score><Score TestId="20071002">87</Score><Score TestId="20072001">95</Score><Score TestId="20072002">100</Score>
20070002	<Score TestId="20071001">90</Score><Score TestId="20071002">88</Score><Score TestId="20072001">96</Score><Score TestId="20072002">100</Score>

またもう1つ使うテーブルがあるのでそちらも記載しておきます。

Testsテーブル
20071001	前期中間　現代文
20071002	前期中間　古典
20072001	前期期末　古典
20072002	前期期末　数学

まずはFOR XMLからです。

FOR XML句には4つのモードがあります。

RAWモード
クエリ結果の各行セットがXML要素に変換されるモードで、NULL以外の各列が属性として設定される。
各列の値を属性ではなく、要素としてXMLを構築したい場合は、ELEMENTを指定する。
行セットの要素は指定がなければ<row>で要素が生成される。要素名を指定する場合は()で要素名を記述する。
行セットのルート要素を指定したい場合は、ROOTを使用して指定する。
NULLの値に対しても、要素を作成するように指定する場合は、XSINILを指定する。
XSINILはELEMENTと共に指定する。
AUTOモード
入れ子構造のXML要素としてクエリ結果が返されるモードで、一番簡単ではあるがXML構造の制御があまりできないので、
単純な階層構造のXML文書を作成したい時に使用するモード。
EXPLICITモード
クエリ結果から生成されるXMLの構造を柔軟に制御することができるモード。
行セットにはTag列とParent列が必要となる。
列名がXMLの階層、要素または属性を示している。
PATHモード
EXPLICITモードよりも簡潔にXML構造を制御することができるモード。
列名がXML構造のXPATH式になっている。

詳しくはFOR XMLを見てもらうとして、まずは、一番簡単なAUTOモードで実行してみます。


select * from ScoreXML FOR XML AUTO;

実行結果

<ScoreXML StudentId="20070001">
    <Score>
        <Score TestId="20071001">89</Score>
        <Score TestId="20071002">87</Score>
        <Score TestId="20072001">95</Score>
        <Score TestId="20072002">100</Score>
    </Score>
</ScoreXML>
<ScoreXML StudentId="20070002">
    <Score>
        <Score TestId="20071001">90</Score>
        <Score TestId="20071002">88</Score>
        <Score TestId="20072001">96</Score>
        <Score TestId="20072002">100</Score>
    </Score>
</ScoreXML>

次にRAWモードで実行してみます。


select * from ScoreXML FOR XML RAW;

実行結果

<row StudentId="20070001">
    <Score>
        <Score TestId="20071001">89</Score>
        <Score TestId="20071002">87</Score>
        <Score TestId="20072001">95</Score>
        <Score TestId="20072002">100</Score>
    </Score>
</row>
<row StudentId="20070002">
    <Score>
        <Score TestId="20071001">90</Score>
        <Score TestId="20071002">88</Score>
        <Score TestId="20072001">96</Score>
        <Score TestId="20072002">100</Score>
    </Score>
</row>

次にRAWモード要素名指定で実行してみます。


select * from ScoreXML FOR XML RAW('REC');

実行結果

<REC StudentId="20070001">
    <Score>
        <Score TestId="20071001">89</Score>
        <Score TestId="20071002">87</Score>
        <Score TestId="20072001">95</Score>
        <Score TestId="20072002">100</Score>
    </Score>
</REC>
<REC StudentId="20070002">
    <Score>
        <Score TestId="20071001">90</Score>
        <Score TestId="20071002">88</Score>
        <Score TestId="20072001">96</Score>
        <Score TestId="20072002">100</Score>
    </Score>
</REC>

次にRAWモード要素名指定でかつELEMENTS指定をして実行してみます。


select * from ScoreXML FOR XML RAW('REC'),ELEMENTS;

実行結果

<REC>
    <StudentId>20070001</StudentId>
    <Score>
        <Score TestId="20071001">89</Score>
        <Score TestId="20071002">87</Score>
        <Score TestId="20072001">95</Score>
        <Score TestId="20072002">100</Score>
    </Score>
</REC>
<REC>
    <StudentId>20070002</StudentId>
    <Score>
        <Score TestId="20071001">90</Score>
        <Score TestId="20071002">88</Score>
        <Score TestId="20072001">96</Score>
        <Score TestId="20072002">100</Score>
    </Score>
</REC>

お次はPATHモードで実行してみます。


select * from ScoreXML FOR XML PATH;

実行結果

<row>
    <StudentId>20070001</StudentId>
    <Score>
        <Score TestId="20071001">89</Score>
        <Score TestId="20071002">87</Score>
        <Score TestId="20072001">95</Score>
        <Score TestId="20072002">100</Score>
    </Score>
</row>
<row>
    <StudentId>20070002</StudentId>
    <Score>
        <Score TestId="20071001">90</Score>
        <Score TestId="20071002">88</Score>
        <Score TestId="20072001">96</Score>
        <Score TestId="20072002">100</Score>
    </Score>
</row>

お次はPATHモードでXPathで列名指定したものを実行してみます。


select StudentId as '@StudentId', Score as '*' from ScoreXML FOR XML PATH;

実行結果

<row StudentId="20070001">
    <Score TestId="20071001">89</Score>
    <Score TestId="20071002">87</Score>
    <Score TestId="20072001">95</Score>
    <Score TestId="20072002">100</Score>
</row>
<row StudentId="20070002">
    <Score TestId="20071001">90</Score>
    <Score TestId="20071002">88</Score>
    <Score TestId="20072001">96</Score>
    <Score TestId="20072002">100</Score>
</row>

さて最後に一番複雑なEXPLICITモードを実行します。
今までのようにScoreXMLだけだと面白くないので、ScoreXMLとTestsテーブルを使って表現してみます。


select * from Tests;
go

select 
	1 as Tag,
	NULL as Parent,
	row_number() over (order by StudentId) as [Tests!1!Number],
	Students.StudentId as [Tests!1!StudentId],
	Students.KanjiName as [Tests!1!StudentName],
	null as [Test!2!Number],
	null as [Test!2!TestId],
	null as [Test!2!TestName],
	null [Test!2!Point]
from Students 
union all
select 
	2 as Tag,
	1 as Parent,
	null,
	ScoreXML.StudentId,
	null,
	char(ascii('A') + row_number() over (partition by StudentId order by TestId)),
	Tests.TestId,
	Tests.TestName,
	ScoreXML.Score.query
	(
		'/Score[(@TestId cast as xs:decimal?) = sql:column("TestId")]'
	)
from ScoreXML , Tests
ORDER BY [Tests!1!StudentId],[Test!2!TestId]
FOR XML EXPLICIT

実行結果

<Tests Number="1" StudentId="20070001" StudentName="野上　彰">
  <Test Number="B" TestId="20071001" TestName="前期中間　現代文">
    <Point>
      <Score TestId="20071001" xmlns="">89</Score>
    </Point>
  </Test>
  <Test Number="C" TestId="20071002" TestName="前期中間　古典">
    <Point>
      <Score TestId="20071002" xmlns="">87</Score>
    </Point>
  </Test>
  <Test Number="D" TestId="20072001" TestName="前期期末　古典">
    <Point>
      <Score TestId="20072001" xmlns="">95</Score>
    </Point>
  </Test>
  <Test Number="E" TestId="20072002" TestName="前期期末　数学">
    <Point>
      <Score TestId="20072002" xmlns="">100</Score>
    </Point>
  </Test>
</Tests>
<Tests Number="2" StudentId="20070002" StudentName="藤原　義江">
  <Test Number="B" TestId="20071001" TestName="前期中間　現代文">
    <Point>
      <Score TestId="20071001" xmlns="">90</Score>
    </Point>
  </Test>
  <Test Number="C" TestId="20071002" TestName="前期中間　古典">
    <Point>
      <Score TestId="20071002" xmlns="">88</Score>
    </Point>
  </Test>
  <Test Number="D" TestId="20072001" TestName="前期期末　古典">
    <Point>
      <Score TestId="20072001" xmlns="">96</Score>
    </Point>
  </Test>
  <Test Number="E" TestId="20072002" TestName="前期期末　数学">
    <Point>
      <Score TestId="20072002" xmlns="">100</Score>
    </Point>
  </Test>
</Tests>

さて、ようやくOPENXMLです。

ここでは、ScoreXML.Scoreに格納されているXMLを行セット(StudentId,TestId,pointの3列)にして取得する処理を行います。
このSQLは以下の処理を行っています。

ScoreXMLからFOR XML句を使用してXML文書の取得をし、varchar(max)へ変換。
sp_xml_preparedocumentを使用して、XMLを解析させる。
OPENXMLを用いて解析したXMLからデータを取得し、行セットに変換する。
sp_xml_removedocumentを使用して、解析したXML文書の破棄をする。


begin
	declare @idoc int ;
	declare @val varchar(max);

	select @val = cast((select * from ScoreXML FOR XML PATH, ROOT('ROOT')) as varchar(max));

	EXEC sp_xml_preparedocument @idoc OUTPUT, @val;
	
	select *
	from openxml(@idoc,'/ROOT/row/Score/Score') 
	with(StudentId decimal(8,0) '../../StudentId', TestId decimal(8,0) , point decimal(3,0) '.');
	EXEC sp_xml_removedocument @idoc 
end

実行結果

StudentId	TestId	point
20070001	20071001	89
20070001	20071002	87
20070001	20072001	95
20070001	20072002	100
20070002	20071001	90
20070002	20071002	88
20070002	20072001	96
20070002	20072002	100

ここでは、OPENXMLで指定したXPathが'/ROOT/row/Score/Score'としたので、Score毎=テスト毎に列が生成されています。
念のため、SQLで得られたXML文書も提示しておきます。


<ROOT>
    <row>
        <StudentId>20070001</StudentId>
        <Score>
            <Score TestId="20071001">89</Score>
            <Score TestId="20071002">87</Score>
            <Score TestId="20072001">95</Score>
            <Score TestId="20072002">100</Score>
        </Score>
    </row>
    <row>
        <StudentId>20070002</StudentId>
        <Score>
            <Score TestId="20071001">90</Score>
            <Score TestId="20071002">88</Score>
            <Score TestId="20072001">96</Score>
            <Score TestId="20072002">100</Score>
        </Score>
    </row>
</ROOT>

さてOPENXMLですが、これにはオプションで第3引数のflagsがあります。
今回は指定をせず、属性中心のマッピングが行われたためうまく動いていますが、
仮に、要素中心のマッピングをするフラグ(2)を指定した場合に、どのような結果が返ってくるかを見てみます。


begin
	declare @idoc int ;
	declare @val varchar(max);

	select @val = cast((select * from ScoreXML FOR XML PATH, ROOT('ROOT')) as varchar(max));

	EXEC sp_xml_preparedocument @idoc OUTPUT, @val;

	select *
	from openxml(@idoc,'/ROOT/row/Score/Score',2) 
	with(StudentId decimal(8,0) '../../StudentId', TestId decimal(8,0) , point decimal(3,0) '.');
	EXEC sp_xml_removedocument @idoc 
end

実行結果

StudentId	TestId	point
20070001	NULL	89
20070001	NULL	87
20070001	NULL	95
20070001	NULL	100
20070002	NULL	90
20070002	NULL	88
20070002	NULL	96
20070002	NULL	100

要素中心のマッピングを行ったため、属性のTestIdが列名と紐づけられずにNULLになってしまいます。
この場合は、 TestId decimal(8,0)をTestId decimal(8,0) '@TestId'と書き換えると、要素がマッピングされるようになります。

・・・とまぁ長くなりましたが、これで東京#20で私が行ったことの概略はかけたかと思います。

いろいろ混乱することもありましょうが、XML関連技術を楽しく快適にお使いいただける情報になれば幸いです♪

#頭の中にはXHTMLの出力をXSLTで行って、画面からの戻りをXHTMLでもらいそれをXSLTにかけ、DBに格納する

#って感じで、うまく使えるようになるんじゃないかなぁと思っておりますです。

#まだ、そこまでフレームワークの作りこみをしていないのでこれからですが・・・・。

投稿日時 : 2008年6月12日 2:56

No comments posted yet.

Post Feedback

タイトル	タイトルを入力してください
名前	名前を入力してください
Url:
コメントコメントを入力してください
名前をブラウザに記憶する

夏椰の東屋

ニュース

記事カテゴリ

書庫

日記カテゴリ

Other Site From Kaya

コメント

Post Feedback